Calculating the future investment value, with a fixed interest rate, but increasing monthly payments.

[J21ARomeo]

Hi All,

I have had a look at a number of forums to try and find the answer to my question, but I will need this to be simplified.

Suppose I started paying £20 every month into an investment portfolio for the next 40 years, with a fixed interest rate of 2.5%. However, every year I increase my monthly payment also by 2.5%. Is there anyway I can I do this future value calculation on excel in one step Please?

 

[DRSteele]

Welcome to the forum.

What you seek is a single-cell formula to compute the future value of an annuity with growth. Here is a closed-form formula that is often used, but as you can see it implodes when the growth rate of the payments equals the interest rate.
FV = Pmt x ( (1 + i)^n - (1 + g)^n ) / (i - g)

This requires cleverness on our part. Below is part of a payments table that lays out the interest and end value for the parameters you provided. Obviously I included here only sample rows. You must copy all the sample formulas in Row13 down all the way to Row491 to account for 480 payments.

You can see that the table produces a final value equal to the single-cell formula I noodled out. Everything here adjusts for payments at the beginning or at the end.

Book1
	A	B	C	D	E
1	periods	480
2	beginning (1), end (0)	1
3	payment	20
4	growth rate	0.2083%
5	interest rate	0.2083%
6
7	future value (Table)	26,068.37
8	future value (Formula)	26,068.37
9
10
11	per	beg	pmt	int	end
12	1		20.00	0.04	20.04
13	2	20.04	20.04	0.08	40.17
Sheet15

Cell Formulas
Range	Formula
B4	=0.025/12
B5	=0.025/12
B7	=E491
B13	=E12
B8	{=FV(B5,B1,0,-NPV(B5,B3*(1+B4)^(ROW(INDIRECT("1:"&B1))-1))*(1+B5)^B2)}
C12	=B3
C13	=C12*(1+$B$4)
D12	=(B12+C12)*$B$5*B2
D13	=(B13+C13*$B$2)*$B$5
E12	=B12+C12+D12
E13	=B13+C13+D13
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[J21ARomeo]

Hi DRSteele, many thanks for your help. I am a bit confused with what is happening from the -NPV... onwards when using the Array Formula, are you able to assist please?

 

[MickG]

Another option, Try this "UDF":-

To Save and Run UDF:-
Copy Function from Thread
In Your Data sheet , Click"Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click"Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.

In Active sheet, Add the "function" in a cell as:-
=nsave(20,0.025,40,1.025)


Where values in function=
Insave = 20
Pc =0.025
Yrs = 40
IncPc = 1.025

[COLOR=black][FONT=Calibri]Function nSave(InSave As Double, PC AsDouble, Yrs As Long, Incpc As Double)[/FONT][/COLOR] as Double
[COLOR=black][FONT=Calibri]Dim n As Long, Tot As Double[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]Dim st As Double[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]st = InSave[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]For n = 1 To Yrs * 12[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]   Tot = InSave * (1 + PC / 12)[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]   If n Mod 12 = 0 Then st = st * Incpc[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]   InSave = Tot + st[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]Next n[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]nSave = Tot[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]End Function[/FONT][/COLOR]

After entering function and values , click "Enter"
The cell should now show the results