Calculate PMT with rising principle

mactoolsix

Board Regular
Joined
Nov 30, 2010
Messages
105
How can I calculate the monthly payment required to save for an item with a current cost of $10,000 rising at 5% @ year with 1.5% inflation and receiving 10% on the saved funds?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
How can I calculate the monthly payment required to save for an item with a current cost of $10,000 rising at 5% @ year with 1.5% inflation and receiving 10% on the saved funds?

I used this (1+10%)/(1+1.5%-5%)-1 to get the real rate of return = 13.4%

Then FV(13.4%,10,,-10000,0) = 35,173

Then PMT(13.4%/12,(12*10),0,-35173,0) = 140.72

This seems to work as I raise the 5% cost the monthly payment does increase.

I'm questioning my real rate of return being higher than 10%???

Can someone confirm or provide me with a correction?
 
Upvote 0
You neglected to specify how far in the future you want to purchase the item. But from your calculations, we can infer that that is 10 years.

I don't believe the 1.5% inflation rate is relevant at all. There are many measures of inflation. But in simple terms, inflation is the average rate at which costs increase. But if we know the rate at which a specific costs increases, we use that rate. Typical example: current CPI is around 2%, which we apply to most consumer costs; but for medical expenses, we use 7% inflation.

Likewise, the interest rate (10%) is the annual rate to use, unless you expect that the interest rate will increase by the rate of inflation(!). But in that case, the interest rate is variable(!). And you would need to tell us how the inflation rate should affect the interest rate.

Assuming a fixed interest rate, I believe the monthly payment is simply:

=PMT(10%/12, 12*10, 0, -FV(5%, 10, 0, -10000))
or
=PMT(10%/12, 12*10, 0, -FV(5%, 10, 0, -10000), 1)

(You must decide whether payments are made at the end or, more likely, the beginning of periods.)
 
Last edited:
Upvote 0
Joe - thanks for the reply!

The term (time before use is 10 years).

Growth of investment (Inflation rate) =10% (no additional increase due to inflation)
Term (nper) =12*10
Present value = 0
FV = Inflation rate (of the asset) = 5%
Term (nper) = 10 yrs
PV = 10,000 (-10000)
Type (end) = 1

Using your formula: PMT(10%/12, 12*10, 0, -FV(5%, 10, 0, -10000), 1) = 79.52 monthly investment

79.52 x 120 months = 9,542

To test this I changed the 5% inflation to 10% (equal to growth) and changes to 126.62 @ month
(10%/12,120,-FV(10%,10,,-10,000,1)

126.62 x 120 = 15,194
If the inflation rate and growth rate are the same, shouldn't the total monthly payments = 10,000?

Thanks again!!
 
Upvote 0
Growth of investment (Inflation rate) =10% (no additional increase due to inflation)
Term (nper) =12*10
Present value = 0
FV = Inflation rate (of the asset) = 5%
Term (nper) = 10 yrs
PV = 10,000 (-10000)
Type (end) = 1

No. Type=1 means payments at the beginning, not the end. Which do you want?

Using your formula: PMT(10%/12, 12*10, 0, -FV(5%, 10, 0, -10000), 1) = 79.52 monthly investment
[....]
To test this I changed the 5% inflation to 10% (equal to growth) and changes to 126.62 @ month
(10%/12,120,-FV(10%,10,,-10,000,1)

The first formula is indeed what I posted; but it should result in about 78.86, not about 79.52.

I suspect you made the same mistake when entering the first formula that you made in the second formula: you put the ",1" (type=1) in the FV function, not in the PMT function.

So the PMT function is effectively type=0 (end of period). And the "type" makes no difference for the FV function, since pmt=0.

126.62 x 120 = 15,194
If the inflation rate and growth rate are the same, shouldn't the total monthly payments = 10,000?

No. But to understand, it would help if I had separated the FV() and PMT() calculations, as you had done.

I was trying to emphasize the "need" to use the exact FV() result, not how it appears in a cell due to formatting, at least for comparison purposes in the table below.

In real life, the FV() result should be rounded (up) to the cent, since we're talking about real payments. But you can choose to round (up) to any lesser precision (i.e. to the left). In either case, if we do that, we have to make a special case for the last payment. I can demonstrate; but for now, I think it is a complication that obfuscates the principles.

FV(10%,10,0,-10000) is about 25,937.42 in B4.

We need to make 120 monthly payments so that we will have 25,937.42 in 10 years.

If we did not earn interest on the payments, the monthly payment would be B4 / 120 = about 216.15.

But since we earn 10%/12 interest per month, the monthly payment is PMT(10%/12,120,0,-B4) = about 126.62 in B8, assuming payments at the end of periods (type=0).

Again, I believe that type=1 (payments the beginning of periods) is more likely. It might just depend on how we define a "period".

Note that 120 * B8 = about 15,194.37.

(It is not 120 * 126.62 = 15,194.40 because B8 is really 126.619772375395.)

120*pmt is not 25,937.42 because we earn interest monthly. The total interest is B4 - 120*B8 = 10,743.05.

120*pmt is not 10,000 because the cumulative monthly interest per year is less than the annual appreciation. This is demonstrated by the following table.


Book1
ABCDEFGHI
1Current cost10,000.00
2Annl inflt rate10.00%
3Term10yrs
4Future cost25,937.42
5
6Annl int rate10.00%
7Term120mo
8Pmt/mo126.62
9
10ApprecEnd BalPmtIntEnd BalAnnl Int
1110,000.00
1211,000.0011,000.001126.620.00126.62
1321,100.0012,100.002126.621.06254.29
1431,210.0013,310.003126.622.12383.03
1541,331.0014,641.004126.623.19512.85
1651,464.1016,105.105126.624.27643.74
1761,610.5117,715.616126.625.36775.72
1871,771.5619,487.177126.626.46908.81
1981,948.7221,435.898126.627.571,043.00
2092,143.5923,579.489126.628.691,178.31
21102,357.9525,937.4210126.629.821,314.75
22TOTAL15,937.4211126.6210.961,452.33
2312126.6212.101,591.0571.61
3524126.6226.633,348.70238.22
4736126.6242.685,290.40422.27
5948126.6260.407,435.43625.59
7160126.6279.999,805.06850.20
8372126.62101.6212,422.831,098.33
9584126.62125.5215,314.711,372.44
10796126.62151.9218,509.411,675.26
119108126.62181.0922,038.642,009.79
131120126.62213.3125,937.422,379.35
132TOTAL15,194.3710,743.0510,743.05
Sheet1
<br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=FV(<font color="Blue">B2,B3,0,-B1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B7</th><td style="text-align:left">=12*B3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">=PMT(<font color="Blue">B6/12,B7,0,-B4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C11</th><td style="text-align:left">=B1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A12</th><td style="text-align:left">=A11+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B12</th><td style="text-align:left">=C11*$B$2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C12</th><td style="text-align:left">=C11+B12</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B22</th><td style="text-align:left">=SUM(<font color="Blue">B12:B21</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E12</th><td style="text-align:left">=E11+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F12</th><td style="text-align:left">=$B$8</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G12</th><td style="text-align:left">=H11*$B$6/12</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H12</th><td style="text-align:left">=H11+F12+G12</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I12</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">E12,12</font>)=0,SUM(<font color="Red">OFFSET(<font color="Green">G12,-11,0,12,1</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F132</th><td style="text-align:left">=SUM(<font color="Blue">F12:F131</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G132</th><td style="text-align:left">=SUM(<font color="Blue">G12:G131</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I132</th><td style="text-align:left">=SUM(<font color="Blue">I12:I131</font>)</td></tr></tbody></table></td></tr></table>
 
Last edited:
Upvote 0
Joe,
Thank you for taking the time to post an excellent explanation!!
Followed your chart and thoroughly understood it.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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