XIRR with dynamic range

[kylc3]

Hi,

I work in the investment industry and am trying to calculate the XIRR of our various mutual funds using the dates and the monthly withdrawal amounts that change over time, with the ending amount being the last number from the post-withdrawal column. The issue is that I have to manually adjust the range to fit exactly, or else the formula errors. Is there a way to use an XIRR formula that is flexible enough to accommodate the different time periods without having to manually adjust the range to perfectly fit the number or rows?

 

[joeu2004]

A concrete example would. In addition to dynamic cell references, you might also need to fudge the sign of values (plus or minus), depending on the "sign" convention that you chose.

 

[kylc3]

Sorry, I tried to post a screen shot but it looks like it didn't work. Here's a link to a screenshot of the spreadsheet. Thanks!

https://imgur.com/a/HR6ko

 

[joeu2004]

I think you want something the following.

[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TH][/TH]
[TH]I
[/TH]
[TH]J
[/TH]
[TH]K
[/TH]
[TH]L
[/TH]
[TH]M
[/TH]
[TH]N
[/TH]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: right"]Date
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]End Bal
[/TD]
[TD="align: center"]Cash Flow
[/TD]
[TD="align: right"]XIRR
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"]12/31/2015
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]500,000
[/TD]
[TD="align: right"]500,000
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"]1/31/2016
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]488,506
[/TD]
[TD="align: right"]-2,083
[/TD]
[TD="align: right"]-23.95%
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: right"]2/29/2016
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]486,222
[/TD]
[TD="align: right"]-2,083
[/TD]
[TD="align: right"]-13.44%
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: right"]3/31/2016
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]509,483
[/TD]
[TD="align: right"]-2,083
[/TD]
[TD="align: right"]11.46%
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: right"]4/30/2016
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]512,765
[/TD]
[TD="align: right"]-2,083
[/TD]
[TD="align: right"]11.99%
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: right"]5/31/2016
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]512,369
[/TD]
[TD="align: right"]-2,083
[/TD]
[TD="align: right"]10.32%
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: right"]6/30/2016
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]519,898
[/TD]
[TD="align: right"]-2,083
[/TD]
[TD="align: right"]12.66%
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: right"]7/31/2016
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]527,801
[/TD]
[TD="align: right"]-2,083
[/TD]
[TD="align: right"]14.39%
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: right"]8/31/2016
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]523,195
[/TD]
[TD="align: right"]-2,083
[/TD]
[TD="align: right"]11.68%
[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: right"]9/30/2016
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]522,390
[/TD]
[TD="align: right"]-2,083
[/TD]
[TD="align: right"]10.72%
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: right"]10/31/2016
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]511,980
[/TD]
[TD="align: right"]-2,083
[/TD]
[TD="align: right"]7.54%
[/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD="align: right"]11/30/2016
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]511,427
[/TD]
[TD="align: right"]-2,083
[/TD]
[TD="align: right"]7.20%
[/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD="align: right"]12/31/2016
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]517,931
[/TD]
[TD="align: right"]-2,083
[/TD]
[TD="align: right"]8.33%
[/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD="align: right"]1/31/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]523,101
[/TD]
[TD="align: right"]-2,146
[/TD]
[TD="align: right"]9.03%
[/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD="align: right"]2/28/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]531,993
[/TD]
[TD="align: right"]-2,146
[/TD]
[TD="align: right"]10.35%
[/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD="align: right"]3/31/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]532,828
[/TD]
[TD="align: right"]-2,146
[/TD]
[TD="align: right"]10.11%
[/TD]
[/TR]
[TR]
[TD="align: center"]18
[/TD]
[TD="align: right"]4/30/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]534,237
[/TD]
[TD="align: right"]-2,146
[/TD]
[TD="align: right"]10.01%
[/TD]
[/TR]
[TR]
[TD="align: center"]19
[/TD]
[TD="align: right"]5/31/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]541,631
[/TD]
[TD="align: right"]-2,146
[/TD]
[TD="align: right"]10.74%
[/TD]
[/TR]
[TR]
[TD="align: center"]20
[/TD]
[TD="align: right"]6/30/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]539,563
[/TD]
[TD="align: right"]-2,146
[/TD]
[TD="align: right"]10.15%
[/TD]
[/TR]
[TR]
[TD="align: center"]21
[/TD]
[TD="align: right"]7/31/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]545,991
[/TD]
[TD="align: right"]-2,146
[/TD]
[TD="align: right"]10.67%
[/TD]
[/TR]
[TR]
[TD="align: center"]22
[/TD]
[TD="align: right"]8/31/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]545,057
[/TD]
[TD="align: right"]-2,146
[/TD]
[TD="align: right"]10.26%
[/TD]
[/TR]
</tbody>[/TABLE]

Formula:
N3: { =XIRR(IF(ROW($M$2:M3)=ROW(M3), -L3, $M$2:M3), $I$2:I3) }
Copy N3 into N4:N22

Formulas displayed with curly brackets {...} are array-entered. Type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

Your XIRRs might differ because I am using the rounded amounts in the image, not the calculated amounts.

 

[kylc3]

Thank you! I'll give it a shot.

This seemed to work as well... =xirr(m2:index(m2:m150,match("",i2:i150,)-1),i2:index(i2:i150,match("",i2:i50,)-1))

 

[joeu2004]

This seemed to work as well...
=xirr(m2:index(m2:m150,match("",i2:i150,)-1),i2:index(i2:i150,match("",i2:i50,)-1))

Oh, I misunderstood your requirements.

Ostensibly, your formula calculates one IRR based on all the cash flows to-date, allowing you to add new cash flows and automatically calculate the updated IRR.

However, match("",i2:i150,) results in a #N/A error. I would write MATCH(1E300,I2:I150). 1E300 is some large number greater than any date.

But the modified formula:

=XIRR(M2:INDEX(M2:M150,MATCH(1E+300,I2:I150)), I2:INDEX(I2:I150,MATCH(1E+300,I2:I50)))

results in the wrong IRR. For the example posted (rows 2:22), the formula results in about -89.17%, whereas the correct IRR is about 10.26%, as demonstrated in my response #4 .

That reason is: the formula fails to substitute the negative ending balance for the last cash flow.

Instead, use the following array-entered formula (press ctrl+shift+Enter instead of just Enter):

=XIRR(IF(ROW(M2:INDEX(M2:M150,O2))=ROW(INDEX(M2:M150,O2)), -INDEX(L2:L150,O2), M2:INDEX(M2:M150,O2)), I2:INDEX(I2:I150,O2))

where O2 contains the formula =MATCH(1E+300,I2:I150). Of course, you might choose to replace O2 in the formula above with the MATCH formula.

 

[joeu2004]

Errata....

However, match("",i2:i150,) results in a #N/A error.

Oh! You might have populated unused cells in I2:I150 with the null string (""). In that case, there is no #N/A error. I had left unused cells empty.

Neverthess, the formula:

=XIRR(M2:INDEX(M2:M150, MATCH("", I2:I150,)-1), I2:INDEX(I2:I150, MATCH("",I2:I50,)-1))

results in the wrong IRR, namely about -89.17%, whereas the correct IRR is about 10.26% through I2.