Forum Discussion
Future Value of Cash Flows Function
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).