Question

You have just purchased a home and taken out a $480,000 mortgage. The mortgage has a 30​-year term with monthly payments and an APR of 6.80%.

a. How much will you pay in​ interest, and how much will you pay in​ principal, during the first​ year?

b. How much will you pay in​ interest, and how much will you pay in​ principal, during the 20th year​ (i.e., between 19 and 20 years from​ now)?

Answer 1

a. EMI for 360 months on a Loan of $480000 is $3129.

The formula to be used in Excel is =PMT(Annual Rate/periods,no.of periods in a year, Loan Amount)

Interest is calculated on opening principal

MONTHS	EMI	INTEREST	PRINCIPAL REPAYMENT	OST PRINCIPAL
0				480000
1	3,129	2720	409	479591
2	3,129	2718	412	479179
3	3,129	2715	414	478765
4	3,129	2713	416	478349
5	3,129	2711	419	477930
6	3,129	2708	421	477510
7	3,129	2706	423	477086
8	3,129	2703	426	476660
9	3,129	2701	428	476232
10	3,129	2699	431	475802
11	3,129	2696	433	475369
12	3,129	2694	435	474933

Hence, total interest during the first year is $29790.

Total principal during the first year is $4631 (($3129*12)-$29790)

(b) Opening principal following the table is $289999

229	3,129	1645	1484	288811
230	3,129	1637	1493	287318
231	3,129	1628	1501	285817
232	3,129	1620	1510	284307
233	3,129	1611	1518	282789
234	3,129	1602	1527	281262
235	3,129	1594	1535	279727
236	3,129	1585	1544	278183
237	3,129	1576	1553	276630
238	3,129	1568	1562	275068
239	3,129	1559	1571	273498
240	3,129	1550	1579	271918

Total interest during 20th year is $19155.

Total principal during 20th year is $18358