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

M

msvariar

New Member
Joined
May 10, 2019
Messages
1
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
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:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Annual[/TD]
[TD="align: right"]Monthly[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]Yield[/TD]
[TD="align: right"]9.00%[/TD]
[TD="align: right"]0.7207%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]Pmt#[/TD]
[TD="align: right"]Pmt[/TD]
[TD="align: right"]End Bal[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"]25,710.51[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"]51,606.32[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"]293,230.88[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"]321,054.80[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]28,079.18[/TD]
[TD="align: right"]351,650.31[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]28,079.18[/TD]
[TD="align: right"]382,466.32[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]63[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]37,373.39[/TD]
[TD="align: right"]2,254,047.59[/TD]
[/TR]
[TR]
[TD="align: center"]64[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]37,373.39[/TD]
[TD="align: right"]2,307,936.00[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Last edited:
Upvote 0
a)Try building a table with your information and then use Goal Seek.
b)Try the UDF shown below


Excel 2010
ABCDE
1Payment25,301.99
2Rate9%
3Term5
4Escalating10%
5Future$2,307,936.00
6
7
8125,301.99189.7625,491.75
3aaa
Cell Formulas
RangeFormula
D5=nSave(B1,B2,B3,1+B4)
D8=B8*B2/12
B8=B1
E8=B8+D8

Code: 
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
 
Last edited:
Upvote 0
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.

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Annual[/TD]
[TD="align: right"]Monthly[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]Yield[/TD]
[TD="align: right"]9.00%[/TD]
[TD="align: right"]0.7207%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]Nper[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]%Pmt inc[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]FV goal[/TD]
[TD="align: right"]2,307,936.00[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]Init pmt[/TD]
[TD="align: right"]294,545.69[/TD]
[TD="align: right"]25,526.53[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]Pmt#[/TD]
[TD="align: right"]Pmt/yr[/TD]
[TD="align: right"]End Bal[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Pmt/mo[/TD]
[TD="align: right"]%Pmt inc[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]294,545.69[/TD]
[TD="align: right"]321,054.80[/TD]
[TD="align: right"][/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]324,000.26[/TD]
[TD="align: right"]703,110.02[/TD]
[TD="align: right"][/TD]
[TD="align: right"]28,079.18[/TD]
[TD="align: right"]10.00%[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]356,400.29[/TD]
[TD="align: right"]1,154,866.23[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30,887.10[/TD]
[TD="align: right"]10.00%[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]392,040.31[/TD]
[TD="align: right"]1,686,128.13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]33,975.81[/TD]
[TD="align: right"]10.00%[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]431,244.35[/TD]
[TD="align: right"]2,307,936.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37,373.39[/TD]
[TD="align: right"]10.00%[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]Pmt#[/TD]
[TD="align: right"]Pmt[/TD]
[TD="align: right"]End Bal[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"]25,710.51[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"]51,606.32[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"]293,230.88[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"]321,054.80[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]28,079.18[/TD]
[TD="align: right"]351,650.31[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]28,079.18[/TD]
[TD="align: right"]382,466.32[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]67[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]37,373.39[/TD]
[TD="align: right"]2,254,047.59[/TD]
[/TR]
[TR]
[TD="align: center"]68[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]37,373.39[/TD]
[TD="align: right"]2,307,936.00[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Last edited:
Upvote 1
Solution
You must log in or register to reply here.

Similar threads

SimonHughes
  • Solved
Intrest v Drawdown Calculation Required
2
Replies
18
Views
1K
SimonHughes
SimonHughes
O
  • Solved
How to calculate annual average growth (i.e., price appreciation) rate for an investment portfolio.
Replies
3
Views
111
kvsrinivasamurthy
K
JenniferMurphy
  • Question Question
Is this a good solution to Excel's table header weaknesses?
Replies
3
Views
301
zero269
Z
JenniferMurphy
  • Question Question
Are these retirement calculations correct?
Replies
6
Views
456
JenniferMurphy
JenniferMurphy
J
  • Solved
PV Formula: Compounded Semi-Annually (Mortgage)
2
Replies
13
Views
2K
Dave Patton
D

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

Share this page

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