Future Value of Cash Flows Function

[JayGardner]

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 for variable payments ? 

[#2]

Hi @JayGardner 

For a variable payment stream, you need to define the stream structure and then apply FV() function against each periodic payment.

I have made a sample calculation in the attached file for more understanding and clarity.

Hope this will help you.

Thanks

Tauqeer

[#3]

@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).

[#4]

Thanks for the explanation and the sample worksheet. Helpful.  My original question was did Excel have a function that would both calculate the FV of variable and uneven flows and sum them up ; the answer I received was 'no'.  So I went looking for a tool that accomplishes this function and found one at   https://www.calculatorsoup.com/calculators/financial/future-value-cash-flows-calculator.php

I would like to see Excel add this function. 

@JayGardner