Future Value of Cash Flows Function

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 

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

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