Simeltaneous XIRR outputs with multiple inputs

[djh30]

Hello all,

I have a situation where I want to be able to calculate the XIRR of given distributions, combined with proceeds of a potential sale. In order to correctly calculate the XIRR, I have to create a new row that has all distributions to date and also the proceeds dictated by that given month. If I have 24 months of projections, this means I have to create 24 rows to see all the various XIRR outcomes for all 24 months if I want to see the outcomes all simultaneously.

Is there a formula that would allow me to calculate all XIRRs without having to create all the rows? Ideally, I would be able to show the XIRR potentially achieved in each month all on the same row.

Heres a link to an example: https://suntexventures.box.com/s/tfil56ne61z9h8d9b0kvr3meg1g1ym76

Thanks in advance!

 

[joeu2004]

Download "IRR Question Example MODIFIED.xlsx" (click here) [1], and see the formulas in row 17 in the worksheets "orig" and "suggested".

Worksheet "suggested" includes the following changes.

[TABLE="class: grid, width: 550"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]M
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]1/31/2015[/TD]
[TD="align: right"]2/28/2015[/TD]
[TD="align: right"]3/31/2015
[/TD]
[TD="align: right"]4/30/2015[/TD]
[TD="align: center"]//[/TD]
[TD="align: right"]11/30/2015
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]Jan-15[/TD]
[TD="align: right"]Feb-15[/TD]
[TD="align: right"]Mar-15[/TD]
[TD="align: right"]Apr-15[/TD]
[TD="align: center"]//[/TD]
[TD="align: right"]Nov-15[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: center"]//
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Cash Flow:[/TD]
[TD]
[/TD]
[TD="align: right"] (6,000)
[/TD]
[TD="align: right"] 1,000[/TD]
[TD="align: right"] 1,005[/TD]
[TD="align: right"] 1,010[/TD]
[TD="align: center"]//
[/TD]
[TD="align: right"] 1,045[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Sale Price:[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"] 5,025[/TD]
[TD="align: right"] 5,050[/TD]
[TD="align: right"] 5,075[/TD]
[TD="align: center"]//
[/TD]
[TD="align: right"] 5,250[/TD]
[/TR]
[TR]
[TD="align: center"]//
[/TD]
[TD="align: center"]//
[/TD]
[TD="align: center"]//
[/TD]
[TD="align: center"]//
[/TD]
[TD="align: center"]//
[/TD]
[TD="align: center"]//
[/TD]
[TD="align: center"]//
[/TD]
[TD="align: center"]//
[/TD]
[TD="align: center"]//
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]5.57%
[/TD]
[TD="align: right"]196.31%[/TD]
[TD="align: right"]313.65%[/TD]
[TD="align: center"]//
[/TD]
[TD="align: right"]527.99%[/TD]
[/TR]
</tbody>[/TABLE]

The formula in D17 is array-entered (press ctrl+shift+Enter instead of just Enter), to wit:

=XIRR(IF(COLUMN($C$4:D4)=COLUMN(D4),D4+D5,$C$4:D4), $C$2:D2)

Row 18 (not shown) demonstrates that these results are the same as the results that you calculated in column B (not shown).

The formula assumes the following changes/corrections.


1. -6000 in column C is moved from row 5 (sale price) to row 4 (cash flow), as I believe it should be.

If you insist on keeping -6000 in row 5, the formula in D17 is array-entered as follows, as demonstrated in worksheet "orig":

=XIRR(IF(COLUMN($C$4:D4)=COLUMN($C$4),$C$5,IF(COLUMN($C$4:D4)=COLUMN(D4),D4+D5,$C$4:D4)), $C$2:D2)

2. As shown in row 1, the correct end-of-month dates are used in row 2, as I believe you intended. The formula in C2 is

=EDATE($C$2,COLUMNS($D$2:D2))

As shown in row 1 of worksheet "orig", starting in E2, the dates are the 28th of the month because the end of Feb is the 28th, and the formula EDATE(C2,1) propagates that last-used day of the month instead of first-used day of the month.


3. I suggest that you align the first (X)IRR result with column D as shown, not column C, since the first IRR is based on data through column D.

The formulas work if you start in column C. But then, we do not have sufficient for a formula in M17 (Nov).


-----
[1] https://app.box.com/s/oi4yazdgscaxdnk935d6zqc8xje61he2