Future Value of Unequal Payments

bsutton

New Member
Joined
Sep 1, 2007
Messages
5
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

dafan

Well-known Member
Joined
May 6, 2008
Messages
692
Not tested, just pure mathematical approach:
Can't you just use this:

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

bsutton

New Member
Joined
Sep 1, 2007
Messages
5
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.
 
Last edited:

Joe MAyo

Board Regular
Joined
May 29, 2008
Messages
79

ADVERTISEMENT

<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>
 
Last edited:

Joe MAyo

Board Regular
Joined
May 29, 2008
Messages
79
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

Board Regular
Joined
May 29, 2008
Messages
79

ADVERTISEMENT

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

New Member
Joined
Sep 1, 2007
Messages
5
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
{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

Board Regular
Joined
May 29, 2008
Messages
79
are you considering monthly compounding?

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

Watch MrExcel Video

Forum statistics

Threads
1,129,809
Messages
5,638,482
Members
417,027
Latest member
wlknspc7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top