We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

XIRR with non-contiguous cells

asked 2020-09-01 18:16:48 +0200

jdkinne gravatar image

updated 2021-05-24 19:11:39 +0200

Alex Kemp gravatar image

Can I use CALC's XIRR with non-contiguous cells?

When I try I get a 504 error.

           (100.00)    01/01/20
(100.00)    105.00     01/03/20
            107.00     01/15/20
            110.00     02/25/20


The formula in B7 is =XIRR(B1:B4,C1:C4,0.01)
The formula in B8 is =XIRR((A2,B2:B4),C1:C4,0.01)


C:\fakepath\XIRR Sample.ods

(Edit: fixed PRE formatting -AK)

edit retag flag offensive close merge delete


Please upload a sample file (your data presented as text without showing any column and row headers are completely incomprehensible - at least for me)

Opaque gravatar imageOpaque ( 2020-09-01 18:48:43 +0200 )edit

How does one upload a file? Thx

jdkinne gravatar imagejdkinne ( 2020-09-02 01:38:26 +0200 )edit

The sample is uploaded and seems to be available from my initial post.

However the answer seems to be that XIRR does not accept a range list/concatenation/union as argument.

So then the follow on is, is there a reasonable work around?

I would like to find something more elegant than creating multiple sheets with nearly the same data. Duplicate data can cause many problems - but there are some ugly ways that data can be manually duplicated so as to present a simple argument to XIRR.

Is there something better?


jdkinne gravatar imagejdkinne ( 2020-09-02 19:43:06 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-09-01 20:07:23 +0200

erAck gravatar image

XIRR does not accept a range list/concatenation/union as argument.

edit flag offensive delete link more


Is there a work around? Thx

jdkinne gravatar imagejdkinne ( 2020-09-02 01:33:55 +0200 )edit

Other than copying values to a contiguous range (or referencing cell ranges from there), no.

erAck gravatar imageerAck ( 2020-09-07 13:31:53 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-01 18:16:48 +0200

Seen: 101 times

Last updated: May 24