Robert Wilson
New Member
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. Attached is a file (sample for XIRR.xlsx) that shows some formulas that work and some that don't, along with columns used for showing correct XIRRs for various time periods.
ending cashflow balance
1/31/16 84,620 84,620
2/28/16 0 84,460
3/31/16 0 85,800
4/30/16 -5,500 90,720
5/31/16 0 93,500
6/30/16 9,000 84,990
7/31/16 -1,000 86,070
8/31/16 -2,700 87,510
9/30/16 0 89,930
10/31/16 0 87,530
11/30/16 0 87,640
12/31/16 0 92,850
1/31/17 300 94,980
2/28/17 300 96,510
3/31/17 300 94,030
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 if 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.
ending cashflow balance
1/31/16 84,620 84,620
2/28/16 0 84,460
3/31/16 0 85,800
4/30/16 -5,500 90,720
5/31/16 0 93,500
6/30/16 9,000 84,990
7/31/16 -1,000 86,070
8/31/16 -2,700 87,510
9/30/16 0 89,930
10/31/16 0 87,530
11/30/16 0 87,640
12/31/16 0 92,850
1/31/17 300 94,980
2/28/17 300 96,510
3/31/17 300 94,030
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 if 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.