# Future Value of Unequal Payments

#### bsutton

##### New Member
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 show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### dafan

##### Well-known Member
Not tested, just pure mathematical approach:
Can't you just use this:

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

#### bsutton

##### New Member
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
your way off on that answer it is

#### Joe MAyo

##### Board Regular

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

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
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
{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
are you considering monthly compounding?

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

Last edited:

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,525
Messages
5,837,867
Members
430,519
Latest member
abtg1

### 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?

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