spreadsheetmaker
New Member
- Joined
- Nov 2, 2018
- Messages
- 5
I have been wrestling with a XIRR formula for the past week. I have a table with outflows and fund names for each outflow. I created a XIRR formula that not only takes into a count a specific fund identified in cell F2 but also prevents any XIRR errors when the initial outflow value is 0. The issue that I'm having right now is that my present value of the fund is located in a non-contiguous cell (G6). I need to somehow expand the array that XIRR is calculating to include the last value in cell G6 with today's date. Based on the data provided, the expected XIRR should be 3.41%.
<colgroup><col><col><col span="2"><col span="2"><col></colgroup><tbody>
</tbody>
Current formula in cell G2 is: =XIRR(IF(OFFSET($B$1:$B$16,MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)-1,0,ROWS($B$1:$B$16)-MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)+1,1)=F2,OFFSET($C$1:$C$16,MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)-1,0,ROWS($B$1:$B$16)-MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)+1,1),0),IF(OFFSET($B$1:$B$16,MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)-1,0,ROWS($B$1:$B$16)-MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)+1,1)=F2,OFFSET($A$1:$A$16,MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)-1,0,ROWS($B$1:$B$16)-MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)+1,1),0))
The reason why I can't simply add today's date to the bottom with the investment name and value is that the actual investment value is pulled from another worksheet. I linked the value of $119,000.00 to cell G6.
It doesn't look like I'm allowed to upload my spreadsheet right now as I'm a brand new forum member.
| Date | FUND | XIRR VALUE | FUND | XIRR: | ||
| 1/1/2015 | A | $ - | D | #N/A | ||
| 1/1/2015 | B | $ - | ||||
| 1/1/2015 | C | $ - | ||||
| 1/1/2015 | D | $ - | Todays: Fund Value | |||
| 1/1/2015 | E | $ - | D | $ 119,000.00 | ||
| 1/1/2015 | F | $ - | ||||
| 1/1/2015 | G | $ - | ||||
| 1/1/2015 | D | $ - | ||||
| 11/17/2014 | A | $ (75,000.00) | ||||
| 1/8/2015 | B | $ (35,000.00) | ||||
| 10/14/2014 | C | $ (75,000.00) | ||||
| 1/8/2015 | D | $ (35,000.00) | ||||
| 1/14/2015 | D | $ (50,000.00) | ||||
| 1/14/2015 | B | $ (50,000.00) | ||||
| 6/8/2015 | D | $ (20,000.00) |
<colgroup><col><col><col span="2"><col span="2"><col></colgroup><tbody>
</tbody>
Current formula in cell G2 is: =XIRR(IF(OFFSET($B$1:$B$16,MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)-1,0,ROWS($B$1:$B$16)-MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)+1,1)=F2,OFFSET($C$1:$C$16,MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)-1,0,ROWS($B$1:$B$16)-MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)+1,1),0),IF(OFFSET($B$1:$B$16,MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)-1,0,ROWS($B$1:$B$16)-MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)+1,1)=F2,OFFSET($A$1:$A$16,MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)-1,0,ROWS($B$1:$B$16)-MATCH(1, ($B$1:$B$16 = F2) * ($C$1:$C$16 <> 0),0)+1,1),0))
The reason why I can't simply add today's date to the bottom with the investment name and value is that the actual investment value is pulled from another worksheet. I linked the value of $119,000.00 to cell G6.
It doesn't look like I'm allowed to upload my spreadsheet right now as I'm a brand new forum member.
