XIRR help

[ngarzacathexis]

I'm trying to calculate XIRR with various initial dates and an end dates.

This is what I have now

Date Column(monthly) Investment #1 Investment #2.... etc.
5/30/2014 xxxx(values) xxxx(values)
. . .
. . .
. . .
. . .
5/30/2029 xxxx(values) xxxx(values)

Investment #1: start: 8/30/2014 end: 10/31/2020
Investment #1: start: 1/31/2018 end: 5/30/2029

I have about 35 different investments so manually would take a while and I will be adding more eventually. So I want to be able to use a XIRR formula that will retrieve the cash flow values based on the start and end date within the date column range to give the XIRR solution.

Any ideas? If you need more details just let me know.

Thank you!

 

[BiocideJ]

Your request is rather unclear on what you actually have, but the XIRR formula takes two sets of data.
The first is a range of cashflows (with the first usually being the intial value and the last being the final value)
The second is a corresponding range of dates which it seems you have in your first column.

The "trick" of XIRR is the last value has to be a negative value that essential would be equivelant to cashing out the entire investment.

So taking an intial investment of $1000 and some made-up cashflows (positive being deposits and negative cash flows being money taken out) and an ending value of $2000 it would need to be setup like this:
[TABLE="width: 139"]
<tbody>[TR]
[TD="align: right"]5/20/2014[/TD]
[TD="align: right"]$1,000 [/TD]
[/TR]
[TR]
[TD="align: right"]5/27/2014[/TD]
[TD="align: right"]$100 [/TD]
[/TR]
[TR]
[TD="align: right"]7/7/2014[/TD]
[TD="align: right"]($50)[/TD]
[/TR]
[TR]
[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]$100 [/TD]
[/TR]
[TR]
[TD="align: right"]9/10/2014[/TD]
[TD="align: right"]$100 [/TD]
[/TR]
[TR]
[TD="align: right"]11/11/2014[/TD]
[TD="align: right"]$100 [/TD]
[/TR]
[TR]
[TD="align: right"]12/5/2014[/TD]
[TD="align: right"]($150)[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]$100 [/TD]
[/TR]
[TR]
[TD="align: right"]5/7/2015[/TD]
[TD="align: right"]$200 [/TD]
[/TR]
[TR]
[TD="align: right"]5/30/2015[/TD]
[TD="align: right"]($2,000)[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

would give an annualized return of 39.36% (note that XIRR always returns an annualized return)

 

[ngarzacathexis]

How can I send you a sample of the spreadsheet?

 

[Zack Barresse]

Take a look at this thread for posting parts of your spreadsheet:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

Uploading files is not allowed on this forum.

 

[JoeMo]

Your request is rather unclear on what you actually have, but the XIRR formula takes two sets of data.
The first is a range of cashflows (with the first usually being the intial value and the last being the final value)
The second is a corresponding range of dates which it seems you have in your first column.

The "trick" of XIRR is the last value has to be a negative value that essential would be equivelant to cashing out the entire investment.

So taking an intial investment of $1000 and some made-up cashflows (positive being deposits and negative cash flows being money taken out) and an ending value of $2000 it would need to be setup like this:
[TABLE="width: 139"]
<tbody>[TR]
[TD="align: right"]5/20/2014[/TD]
[TD="align: right"]$1,000[/TD]
[/TR]
[TR]
[TD="align: right"]5/27/2014[/TD]
[TD="align: right"]$100[/TD]
[/TR]
[TR]
[TD="align: right"]7/7/2014[/TD]
[TD="align: right"]($50)[/TD]
[/TR]
[TR]
[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]$100[/TD]
[/TR]
[TR]
[TD="align: right"]9/10/2014[/TD]
[TD="align: right"]$100[/TD]
[/TR]
[TR]
[TD="align: right"]11/11/2014[/TD]
[TD="align: right"]$100[/TD]
[/TR]
[TR]
[TD="align: right"]12/5/2014[/TD]
[TD="align: right"]($150)[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]$100[/TD]
[/TR]
[TR]
[TD="align: right"]5/7/2015[/TD]
[TD="align: right"]$200[/TD]
[/TR]
[TR]
[TD="align: right"]5/30/2015[/TD]
[TD="align: right"]($2,000)[/TD]
[/TR]
</tbody>[/TABLE]

would give an annualized return of 39.36% (note that XIRR always returns an annualized return)

Alternatively, the first value (the initial investment) has to be negative along with any dividend and cap gains reinvestments (all cash in), while sales proceeds (cash out) should be positive. As long as the sign convention adopted is consistent, the same XIRR value will be returned.

 

[ngarzacathexis]

Investment 1 2 3 4 5 6 7 8 9 May-14 (10,012,042) (423,478) (755,963) (1,308,414) (1,676,141) (5,921,758) (4,869,519) (5,302,830) (8,512,973)
Jun-14 1,635,812 1,538,847 1,551,703 1,276,165 - - - - -
Jul-14 3,359,306 3,038,204 3,005,115 2,552,242 - - - - -
Aug-14 2,395,707 2,140,127 2,155,957 1,858,428 - - - - -
Sep-14 1,691,005 1,562,738 1,573,430 1,384,531 - 925,897 - 956,171 -
Oct-14 1,343,776 1,249,301 1,220,903 1,092,143 666,003 1,368,673 786,725 1,454,585 -
Nov-14 959,440 886,652 856,751 778,632 1,452,202 950,358 1,710,655 1,016,737 -
Dec-14 138,057 111,110 175,673 187,912 778,576 595,060 845,812 671,840 785,082
Jan-15 419,091 348,886 304,084 439,745 159,536 440,057 636,781 465,542 1,630,930
Feb-15 396,200 406,010 447,770 449,709 75,997 254,596 354,233 199,172 1,099,948
Mar-15 404,486 407,054 437,751 435,329 459,368 225,188 470,430 207,234 1,045,879
Nov-21 63,136 58,621 54,418 54,228 31,992 36,235 34,002 49,383 47,615
Dec-21 62,412 57,955 53,796 53,613 31,615 35,806 33,596 48,803 47,046
Jan-22 61,704 57,304 53,189 53,011 31,246 35,388 33,200 48,236 46,490
Feb-22 61,011 56,667 52,595 52,423 30,886 34,980 32,812 47,681 45,947
Mar-22 60,334 56,044 52,014 51,847 30,535 34,580 32,434 47,139 45,417
Apr-22 59,672 55,434 51,445 51,284 30,191 34,190 32,064 46,609 44,899
May-22 59,023 54,838 50,889 50,732 33,808 31,702 46,090 44,392
Jun-22 58,389 54,254 50,345 50,192 33,434 31,349 45,583 43,896
Jul-22 57,767 53,682 49,812 49,664 33,069 31,003 45,086 43,412
Aug-22 57,159 53,122 49,289 49,146 32,711 30,665 44,600 42,938
Sep-22 56,563 52,574 48,778 48,639 32,361 30,334 44,124 42,474
Oct-22 55,979 52,037 48,277 48,142 32,018 30,010 43,658 42,020
Nov-22 55,407 51,510 47,786 47,656 31,683 43,202 41,575
Dec-22 54,847 50,994 47,305 47,179 31,354 42,755 41,140
Jan-23 54,297 50,488 46,834 46,711 31,032 42,317 40,714
Feb-23 53,758 49,991 46,371 46,251 30,716 41,887 40,297
Mar-23 53,228 49,504 45,916 45,800 30,407 41,466 39,888
Apr-23 52,709 49,025 45,470 45,357 30,104 41,053 39,487
May-23 52,198 48,554 45,032 44,922 29,806 40,648 39,094
Jun-23 51,697 48,092 44,601 44,495 40,250 38,709
Jul-23 51,204 47,638 44,178 44,075 39,859 38,331





I'm running the date range along the column and my initial investment is at the top. I need a formula that will calculate XIRR. The different investments have different initial and end dates. The formula has to select only the range that has values for that investment.

 

[hiker95]

ngarzacathexis,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

How can I send you a sample of the spreadsheet?

You can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[ngarzacathexis]

BiocideJ,

I understand the XIRR formula and how it works, what I need is an XIRR formula that will look at my leftmost column for the dates and then pick up in the individual investment columns only the cells that have values thus allowing me to use the same formula for each column without having to manually adjust it for the start and end dates.

Inv 1 Inv2 Inv3
May-14 -10,000 0.00 0.00
Jun-14 2,000 -5,000 0.00
Jul-14 1,000 1,500 -2,000
Aug-14 2,000 1,000 500
Sep-14 1,500 1,500 750
Oct-14 2,000 0.00 500


XIRR needs to select only the months that have data as the start and end dates are different for each investment.

 

[BiocideJ]

Alternatively, the first value (the initial investment) has to be negative along with any dividend and cap gains reinvestments (all cash in), while sales proceeds (cash out) should be positive. As long as the sign convention adopted is consistent, the same XIRR value will be returned.

This is correct. I tend to prefer it that way, I suppose, because it is more intuitive for myself. I should have been less deterministic with my phrasing though.

ngarzacathexis,
I don't understand what your values are...Perhaps just post the dates and the first column of values. Also, it will be important to explain what those values actually are. I am assuming they are cash flows, but what direction are each of the values? Also, I don't see an end value anywhere.
EDIT: Sorry, my page apparently was not refreshing to see your most recent post.

 

[BiocideJ]

OK. Well, I'm not sure this entirely solves this problem because I still don't see where you have put end values, however, this will take the values you have and exclude the first and last zeros to give you a XIRR range that is at least usable.

you will need to change the row numbers to match what you are actually using

=XIRR(INDEX(B2:B7,MATCH(1,--(B2:B7<>0),0)):INDEX(B2:B7,MAX(IF(B2:B7<>0,ROW(B2:B7)))-1),INDEX($A2:$A7,MATCH(1,--(B2:B7<>0),0)):INDEX($A2:$A7,MAX(IF(B2:B7<>0,ROW(B2:B7)))-1))
This is an array formula and will need to be confirmed with CTRL+SHIFT+ENTER