How can I find out a annual increasing PMT for a value

[msvariar]

I have a future value of 2307936 (after 5 years) and need to make an investment every month, with an annual increase of 10%. The rate of return on investment is 9% per annum.

If there is no annual increase of 10% I am getting 30882 as PMT. How do I calculate with 10% annual increase.

 

[joeu2004]

2307936 is pretty specific. How did you arrive at that requirement?

I presume that you calculated 30882 as follows: =PMT((1+9%)^(1/12)-1, 5*12, 0, -2307936, 0).

That is good to know, because it tells us how you want to convert the annual rate (yield) to a monthly rate. The result is 30882.4752290111.

However, I believe you should use: =PMT((1+9%)^(1/12)-1, 5*12, 0, -2307936, 1). That results in 30661.4879718442.

Presumably, you want to earn a return on each payment. Therefore, payments should occur at the beginning of each period.

-----

Off-hand, I'm not sure that we can use a formula to calculate the payment exactly.

We might be able to derive a formula that provides a good-enough estimate. But I don't have time to look at the math right now.

In the meantime, I think the easiest and possibly best approach is to use Goal Seek or Solver. Then the initial payment is 25526.5305148194.

The set-up:

	A	B	C
1		Annual	Monthly
2	Yield	9.00%	0.7207%
3
4	Pmt#	Pmt	End Bal
5	1	25,526.53	25,710.51
6	2	25,526.53	51,606.32
15	11	25,526.53	293,230.88
16	12	25,526.53	321,054.80
17	13	28,079.18	351,650.31
18	14	28,079.18	382,466.32
63	59	37,373.39	2,254,047.59
64	60	37,373.39	2,307,936.00

<tbody>
</tbody>

Formulas
C2: =(1+B2)^(1/12)-1
A5: =N(A4)+1
B5: empty (initially)
C5: =(N(C4)+B5)*(1+$C$2)
B6: =IF(MOD(A6-1,12)=0,B5*(1+10%),B5)
Copy A5 into A6
Copy C5 into C6
Copy A6:C6 into A7:C64

Solver:
Objective: C64
To Value of: 2307936
By Changing: B5

(In hindsight, I should have put 10% into a cell, and referenced the cell in the formula in B6.

 

[Dave Patton]

a)Try building a table with your information and then use Goal Seek.
b)Try the UDF shown below

Excel 2010
	A	B	C	D	E
1	Payment	25,301.99
2	Rate	9%
3	Term	5
4	Escalating	10%
5	Future			$2,307,936.00
6
7
8	1	25,301.99		189.76	25,491.75
3aaa

Cell Formulas
Range	Formula
D5	=nSave(B1,B2,B3,1+B4)
D8	=B8*B2/12
B8	=B1
E8	=B8+D8

Function nSave(InSave As Double, PC As Double, Yrs As Double, Incpc As Double) As Variant

Dim n As Double, Tot As Double, st As Double
st = InSave
For n = 1 To Yrs * 12
   Tot = InSave * (1 + PC / 12)
   If n Mod 12 = 0 Then st = st * Incpc
   InSave = Tot + st
Next n
nSave = Tot
End Function

 

[joeu2004]

As I suspected, we can indeed derive formulas to calculate the initial payment directly and exactly, not an estimate.

Granted, the formulas are complicated. So as I said before, my previous solution using Goal Seek or Solver might be easier.

For the direct calculation....

First some important assumptions, which I noted in my previous posting.

1. The monthly rate should be derived from the annual rate as follows: (1+9%)^(1/12)-1. This is based on msvariar's assertion that without the 10% annual increase, the monthly payment would be 30882 [sic]. That is derived from the formula =PMT((1+9%)^(1/12)-1, 5*12, 0, -2307936, 0), which results in 30882.4752290111.

2. IMHO, payments should be at the beginning of periods, not at the end, so that we earn a return on all payments. This is contrary to msvariar's implicit assumption that payments are at the end of periods. So the correct formula should be =PMT((1+9%)^(1/12)-1, 5*12, 0, -2307936, 1). That results in 30661.4879718442.

Based on those assumptions, the initial payment can be calculated as follows.

	A	B	C
1		Annual	Monthly
2	Yield	9.00%	0.7207%
3	Nper	5	60
4	%Pmt inc	10.00%
5	FV goal	2,307,936.00
6	Init pmt	294,545.69	25,526.53

<tbody>
</tbody>

Formulas:
C2: =(1+B2)^(1/12) - 1
C3: =B3*12
B6: =B5 / SUMPRODUCT((1+B2)^(B3+1-ROW(A1:INDEX(A:A,B3,1))) * (1+B4)^(ROW(A1:INDEX(A:A,B3,1))-1))
C6: =PMT(C2,12,0,-B6*(1+B2),1)

The correctness of the initial annual payment in B6 can be demonstrated as follows:

	E	F	G	H	I	J
8	Pmt#	Pmt/yr	End Bal		Pmt/mo	%Pmt inc
9	1	294,545.69	321,054.80		25,526.53
10	2	324,000.26	703,110.02		28,079.18	10.00%
11	3	356,400.29	1,154,866.23		30,887.10	10.00%
12	4	392,040.31	1,686,128.13		33,975.81	10.00%
13	5	431,244.35	2,307,936.00		37,373.39	10.00%

<tbody>
</tbody>

Formulas:
F9: =B6
G9: =(N(G8)+F9)*(1+$B$2)
I9: =PMT($C$2, 12, N(G8), -G9, 1)
F10: =F9*(1+$B$4)
J10: =I10/I9 - 1

Copy G9 into G10. Copy I9 into I10. Copy F10:J10 into F11:J13.

The correctness of the initial monthly payment in C6 can be demonstrated with a table similar to the one in my previous posting, to wit:

	A	B	C
8	Pmt#	Pmt	End Bal
9	1	25,526.53	25,710.51
10	2	25,526.53	51,606.32
19	11	25,526.53	293,230.88
20	12	25,526.53	321,054.80
21	13	28,079.18	351,650.31
22	14	28,079.18	382,466.32
67	59	37,373.39	2,254,047.59
68	60	37,373.39	2,307,936.00

<tbody>
</tbody>

Formulas:
A9: =N(A8)+1
B9: =C6
C9: =(N(C8)+B9)*(1+$C$2)
B10: =IF(MOD(A10-1,12)=0, B9*(1+$B$4), B9)

Copy A9 into A10. Copy C9 into C10. Copy A10:C10 into A11:C68.


-----
For the curious, the mathematical derivation of the formula in B6 (initial annual payment) is as follows.

PMT*(1+10%)^0*(1+9%)^5 + PMT*(1+10%)^1*(1+9%)^4 +...+ PMT*(1+10%)^4*(1+9%)^1 = FV (2,307,936)

PMT = FV / ( (1+10%)^0*(1+9%)^5 + (1+10%)^1*(1+9%)^4 +...+ (1+10%)^4*(1+9%)^1 )

Note that (1+10%)^0 = 1. I include it in order to make the exponential series clearer.