Question

Using excel spreadsheets only!

Malfoy buys a home down the street from Hagrid for $342,000 and puts 10% down.  He finances the rest at 4.3% for 30 years.  

He decides to refi after 11 years at 3.8%.  If he pays the original payment, how much does he save?

Answer 1

Calculations
Price	342,000
Downpayment	34,200
Balance to be financed	307,800
Rate of interest	4.30%
Refinanced rate	3.80%
Formula for Calculating EMI	=P*R*(1+R)^n '(1+R)^n-1
Where
P= Finance amount	307,800
R= Rate of interest	4%
N= no of instalment	30 Years
Orinal annual annual EMI	=307800*4.3%*(1+0.043)^30 (1+0.043)^30
	=13235(5.74349117263) '5.74349117263-1
Annual EMI	18,454.20
Refinanced EMI	=236313*3.8%(1+0.038)^19 (1+0.038)^19
Refinanced Annual EMI	17688.94
Total interest payable at 4.3% for 30 year (A)	245,821.36
Total interest payble for 19 year @ 3.8%	99,757.07
Total interest paid @4.3% for 11 year	131,509.22
Total interest expenses (B)	231,266.30
Savings in interest (A-B)	14,555.06
Original Principal repayment schedule
Years	opening Principal Outstanding	EMI	Principal amount	Interest	Closing principal outstanding
1	307,800	18,454.20	5,218.80	13,235.40	302,581.20
2	302,581.20	18,454.20	5,443.21	13,010.99	297,137.99
3	297,137.99	18,454.20	5,677.27	12,776.93	291,460.72
4	291,460.72	18,454.20	5,921.39	12,532.81	285,539.33
5	285,539.33	18,454.20	6,176.01	12,278.19	279,363.33
6	279,363.33	18,454.20	6,441.58	12,012.62	272,921.75
7	272,921.75	18,454.20	6,718.57	11,735.64	266,203.18
8	266,203.18	18,454.20	7,007.46	11,446.74	259,195.72
9	259,195.72	18,454.20	7,308.78	11,145.42	251,886.93
10	251,886.93	18,454.20	7,623.06	10,831.14	244,263.87
11	244,263.87	18,454.20	7,950.85	10,503.35	236,313.02
12	236,313.02	18,454.20	8,292.74	10,161.46	228,020.28
13	228,020.28	18,454.20	8,649.33	9,804.87	219,370.95
14	219,370.95	18,454.20	9,021.25	9,432.95	210,349.70
15	210,349.70	18,454.20	9,409.16	9,045.04	200,940.53
16	200,940.53	18,454.20	9,813.76	8,640.44	191,126.78
17	191,126.78	18,454.20	10,235.75	8,218.45	180,891.03
18	180,891.03	18,454.20	10,675.89	7,778.31	170,215.14
19	170,215.14	18,454.20	11,134.95	7,319.25	159,080.19
20	159,080.19	18,454.20	11,613.75	6,840.45	147,466.44
21	147,466.44	18,454.20	12,113.14	6,341.06	135,353.30
22	135,353.30	18,454.20	12,634.01	5,820.19	122,719.29
23	122,719.29	18,454.20	13,177.27	5,276.93	109,542.02
24	109,542.02	18,454.20	13,743.89	4,710.31	95,798.12
25	95,798.12	18,454.20	14,334.88	4,119.32	81,463.24
26	81,463.24	18,454.20	14,951.28	3,502.92	66,511.96
27	66,511.96	18,454.20	15,594.19	2,860.01	50,917.77
28	50,917.77	18,454.20	16,264.74	2,189.46	34,653.04
29	34,653.04	18,454.20	16,964.12	1,490.08	17,688.92
30	17,688.92	18,454.20	17,693.58	760.62	(4.66)
			Total interest	245,821.36
refinanced Principal repayment schedule
Years	opening Principal Outstanding	EMI	Principal amount	Interest	Closing principal outstanding
12	236,313.02	17688.94	8,709.04	8,979.89	227,603.97
13	227,603.97	17688.94	9,039.99	8,648.95	218,563.99
14	218,563.99	17688.94	9,383.51	8,305.43	209,180.48
15	209,180.48	17688.94	9,740.08	7,948.86	199,440.40
16	199,440.40	17688.94	10,110.20	7,578.74	189,330.20
17	189,330.20	17688.94	10,494.39	7,194.55	178,835.81
18	178,835.81	17688.94	10,893.18	6,795.76	167,942.63
19	167,942.63	17688.94	11,307.12	6,381.82	156,635.51
20	156,635.51	17688.94	11,736.79	5,952.15	144,898.72
21	144,898.72	17688.94	12,182.79	5,506.15	132,715.94
22	132,715.94	17688.94	12,645.73	5,043.21	120,070.20
23	120,070.20	17688.94	13,126.27	4,562.67	106,943.93
24	106,943.93	17688.94	13,625.07	4,063.87	93,318.86
25	93,318.86	17688.94	14,142.82	3,546.12	79,176.04
26	79,176.04	17688.94	14,680.25	3,008.69	64,495.80
27	64,495.80	17688.94	15,238.10	2,450.84	49,257.70
28	49,257.70	17688.94	15,817.15	1,871.79	33,440.55
29	33,440.55	17688.94	16,418.20	1,270.74	17,022.35
30	17,022.35	17688.94	17,042.09	646.85	(19.73)
		Total interest		99,757.07
		Interest already paid @4.3%		131,509.22
				231,266.30