Question 468259: Please help me with this question.?
Lauren takes out a $85000 home loan at 12% pa interest (compounded monthly) and chooses to repay it over a 20 year period.
a.) find the size of Lauren's monthly repayments.
after 10 years of making regular payments Lauren is surprised to find that she has repaid less than half of her loan
b.) How much of Lauren's loan has been repaid?
Answer by Theo(13342)   (Show Source):
You can put this solution on YOUR website! Here's a printout from an Excel worksheet that shows you the month by month calculations of Balance, Payment, and Interest.
In time period 1, the interest was $850 and the capital was $86 dollars. That's the difference between the payment of $936 and the interest of $850.
The balance of the loan dropped by the amount of capital (amortization) which was only $86.
In time period 200 (scroll down to see it), the interest was $313 on a previous time period balance of $31,353. The capital portion of the payment was $623 which reduced the balance from $31,353 (in time period 199) to $30,730 (in time period 200). The interest was a lot lower because the balance that the interest was being charged against was much lower.
You can see that, as the loan progresses, the interest charged becomes progressively less and the amount of capital to amortize the loan becomes progressively more.
LOOK BELOW THE PRINTOUT FROM THE EXCEL SPREADSHEET TO SEE HOW TO CALCULATE THIS USING THE FINANCIAL FORMULAS YOU HAVE BEEN PROVIDED.
TP REMBAL PMT INT
0 $85,000.00 $935.92 $0.00
1 $84,914.08 $935.92 $850.00
2 $84,827.29 $935.92 $849.14
3 $84,739.64 $935.92 $848.27
4 $84,651.12 $935.92 $847.40
5 $84,561.71 $935.92 $846.51
6 $84,471.40 $935.92 $845.62
7 $84,380.19 $935.92 $844.71
8 $84,288.07 $935.92 $843.80
9 $84,195.03 $935.92 $842.88
10 $84,101.05 $935.92 $841.95
11 $84,006.14 $935.92 $841.01
12 $83,910.28 $935.92 $840.06
13 $83,813.46 $935.92 $839.10
14 $83,715.67 $935.92 $838.13
15 $83,616.90 $935.92 $837.16
16 $83,517.15 $935.92 $836.17
17 $83,416.40 $935.92 $835.17
18 $83,314.64 $935.92 $834.16
19 $83,211.86 $935.92 $833.15
20 $83,108.06 $935.92 $832.12
21 $83,003.21 $935.92 $831.08
22 $82,897.32 $935.92 $830.03
23 $82,790.37 $935.92 $828.97
24 $82,682.35 $935.92 $827.90
25 $82,573.25 $935.92 $826.82
26 $82,463.06 $935.92 $825.73
27 $82,351.77 $935.92 $824.63
28 $82,239.36 $935.92 $823.52
29 $82,125.84 $935.92 $822.39
30 $82,011.17 $935.92 $821.26
31 $81,895.36 $935.92 $820.11
32 $81,778.39 $935.92 $818.95
33 $81,660.25 $935.92 $817.78
34 $81,540.93 $935.92 $816.60
35 $81,420.42 $935.92 $815.41
36 $81,298.70 $935.92 $814.20
37 $81,175.76 $935.92 $812.99
38 $81,051.59 $935.92 $811.76
39 $80,926.19 $935.92 $810.52
40 $80,799.53 $935.92 $809.26
41 $80,671.60 $935.92 $808.00
42 $80,542.39 $935.92 $806.72
43 $80,411.89 $935.92 $805.42
44 $80,280.09 $935.92 $804.12
45 $80,146.96 $935.92 $802.80
46 $80,012.51 $935.92 $801.47
47 $79,876.71 $935.92 $800.13
48 $79,739.56 $935.92 $798.77
49 $79,601.03 $935.92 $797.40
50 $79,461.12 $935.92 $796.01
51 $79,319.80 $935.92 $794.61
52 $79,177.08 $935.92 $793.20
53 $79,032.93 $935.92 $791.77
54 $78,887.33 $935.92 $790.33
55 $78,740.28 $935.92 $788.87
56 $78,591.76 $935.92 $787.40
57 $78,441.76 $935.92 $785.92
58 $78,290.25 $935.92 $784.42
59 $78,137.23 $935.92 $782.90
60 $77,982.68 $935.92 $781.37
61 $77,826.58 $935.92 $779.83
62 $77,668.93 $935.92 $778.27
63 $77,509.69 $935.92 $776.69
64 $77,348.87 $935.92 $775.10
65 $77,186.43 $935.92 $773.49
66 $77,022.37 $935.92 $771.86
67 $76,856.67 $935.92 $770.22
68 $76,689.32 $935.92 $768.57
69 $76,520.29 $935.92 $766.89
70 $76,349.57 $935.92 $765.20
71 $76,177.14 $935.92 $763.50
72 $76,002.99 $935.92 $761.77
73 $75,827.09 $935.92 $760.03
74 $75,649.44 $935.92 $758.27
75 $75,470.01 $935.92 $756.49
76 $75,288.79 $935.92 $754.70
77 $75,105.75 $935.92 $752.89
78 $74,920.89 $935.92 $751.06
79 $74,734.17 $935.92 $749.21
80 $74,545.59 $935.92 $747.34
81 $74,355.12 $935.92 $745.46
82 $74,162.75 $935.92 $743.55
83 $73,968.46 $935.92 $741.63
84 $73,772.22 $935.92 $739.68
85 $73,574.02 $935.92 $737.72
86 $73,373.83 $935.92 $735.74
87 $73,171.65 $935.92 $733.74
88 $72,967.44 $935.92 $731.72
89 $72,761.19 $935.92 $729.67
90 $72,552.88 $935.92 $727.61
91 $72,342.49 $935.92 $725.53
92 $72,129.99 $935.92 $723.42
93 $71,915.37 $935.92 $721.30
94 $71,698.60 $935.92 $719.15
95 $71,479.66 $935.92 $716.99
96 $71,258.53 $935.92 $714.80
97 $71,035.20 $935.92 $712.59
98 $70,809.62 $935.92 $710.35
99 $70,581.80 $935.92 $708.10
100 $70,351.69 $935.92 $705.82
101 $70,119.29 $935.92 $703.52
102 $69,884.56 $935.92 $701.19
103 $69,647.48 $935.92 $698.85
104 $69,408.03 $935.92 $696.47
105 $69,166.19 $935.92 $694.08
106 $68,921.92 $935.92 $691.66
107 $68,675.22 $935.92 $689.22
108 $68,426.05 $935.92 $686.75
109 $68,174.39 $935.92 $684.26
110 $67,920.21 $935.92 $681.74
111 $67,663.49 $935.92 $679.20
112 $67,404.20 $935.92 $676.63
113 $67,142.32 $935.92 $674.04
114 $66,877.82 $935.92 $671.42
115 $66,610.67 $935.92 $668.78
116 $66,340.86 $935.92 $666.11
117 $66,068.34 $935.92 $663.41
118 $65,793.10 $935.92 $660.68
119 $65,515.11 $935.92 $657.93
120 $65,234.34 $935.92 $655.15
121 $64,950.76 $935.92 $652.34
122 $64,664.34 $935.92 $649.51
123 $64,375.06 $935.92 $646.64
124 $64,082.89 $935.92 $643.75
125 $63,787.79 $935.92 $640.83
126 $63,489.75 $935.92 $637.88
127 $63,188.72 $935.92 $634.90
128 $62,884.69 $935.92 $631.89
129 $62,577.61 $935.92 $628.85
130 $62,267.46 $935.92 $625.78
131 $61,954.22 $935.92 $622.67
132 $61,637.83 $935.92 $619.54
133 $61,318.29 $935.92 $616.38
134 $60,995.55 $935.92 $613.18
135 $60,669.58 $935.92 $609.96
136 $60,340.35 $935.92 $606.70
137 $60,007.83 $935.92 $603.40
138 $59,671.99 $935.92 $600.08
139 $59,332.79 $935.92 $596.72
140 $58,990.19 $935.92 $593.33
141 $58,644.17 $935.92 $589.90
142 $58,294.69 $935.92 $586.44
143 $57,941.71 $935.92 $582.95
144 $57,585.21 $935.92 $579.42
145 $57,225.13 $935.92 $575.85
146 $56,861.46 $935.92 $572.25
147 $56,494.15 $935.92 $568.61
148 $56,123.17 $935.92 $564.94
149 $55,748.48 $935.92 $561.23
150 $55,370.04 $935.92 $557.48
151 $54,987.82 $935.92 $553.70
152 $54,601.77 $935.92 $549.88
153 $54,211.87 $935.92 $546.02
154 $53,818.06 $935.92 $542.12
155 $53,420.32 $935.92 $538.18
156 $53,018.60 $935.92 $534.20
157 $52,612.86 $935.92 $530.19
158 $52,203.07 $935.92 $526.13
159 $51,789.18 $935.92 $522.03
160 $51,371.15 $935.92 $517.89
161 $50,948.93 $935.92 $513.71
162 $50,522.50 $935.92 $509.49
163 $50,091.80 $935.92 $505.23
164 $49,656.80 $935.92 $500.92
165 $49,217.44 $935.92 $496.57
166 $48,773.69 $935.92 $492.17
167 $48,325.51 $935.92 $487.74
168 $47,872.84 $935.92 $483.26
169 $47,415.64 $935.92 $478.73
170 $46,953.88 $935.92 $474.16
171 $46,487.49 $935.92 $469.54
172 $46,016.44 $935.92 $464.87
173 $45,540.69 $935.92 $460.16
174 $45,060.17 $935.92 $455.41
175 $44,574.85 $935.92 $450.60
176 $44,084.67 $935.92 $445.75
177 $43,589.60 $935.92 $440.85
178 $43,089.57 $935.92 $435.90
179 $42,584.54 $935.92 $430.90
180 $42,074.46 $935.92 $425.85
181 $41,559.29 $935.92 $420.74
182 $41,038.96 $935.92 $415.59
183 $40,513.42 $935.92 $410.39
184 $39,982.63 $935.92 $405.13
185 $39,446.54 $935.92 $399.83
186 $38,905.08 $935.92 $394.47
187 $38,358.21 $935.92 $389.05
188 $37,805.86 $935.92 $383.58
189 $37,248.00 $935.92 $378.06
190 $36,684.56 $935.92 $372.48
191 $36,115.48 $935.92 $366.85
192 $35,540.71 $935.92 $361.15
193 $34,960.19 $935.92 $355.41
194 $34,373.87 $935.92 $349.60
195 $33,781.69 $935.92 $343.74
196 $33,183.58 $935.92 $337.82
197 $32,579.49 $935.92 $331.84
198 $31,969.37 $935.92 $325.79
199 $31,353.14 $935.92 $319.69
200 $30,730.74 $935.92 $313.53
201 $30,102.13 $935.92 $307.31
202 $29,467.23 $935.92 $301.02
203 $28,825.98 $935.92 $294.67
204 $28,178.31 $935.92 $288.26
205 $27,524.17 $935.92 $281.78
206 $26,863.49 $935.92 $275.24
207 $26,196.20 $935.92 $268.63
208 $25,522.24 $935.92 $261.96
209 $24,841.54 $935.92 $255.22
210 $24,154.03 $935.92 $248.42
211 $23,459.65 $935.92 $241.54
212 $22,758.32 $935.92 $234.60
213 $22,049.98 $935.92 $227.58
214 $21,334.56 $935.92 $220.50
215 $20,611.98 $935.92 $213.35
216 $19,882.18 $935.92 $206.12
217 $19,145.08 $935.92 $198.82
218 $18,400.61 $935.92 $191.45
219 $17,648.69 $935.92 $184.01
220 $16,889.25 $935.92 $176.49
221 $16,122.22 $935.92 $168.89
222 $15,347.52 $935.92 $161.22
223 $14,565.07 $935.92 $153.48
224 $13,774.80 $935.92 $145.65
225 $12,976.62 $935.92 $137.75
226 $12,170.47 $935.92 $129.77
227 $11,356.25 $935.92 $121.70
228 $10,533.89 $935.92 $113.56
229 $9,703.30 $935.92 $105.34
230 $8,864.41 $935.92 $97.03
231 $8,017.13 $935.92 $88.64
232 $7,161.38 $935.92 $80.17
233 $6,297.07 $935.92 $71.61
234 $5,424.12 $935.92 $62.97
235 $4,542.44 $935.92 $54.24
236 $3,651.94 $935.92 $45.42
237 $2,752.54 $935.92 $36.52
238 $1,844.14 $935.92 $27.53
239 $926.66 $935.92 $18.44
240 $0.00 $935.92 $9.27
The method used to find the remaining balance (unless you calculator gives you a button to find it directly) is as follows:
First use the Payment from a Present Value formula to get the monthly payments.
This formula might also be called Annuity from a Present Amount, or Payment from a Present Amount.
In that formula:
Present Amount / Value = $85,000
Monthly Interest Rate = .01
Number of Months = 240
Payments at the end of the month are assumed.
If you did it correctly, you will see that the monthly payment is $935.9232135.
That was step 1.
Now that you have the monthly payments, do the following:
Use the Present Value of Payments formula.
This formula might also be called Present Value of an Annuity.
In that formula:
Payment = $935.9232135
Interest Rate = .01
Number of Time Periods = 240
You should get a Present Value / Amount of $85,000
This is obvious since you used the Present Value of $85,000 to calculate the payment.
Now you want to find the remaining balance after 10 years.
Use the Present Value of a Payments formula again, only this time make the number of time periods = 120.
That's the number of remaining months in the loan.
You will find the Present Value from that.
The difference between the Present Value of Payments for 240 months and the Present Value of Payments for 120 months is the amount of the loan that was amortized, or paid off in the first 10 years.
Some Examples:
Present Value for 240 months = $85,000
This will show up as REMBAL in TP 0 of the Excel Printout.
Present Value for 120 months = $65,234
This will show up as REMBAL in TP 120 of the Excel Printout.
Present Value for 60 months = $42,074
This will show up as REMBAL in TP 180 of the Excel Printout.
Present Value for 24 months = $19,882
This will show up as REMBAL in TP 216 of the Excel Printout.
Present Value for 12 months = $10533
This will show up as REMBAL in TP 228 of the Excel Printout.
Present Value for 1 month = $926
This will show up as REMBAL in TP 239 of the Excel Printout.
These values are the amount of the loan that still remains to be paid off.
If you have 240 months to go, then the full amount of $85,000 needs to be paid off.
If you have 12 months to go, then $10,533 remains to be paid off.
If you only have 1 month to go, then $926 remains to be paid off.
The difference from $85,000 is what has already been paid off.
With 240 months to go, $85,000 minus $85,000 = $0 has been paid off.
With 24 months to go, $85,000 - $19,882 = $65,118 has been paid off.
With 120 months to go, $85,000 - $65,234 = $19,766 has been paid off.
This is the procedure you would use to find the remaining balance using the formulas.
The formulas you will use are:
PAYMENT FOR A PRESENT VALUE
PMT = Payment per time period
PV = Present Value
i = Interest Rate per Time Period
n = Number of Time Periods
PRESENT VALUE OF A PAYMENT
PV = Present Value
PMT = Payment per time period
i = Interest Rate per Time Period
n = Number of Time Periods
These formulas come from the following lesson that I wrote:
BASIC FORMULAS AND ASSUMPTIONS USED IN FINANCIAL FORMULAS
|
|
|
