Question

You are thinking of purchasing a house that costs $245,000. You have $13,000 in cash that you can use as a down payment but you need to borrow the rest of the purchase price. The bank is offering a 30 year mortgage that requires monthly payments and has an annual interest rate of 4.35% per year. What will your monthly payments be if you sign up for this mortgage? Draw the amortization schedule on a monthly basis using Excel. Calculate the total amount of interest paid throughout the life of the loan. Create a graph depicting the changes in the portions of interest and principal for each monthly payment throughout the life of the loan.

Suppose the interest rate decreases to 3.65% per year and the length of repayment decreases to 15 years. What will the new monthly payment be? Draw a new amortization schedule in a seperate Excel sheet. Calculate the total amount of interest paid throughout the life of the loan. How much do you save if you go with the 15 year mortgage versus the 30 year mortgage example above?

Answer 1

SITUATION- 1

LOAN $245000 - $13000 = $232000

YEARS = 30, INTEREST RATE = 4.35% P.A.

MONTHLY PAYMENTS = EMI = $1155

TOTAL INETERST PAYABLE = $183772

TOTAL PAYMENT = PRINCIPAL + INTEREST = $415772

FORMULA USED =

E = P×r×(1 + r)ⁿ/((1 + r)ⁿ - 1

WHERE, P= PRINCIPAL AMOUNT, r = RATE OF INTEREST, n = NO.OF YEARS

AMORTIZATION TABLE
LOAN $245000, DOWN PAYMENT $13000, INT. RATE. 4.35% P.A., YEARS 30
YEAR	PRINCIPAL	INTEREST	PAYMENT	BALANCE
1	3,843	10,016	13,859	2,28,157
2	4,014	9,845	13,859	2,24,143
3	4,192	9,667	13,859	2,19,951
4	4,378	9,481	13,859	2,15,574
5	4,572	9,287	13,859	2,11,002
6	4,775	9,084	13,859	2,06,227
7	4,987	8,872	13,859	2,01,240
8	5,208	8,651	13,859	1,96,032
9	5,439	8,420	13,859	1,90,592
10	5,681	8,178	13,859	1,84,912
11	5,933	7,926	13,859	1,78,979
12	6,196	7,663	13,859	1,72,783
13	6,471	7,388	13,859	1,66,312
14	6,758	7,101	13,859	1,59,554
15	7,058	6,801	13,859	1,52,495
16	7,371	6,488	13,859	1,45,124
17	7,698	6,161	13,859	1,37,426
18	8,040	5,819	13,859	1,29,386
19	8,397	5,462	13,859	1,20,989
20	8,770	5,090	13,859	1,12,219
21	9,159	4,700	13,859	1,03,060
22	9,565	4,294	13,859	93,495
23	9,990	3,869	13,859	83,506
24	10,433	3,426	13,859	73,073
25	10,896	2,963	13,859	62,177
26	11,379	2,480	13,859	50,797
27	11,884	1,975	13,859	38,913
28	12,412	1,447	13,859	26,501
29	12,963	896	13,859	13,538
30	13,538	321	13,859	0

SITUATION- 2

LOAN $245000 - $13000 = $232000

YEARS = 15, INTEREST RATE = 3.65% P.A.

MONTHLY PAYMENTS = EMI = $1676

TOTAL INETERST PAYABLE = $69621

TOTAL PAYMENT = PRINCIPAL + INTEREST = $301621

AMORTIZATION TABLE
LOAN $245000, DOWN PAYMENT $13000, INT. RATE. 3.65% P.A., YEARS 15
YEAR	PRINCIPAL	INTEREST	PAYMENT	BALANCE
1	11,837	8,271	20,108	2,20,163
2	12,276	7,832	20,108	2,07,887
3	12,732	7,376	20,108	1,95,155
4	13,204	6,904	20,108	1,81,951
5	13,694	6,414	20,108	1,68,257
6	14,203	5,905	20,108	1,54,054
7	14,730	5,378	20,108	1,39,324
8	15,277	4,831	20,108	1,24,048
9	15,844	4,264	20,108	1,08,204
10	16,432	3,676	20,108	91,772
11	17,042	3,066	20,108	74,731
12	17,674	2,434	20,108	57,057
13	18,330	1,778	20,108	38,727
14	19,010	1,098	20,108	19,716
15	19,716	392	20,108	0

TOTAL SAVINGS IF INTEREST RATE DECREASES TO 3.65% P.A. AND LENGTH OF 15YEARS

= $415772-$301621 = $114151