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