XIRR, average growth rate of NAV

[FR Lucas]

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:

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

 

[FR Lucas]

Does anyone have any thoughts on this one?

Thanks

 

[XOR LX]

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

Hi. Not sure I understand.

If that's the formula in C3, then what are the formulas in C2 and C4-C8? That formula doesn't look as if it can be simply copied to the other cells in that column to give similar results, principally since none of the range references have been made absolute.

Also, can you break down how that formula gives a result of 115? Using that data, i.e. A7=30/06/2017, A8=31/12/2017, B8=85, B10=29.6% and C7=80 I don't get anything like your result.

Regards

 

[FR Lucas]

Hi,

Thanks for the reply.

I'm sorry, i copied the wrong formula above, therefore it has the wrong references. The formula in C3 giving the result of 115 is as followed:

=C2*(1+($B$10*YEARFRAC(A2,A3,3)))-B3

The formula is then simply copied down.

What i'm basically thought to do is grow the NAV (100) at the IRR of 29.6% and than deduct the CF of the period. I thought that by doing so, the NAV in 31/12/2017 (cell C8) would have to equal 0. Unfortunately this isn't true and it returns a rest value of 7.

Many thanks for any ideas.

Lucas

 

[XOR LX]

Apologies. I can help you with the Excel work, though as to what you're trying to calculate, and the financial reasoning behind why that isn't working I'm afraid I don't really have the knowledge to suggest anything.

Perhaps you should talk to someone first about your assumption that "I assumed that one grows the investment at the IRR and deducts the future CFs from it.". If this is corroborated, or corrected, then perhaps you can come back and we can help with the actual Excel side of things.

Regards

 

[joeu2004]

[...deleted; submitted by mistake; incomplete...]

 

[joeu2004]

The formula in C3 giving the result of 115 is as followed:
=C2*(1+($B$10*YEARFRAC(A2,A3,3)))-B3

The correct formula to use is:

=C2*(1+$B$10)^YEARFRAC(A2,A3,3)-B3

Then the results would be:

	A	B	C	D
1		CF	Bal
2	12/31/2014	-100.00	100.00	=-B2
3	6/30/2015		113.70	=C2*(1+$B$10)^YEARFRAC(A2,A3,3)-B3
4	12/31/2015	35.00	94.55	=C3*(1+$B$10)^YEARFRAC(A3,A4,3)-B4
5	6/30/2016	50.00	57.58	...etc...
6	12/31/2016		65.61
7	6/30/2017		74.60
8	12/31/2017	85.00	0.00
9
10	XIRR	29.55%		=XIRR(B2:B8,A2:A8)

<tbody>
</tbody>