rwilson200
New Member
- Joined
- Mar 15, 2018
- Messages
- 3
Although there have been helpful discussions about Excel 2013's XIRR function using non-contiguous cells, I can't get any suggestions to work in my particular situation. Below is an excerpt from a large spreadsheet covering a number of years. It shows some formulas that work and some that don't, along with columns used for showing correct XIRRs for various time periods.
I would like to calculate XIRR for rolling YTD, 12 month, and 36 month periods. For example, for the 12 month period ending 3/31/17, I would like to use the starting value in cell C4, the cashflow range B5:B15, and the ending value -C16 (note the negative). For the next new month, I insert a row above row 16, copy the 3/31/17 data to the newly inserted row, then add new data for 4/30/17.
XIRR can't handle multiple ranges but I found a workaround that can deal with input from two non-contiguous ranges. However, this workaround can't handle three ranges or make C16 negative. Furthermore, I'm puzzled how the multiple ranges seem work in the XIRR formula shown in C24.
IRR could be annualized to get the return I'm looking for and it works with multiple ranges if they are assigned a range name. So, I could use an assigned name (e.g. =IRR(cashflow)), but the ranges vary monthly and the ending value must be made negative. I don't know how to deal with these variables.
Some of my attempts used OFFSET to select the last 12 months of data, but I'm still stuck on trying to pick the three ranges that I need. I imagine the solution will involve CHOOSE but I can't seem to figure out how to make it work.
A couple preferences: 1) the extent of the spreadsheet prevents me from using nearby cells for placeholders/work areas and 2) I would prefer to avoid VBA is possible.
GoogleSheets appears to handle multiple ranges perfectly, even changing some of them negative inside the formula, but using GoogleSheets isn't possible in my situation.
Any suggestions will be greatly appreciated.
Excel 2013 32 bit
<tbody>
</tbody>
<tbody>
</tbody>
I would like to calculate XIRR for rolling YTD, 12 month, and 36 month periods. For example, for the 12 month period ending 3/31/17, I would like to use the starting value in cell C4, the cashflow range B5:B15, and the ending value -C16 (note the negative). For the next new month, I insert a row above row 16, copy the 3/31/17 data to the newly inserted row, then add new data for 4/30/17.
XIRR can't handle multiple ranges but I found a workaround that can deal with input from two non-contiguous ranges. However, this workaround can't handle three ranges or make C16 negative. Furthermore, I'm puzzled how the multiple ranges seem work in the XIRR formula shown in C24.
IRR could be annualized to get the return I'm looking for and it works with multiple ranges if they are assigned a range name. So, I could use an assigned name (e.g. =IRR(cashflow)), but the ranges vary monthly and the ending value must be made negative. I don't know how to deal with these variables.
Some of my attempts used OFFSET to select the last 12 months of data, but I'm still stuck on trying to pick the three ranges that I need. I imagine the solution will involve CHOOSE but I can't seem to figure out how to make it work.
A couple preferences: 1) the extent of the spreadsheet prevents me from using nearby cells for placeholders/work areas and 2) I would prefer to avoid VBA is possible.
GoogleSheets appears to handle multiple ranges perfectly, even changing some of them negative inside the formula, but using GoogleSheets isn't possible in my situation.
Any suggestions will be greatly appreciated.
Excel 2013 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR] | |
|---|---|---|---|---|---|---|---|---|
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR] | Month ending | monthly cashflow deposits (+) withdrawals (-) | ending balance | monthly return | overall cashflow | 12 month cashflow | ||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR] | 1/31/16 | 84,620 | 84,620 | 84,620 | -84,620 | -84,620 | ||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR] | 2/28/16 | 0 | 84,460 | -0.2% | 0 | 0 | 0 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR] | 3/31/16 | 0 | 85,800 | 1.6% | 0 | 85,800 | 0 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR] | 4/30/16 | -5,500 | 90,720 | 12.1% | -5,500 | -5,500 | -5,500 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR] | 5/31/16 | 0 | 93,500 | 3.1% | 0 | 0 | 0 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR] | 6/30/16 | 9,000 | 84,990 | -18.7% | 9,000 | 9,000 | 9,000 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR] | 7/31/16 | -1,000 | 86,070 | 2.4% | -1,000 | -1,000 | -1,000 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR] | 8/31/16 | -2,700 | 87,510 | 4.8% | -2,700 | -2,700 | -2,700 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR] | 9/30/16 | 0 | 89,930 | 2.8% | 0 | 0 | 0 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR] | 10/31/16 | 0 | 87,530 | -2.7% | 0 | 0 | 0 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR] | 11/30/16 | 0 | 87,640 | 0.1% | 0 | 0 | 0 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR] | 12/31/16 | 0 | 92,850 | 5.9% | 0 | 0 | 0 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR] | 1/31/17 | 300 | 94,980 | 2.0% | 300 | 300 | 94,980 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR] | 2/28/17 | 300 | 96,510 | 1.3% | 300 | 300 | 300 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR] | 3/31/17 | 300 | 94,030 | -2.9% | -94,030 | -94,030 | -94,030 | |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR] | these work: | source data is in red above and aren't to be changed | Columns F-H aren't part of original spreadsheet and are for showing correct XIRRs. Data in purple has been changed from source at left to calculate XIRR for the period. | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR] | 12 mo annualized TWR (4/1/16 - 3/31/17): | 7.357% | {=PRODUCT(1+OFFSET(D16,-11,0, 12, 1))-1} | 9.121% | 9.176% | -7.856% | ||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR] | 12 mo annualized TWR (4/1/16 - 3/31/17): | 7.357% | {=PRODUCT(1+D5:D16)-1} | =XIRR(F2:F16,$A$2:A16) | =XIRR(G4:G16,A4:A16) | =XIRR(H14:H16,A14:A16) | ||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR] | YTD TWR (1/1/17 - 3/31/17): | 0.316% | {=PRODUCT(1+OFFSET(D16,-MONTH($A$16)+1,0,MONTH($A$16)))-1} | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR] | YTD TWR (1/1/17 - 3/31/17): | 0.316% | {=PRODUCT(1+D14:D16)-1} | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR] | 12 mo IRR (4/1/16 - 3/31/17): | 0.734% | =IRR(cashflow1) | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR] | Annualized IRR: | 9.177% | =((1+B22)^12)-1 | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR] | XIRR for entire history: | 9.121% | {=XIRR(IF(ROW($2:16)< ROW(A16),$B$2:B15,-C16), A2:A16)} | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR] | in above formula, for rows 2-15, XIRR uses values in C2:C15, but for row 16, how does XIRR use D16? | |||||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]26[/COLOR] | ||||||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]27[/COLOR] | ||||||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]28[/COLOR] | these don't work: | |||||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]29[/COLOR] | 12 mo XIRR (4/1/16 - 3/31/17): | 0.000000298% | {=XIRR(OFFSET(B16,-12,0,13),OFFSET(A16,-12,0,13),0.1)} | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]30[/COLOR] | 12 mo XIRR (4/1/16 - 3/31/17): | #NUM! | =IRR(cashflow2) | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]31[/COLOR] | Annualized IRR: | #NUM! | =((1+B30)^12)-1 | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]32[/COLOR] | ||||||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]33[/COLOR] | YTD XIRR (1/1/17 - 3/31/17): | #VALUE! | {=XIRR(($B$14:B15,C16), A14:A16)} | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]34[/COLOR] | YTD XIRR (1/1/17 - 3/31/17): | #NUM! | {=XIRR(OFFSET(B16,-MONTH($A$16)+1,0,MONTH($A$16)),OFFSET(A16,-MONTH($A$16)+1,0,MONTH($A$16)))} | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]35[/COLOR] | YTD XIRR (1/1/17 - 3/31/17): | 1.84901E+15 | {=XIRR(IF(ROW(OFFSET(B16,-MONTH($A$16)+1,0,MONTH($A$16),1))< ROW(A16),OFFSET(B16,-MONTH($A$16)+1,0,MONTH($A$16), 1),-C16),OFFSET(A16,-MONTH($A$16)+1,0,MONTH($A$16),1))} | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]36[/COLOR] | ||||||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]37[/COLOR] | IRR for entire history (1/31/16 - 3/31/17): | #VALUE! | =IRR((B2:B15),-C16) | |||||
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]38[/COLOR] | Annualized IRR: | #VALUE! | =((1+B37)^12)-1 |
<tbody>
</tbody>
| Sheet: worksheet |
<tbody>
</tbody>
