# XIRR, average growth rate of NAV

#### FR Lucas

##### New Member
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

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Does anyone have any thoughts on this one?

Thanks

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

Hi,

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

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

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

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>

Replies
3
Views
755
Replies
3
Views
3K
Replies
1
Views
515
Replies
8
Views
594
Replies
2
Views
237

1,219,938
Messages
6,151,071
Members
451,006
Latest member
dhinze84

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back