Calculate PMT with rising principle

[mactoolsix]

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?

 

[mactoolsix]

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?

 

[joeu2004]

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.)

 

[mactoolsix]

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!!

 

[joeu2004]

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
	A	B	C	D	E	F	G	H	I
1	Current cost	10,000.00
2	Annl inflt rate	10.00%
3	Term	10	yrs
4	Future cost	25,937.42
5
6	Annl int rate	10.00%
7	Term	120	mo
8	Pmt/mo	126.62
9
10		Apprec	End Bal			Pmt	Int	End Bal	Annl Int
11			10,000.00
12	1	1,000.00	11,000.00		1	126.62	0.00	126.62
13	2	1,100.00	12,100.00		2	126.62	1.06	254.29
14	3	1,210.00	13,310.00		3	126.62	2.12	383.03
15	4	1,331.00	14,641.00		4	126.62	3.19	512.85
16	5	1,464.10	16,105.10		5	126.62	4.27	643.74
17	6	1,610.51	17,715.61		6	126.62	5.36	775.72
18	7	1,771.56	19,487.17		7	126.62	6.46	908.81
19	8	1,948.72	21,435.89		8	126.62	7.57	1,043.00
20	9	2,143.59	23,579.48		9	126.62	8.69	1,178.31
21	10	2,357.95	25,937.42		10	126.62	9.82	1,314.75
22	TOTAL	15,937.42			11	126.62	10.96	1,452.33
23					12	126.62	12.10	1,591.05	71.61
35					24	126.62	26.63	3,348.70	238.22
47					36	126.62	42.68	5,290.40	422.27
59					48	126.62	60.40	7,435.43	625.59
71					60	126.62	79.99	9,805.06	850.20
83					72	126.62	101.62	12,422.83	1,098.33
95					84	126.62	125.52	15,314.71	1,372.44
107					96	126.62	151.92	18,509.41	1,675.26
119					108	126.62	181.09	22,038.64	2,009.79
131					120	126.62	213.31	25,937.42	2,379.35
132					TOTAL	15,194.37	10,743.05		10,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>

 

[mactoolsix]

Joe,
Thank you for taking the time to post an excellent explanation!!
Followed your chart and thoroughly understood it.