Forum Discussion

JayGardner's avatar
JayGardner
Copper Contributor
Nov 18, 2021

Future Value of Cash Flows Function

I would like to calculate the FV of a stream of cash flows.  The FV function in Excel requires that the Payments made in every period be the same amount per period.  Is there a function that allows f...
Formulas and Functions
JoeUser2004's avatar
JoeUser2004
Bronze Contributor
Dec 11, 2021

JayGardner  ....  We can use Excel NPV to calculate the present value of the variable cash flows, then use Excel FV to calculate the future value of the NPV -- what I call NFV.

 

The exact formula depends on whether payments are at the beginning or end of each period.  Using Tauqeer's example (abbreviated table; see the attached Excel file for the complete table):

 

 

NFV formulas:

E46 (beginning payments): =FV(E2/E3, E4+1, 0, -NPV(E2/E3, D9:D44))

F46 (ending payments): =FV(E2/E3, E4, 0, -NPV(E2/E3, D9:D44))

 

Notes:

1. For the "NFV" formulas, we need only the list of payments (D9:D44) and the "NFV" formula (E46 or F46) in addition to the terms the cash flow model (E2:E4).

 

2. Excel NPV discounts the first value in the range.  So, the time frame of the present value is one period __before__ the time of the first value in the range.  That is why we use E4+1 (#payments + 1) for FV-of-NPV of beginning payments (E46), and E4 (#payments) for ending payments (F46).

 

3. We assume that the periodic rate is E2/E3 (annual rate divided by number of periods per year).  If the annual rate is a compounded yield, the periodic rate is (1+E2)^(1/E3) - 1 .  Usually, I calculate the periodic rate in the range with the data input; for example, in F2.

 

4. The "type" parameter in Excel FV has no effect when "pmt" is zero.  Therefore, it can be omitted (default type=0).

Resources