Future Value of Unequal Payments

[bsutton]

Does anyone know a formula that calculates the future value of an unequal payment stream? I am attempting to calculate the value of an investment plan where the periodic payment increases by 1% each period. I can get the answer by using the FV formula for each payment and then adding them up, but would like to be able to do it without having to do so.

Thanks.

 

[dafan]

Not tested, just pure mathematical approach:
Can't you just use this:

[FV formula] * (1.01)^[amount of periods]

 

[bsutton]

Thanks for the response Stefan. However, I can't seem to get the right answer with that formula. I may be doing something wrong.

The scenario is an investment stream where the individual invests $1800 now and $75 each month for the next 65 years (a total of 780 periods). However, the $75 periodic investment is to increase by 1% each year or 1%/12 each period.
For example,
1st period: 75.00
2nd period:75.06
3rd period:75.13
4th period:75.19
etc. for 780 periods
The annual interest rate is 8%.

According to another source and my long calculation of getting the FV of each payment individually, the future value of the cash flow stream is $2,586,855. Here are may answers attempting to use your suggestion.

FV(0.08/12,780,75,1800)*(1.01^780) = $5,432,659,433.

FV(0.08/12,780,75,1800)*(1+(0.01/12))^780 = $4,431,133

FV(0.08/12,780,75,1800)*(1.01)^65 = $4,418,046

Am I doing something wrong?

Thanks.

 

[Joe MAyo]

your way off on that answer it is

 

[Joe MAyo]

<TABLE style="WIDTH: 71pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=94 border=0 x:str><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=94 height=17 x:num="2235993.9149793377">$2,235,993.91 </TD></TR></TBODY></TABLE>

 

[Joe MAyo]

you cant do that in a calculator or use the FV forumla in excel because its set up like an annuity but no annuity exists with uneven payments, so therfore its not an annuity and there is no formula so you have to break it down into 2 parts

1. 1800*(1.08)^65
2. 75*(1+(.01/12)^(780/12) + 75.0625*(1+(.01/12)^(779/12).....etc until you reach ^(1/12)

then you sum that up and add it to step 1 to get the answer.

 

[Joe MAyo]

and if its compounded monthly the answer is $2,237,271.22
the effective interest rate is for monthly compounding is
(((1+(.01/12))^(12))-1)/12

 

[bsutton]

Thanks for the help Joe, however, if you could help me understand where I am going wrong in my calculation, I would appreciate it. I have used the FV formula and calculated the future value of each payment, and then added them up to equal $2,586,855. Here is an example of my schedule. I used 780 minus the period number as the nper in each FV formula.

Per Pmt Future Value
0 1,800.00 320,707.66
1 75.00 13,274.32
2 75.06 13,197.40
3 75.13 13,120.93
4 75.19 13,044.90
5 75.25 12,969.30
6 75.31 12,894.15
7 75.38 12,819.43
8 75.44 12,745.15
9 75.50 12,671.29
10 75.56 12,597.87
...
...
774 142.79 148.60
775 142.91 147.74
776 143.03 146.88
777 143.15 146.03
778 143.27 145.18
779 143.39 144.34
780 143.51 143.51

I believe I understand the concept behind your formula but don't understand why we wouldn't get the same answer.

Thank you for your input.

Ben

 

[tusharm]

{snip}
According to another source and my long calculation of getting the FV of each payment individually, the future value of the cash flow stream is $2,586,855. {snip}
Am I doing something wrong?

Thanks.

Cash flows at a give instance in time are additive. So, calculating the FV of each cash flow and adding them on the same future date will yield the correct answer. So, your $2,586,855 is correct (rounded to the full dollar at least and depending on how one handles the last payment).

I don't believe there is a simple single formula that you can use. I can write it mathematically, but I don't know how to solve it. What you want is
SUM j=0..(N-1) [P*(1+i)^j*(1+r)^(N-j)]

This is the same as P(1+r)^N + P(1+i)*(1+r)^(N-1) + P(1+i)^2*(1+r)^(N-2) + ... + P(1+i)^779*(1+r)^1

In the above N is the duration or 780; j is an index for the months; i is the monthly increase or 1%/12; r is the monthly interest or 8%/12; P is the initial monthly investment or $75

 

[Joe MAyo]

are you considering monthly compounding?

the answer is not $2,586,855 , it is $2,601,962.676 for monthly compounding