spreadsheetmaker
New Member
- Joined
- Nov 2, 2018
- Messages
- 5
I'm currently trying to get XIRR formula to work. I have a list of Dates/Values with first value being 0. The
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
I have gotten so far: =XIRR(INDEX(B2:B40,MATCH(TRUE,B2:B40<0,0)):B49,INDEX(A2:A40,MATCH(TRUE,B2:B40<0,0)):A49)
My formula takes care of the issue with leading value being 0 but it requires Today's date to be in cell A49 and todays value to be in date B49. If I move those values to a different column, like what I need to do, the entire formula breaks.
Date | Value | Blank | Today's Value | 35000 |
2/28/2017 | $ - | |||
2/28/2017 | $ (700.00) | XIRR | ???? | |
2/28/2017 | $ (210.00) | |||
2/28/2017 | $ (175.00) | |||
2/28/2017 | $ (52.50) | |||
3/15/2017 | $ - | |||
3/15/2017 | $ (700.00) | |||
3/15/2017 | $ (210.00) | |||
3/15/2017 | $ (175.00) | |||
3/15/2017 | $ (52.50) | |||
3/21/2017 | $ - | |||
3/21/2017 | $ - | |||
3/23/2017 | $ - | |||
3/23/2017 | $ - | |||
3/23/2017 | $ - | |||
3/23/2017 | $ (19,191.98) | |||
3/23/2017 | $ (4,798.00) | |||
3/31/2017 | $ - | |||
3/31/2017 | $ (700.00) | |||
3/31/2017 | $ (210.00) | |||
3/31/2017 | $ (175.00) | |||
3/31/2017 | $ (52.50) | |||
4/13/2017 | $ - | |||
4/13/2017 | $ (700.00) | |||
4/13/2017 | $ (210.00) | |||
4/13/2017 | $ (175.00) | |||
4/13/2017 | $ (52.50) | |||
4/28/2017 | $ - | |||
4/28/2017 | $ (700.00) | |||
4/28/2017 | $ (210.00) | |||
4/28/2017 | $ (175.00) | |||
4/28/2017 | $ (52.50) | |||
5/15/2017 | $ - | |||
5/15/2017 | $ (700.00) | |||
5/15/2017 | $ (210.00) | |||
5/15/2017 | $ (175.00) | |||
5/15/2017 | $ (52.50) | |||
5/31/2017 | $ - |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
I have gotten so far: =XIRR(INDEX(B2:B40,MATCH(TRUE,B2:B40<0,0)):B49,INDEX(A2:A40,MATCH(TRUE,B2:B40<0,0)):A49)
My formula takes care of the issue with leading value being 0 but it requires Today's date to be in cell A49 and todays value to be in date B49. If I move those values to a different column, like what I need to do, the entire formula breaks.