Hi,
I am trying to project the NAV based on a string of CFs and its corresponding IRR. I have 1 investment and three CFs (see below), returning an IRR of 29.6%.
On that basis i am trying to estimate the NAV going forward. I assumed that one grows the investment at the IRR and deducts the future CFs from it. However, this results in a residual NAV at the end of the investment period with no future CFs left. The Formula I have used to estimate the NAV in C3 (i.e. the 115) is as followed:
=C7*(1+($B$10*YEARFRAC(A7,A8,3)))-B8
The CFs are as followed:
<tbody>
</tbody>
The NAV in 31/12/2017 should equal 0 but in the above example it is not. What am I doing wrong?
Many thanks,
Lucas
I am trying to project the NAV based on a string of CFs and its corresponding IRR. I have 1 investment and three CFs (see below), returning an IRR of 29.6%.
On that basis i am trying to estimate the NAV going forward. I assumed that one grows the investment at the IRR and deducts the future CFs from it. However, this results in a residual NAV at the end of the investment period with no future CFs left. The Formula I have used to estimate the NAV in C3 (i.e. the 115) is as followed:
=C7*(1+($B$10*YEARFRAC(A7,A8,3)))-B8
The CFs are as followed:
Date | CF | NAV |
31/12/2014 | -100 | 100 |
30/06/2015 | 115 | |
31/12/2015 | 35 | 97 |
30/06/2016 | 50 | 61 |
31/12/2016 | 70 | |
30/06/2017 | 80 | |
31/12/2017 | 85 | 7 |
XIRR | 29.6% |
<tbody>
</tbody>
The NAV in 31/12/2017 should equal 0 but in the above example it is not. What am I doing wrong?
Many thanks,
Lucas