Using the NPER to work out how many more payments I would require (but it needs to acknowledge that the payments would be increasing each year by 2.5%

[J21ARomeo]

Hi All,

I used the follwing forumla: =(FV(Int,NPER,0,-NPV(Int,PMT*(1+Grw)^(ROW(INDIRECT("1:"&NPER))-1))*(1+Int) to work out my future investment value.

I wanted to start paying £20 every month into an investment portfolio for the next 40 years, wit a fixed interest rate of 2.5%. However, every year I would increase my payments by 2.5%.

I recieved the following answer 26,068.37.

However, the final bit that I wanted to do which I require help on... Is suppose after the 40 years I decided to set a goal of 40,000.00.

Question: Is there a way to work out the number of additional periods using the NPER function, (but the function would acknowledge that my payments would need to increase by 2.5% annual) please?

 

[MickG]

Try this example:-
NB:-With both your previous formula and this example, the Yearly Increment is spread out over the Year and not Increased at the start of each year ??
https://app.box.com/s/1ttslf8pnb88ts8xz8t1ut6ojo93tjga

 

[Dave Patton]

MickG provided a relevant UDF in an earlier post.

N.B. NSave is MickG's suggestion in earlier post.
This agrees to the manual calculation table.
Goal Seek with the Sumproduct formula yielded the 49.02 years.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	Amount	20.00	39,262.26	39,982.01
2	Rate	2.50%
3	Years	49
4	Escalation	2.50%
5				T10_1807a	3a
6
7		Amount	Interest	Total
8		0.00		0.00
9	1	20.00	0.04	20.04
10	2	20.00	0.08	40.13
3a

Cell Formulas
Range	Formula
C1	=Nsave(B1,B2,B3,1+B4)
C9	=(D8+B9)*$B$2/12
C10	=(D9+B10)*$B$2/12
D1	=B1* SUMPRODUCT((1+Escalation/12)^(ROW(OFFSET($A$1,0,0,Years*12,1))-1),(1+Rate/12)^(Years*12-ROW(OFFSET($A$1,0,0,Years*12,1))+1))
D9	=D8+B9+C9
D10	=D9+B10+C10
B9	=B1
B10	=B9*(1+(MOD(A9,12)=0)*$B$4)

Named Ranges
Name	Refers To	Cells
Escalation	='3a'!$B$4
Rate	='3a'!$B$2
Years	='3a'!$B$3

Excel 2010
	A	B	C	D	E
1	Amount	20.00	41,070.91	41,829.69
2	Rate	2.50%
3	Years	50
4	Escalation	2.50%
5				T10_1807a	3a
6
7		Amount	Interest	Total
8		0.00		0.00
9	1	20.00	0.04	20.04
10	2	20.00	0.08	40.13
3a

 

[J21ARomeo]

Thanks for pointing this out Mick. How would I make the previous single forumla and this example so that the increase happens at the start of the year. I.e. In Y1 I pay £20 each month, then in Y2 i pay £20.50 each month, and then in Y3 I pay £21.01...

 

[MickG]

All I can Offer is this UDF to find the Number of years for a Given return:-
Enter Function as:-
=tsave(20,0.025,40000,1.04)

Where 20 = Monthly payments, 0.025 = Basic Interest rate, 40000 = Total Return required.
and 1.04 = Yearly Interest rate.

Function TSave(InSave As Double, PC As Double, TRet As Double, Incpc As Double) As Variant
'Yearly increment % is Added at the beginning of each year"
 Dim n as Long, Tot as Double
st = InSave
   Do Until Tot > TRet
       n = n + 1
       Tot = InSave * (1 + PC / 12)
       If n Mod 12 = 0 Then st = st * Incpc
       InSave = Tot + st
  Loop

TSave = Format(n / 12, "0.0 Years")
End Function