Question

Sarah secured a bank loan of $155,000 for the purchase of a house. The mortgage is to be amortized through monthly payments for a term of 15 years, with an interest rate of 3%/year compounded monthly on the unpaid balance. She plans to sell her house in 5 years. How much will Sarah still owe on her house? (Round your answer to the nearest cent.)

Answer 1

EMI :

EMI = Loan / PVAF (r%, n)
PVAF = SUm [ PVF(r%, n) ]
PVF(r%, n) = 1 / ( 1 + r)^n
r = Int rate per period
n = No. of periods

How to calculate PVAF using Excel:
=PV(Rate,NPER,-1)
Rate = Disc Rate
NPER = No.of periods

Particulars	Amount
Loan Amount	$         1,55,000.00
Int rate per Month	0.2500%
No. of Months	180

EMI = Loan Amount / PVAF (r%, n)
Where r is Int rate per Month & n is No. of Months
= $ 155000 / PVAF (0.0025 , 180)
= $ 155000 / 144.8055
= $ 1070.4

Particulars	Amount
Loan Amount	$ 1,55,000.00
Int rate per Month	0.2500%
No. of Months	180
Outstanding Bal after	60
EMI	$      1,070.40
Payments Left	120

Outstanding Bal = Instalment * [ 1 - ( 1 + r )^ - n ] / r
= $ 1070.4 * [ 1 - ( 1 + 0.0025 ) ^ - 120 ] / 0.0025
= $ 1070.4 * [ 1 - ( 1.0025 ) ^ - 120 ] / 0.0025
= $ 1070.4 * [ 1 - 0.741096 ] / 0.0025
= $ 1070.4 * [ 0.258904 ] / 0.0025
= $ 110852.34

r = Int Rate per period
n = Balance No. of periods
Amount still owede after 5 Years is $ 110852.34

Amortization Table:

Period	Opening Bal	EMI	Int	Principal Repay	Closing Outstanding
1	$         1,55,000.00	$         1,070.40	$               387.50	$                682.90	$            1,54,317.10
2	$         1,54,317.10	$         1,070.40	$               385.79	$                684.61	$            1,53,632.49
3	$         1,53,632.49	$         1,070.40	$               384.08	$                686.32	$            1,52,946.17
4	$         1,52,946.17	$         1,070.40	$               382.37	$                688.04	$            1,52,258.13
5	$         1,52,258.13	$         1,070.40	$               380.65	$                689.76	$            1,51,568.38
6	$         1,51,568.38	$         1,070.40	$               378.92	$                691.48	$            1,50,876.90
7	$         1,50,876.90	$         1,070.40	$               377.19	$                693.21	$            1,50,183.69
53	$         1,17,128.02	$         1,070.40	$               292.82	$                777.58	$            1,16,350.43
54	$         1,16,350.43	$         1,070.40	$               290.88	$                779.53	$            1,15,570.91
55	$         1,15,570.91	$         1,070.40	$               288.93	$                781.47	$            1,14,789.43
56	$         1,14,789.43	$         1,070.40	$               286.97	$                783.43	$            1,14,006.01
57	$         1,14,006.01	$         1,070.40	$               285.02	$                785.39	$            1,13,220.62
58	$         1,13,220.62	$         1,070.40	$               283.05	$                787.35	$            1,12,433.27
59	$         1,12,433.27	$         1,070.40	$               281.08	$                789.32	$            1,11,643.95
60	$         1,11,643.95	$         1,070.40	$               279.11	$                791.29	$           1,10,852.66
61	$         1,10,852.66	$         1,070.40	$               277.13	$                793.27	$            1,10,059.39
62	$         1,10,059.39	$         1,070.40	$               275.15	$                795.25	$            1,09,264.14

Pls do rate, if the answer is correct and comment, if any further assistance is required.