Using excel spreadsheets only! Malfoy buys a home down the street from Hagrid for $342,000 and...

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?

Expert Solution

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	=PR(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	=3078004.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

ekkarill92 answered 2 years ago

Using excel! Hagrid buys a home for $230,000 and put $19000 down. He finances the rest at...

Using excel! Hagrid buys a home for $230,000 and put $19000 down. He finances the rest at 6% for 30 years. If he refinances after 8 years at 3.95% how much will he save assuming he pays the original amount the whole time? How many years total does it take to pay off the loan?

Using excel! Snape buys a home for $450000 and puts $50000 down. He finances the rest at...

Using excel! Snape buys a home for $450000 and puts $50000 down. He finances the rest at 5.4% for 30 years. He begins paying an extra $700 per month after 5 years How much will he end up saving? When will it be paid off assuming he purchased the home June 1, 1999?

Using Excel! Professor McGonagall buys a home for $129,000 and puts $15000 down. She finances the rest...

Using Excel! Professor McGonagall buys a home for $129,000 and puts $15000 down. She finances the rest for 30 years at 4.25% If she refinances after 5 years at 3.9%, how much will she save assuming she keeps paying the original amount?

Consider the following data from the gas station down the street from you for the last...

Consider the following data from the gas station down the street from you for the last week of November: Nov 2018 Price per gallon: $2.79 Gallons sold: 12,500 Nov 2019 Price per gallon: $2.24 Gallons sold: 14,250 In a two-page paper (minimum not including the title and reference pages), address the following: Explain how you would calculate the price of elasticity of demand for gasoline. Explain how consumer and producer surplus will change as a result of this price change....

An investor buys a condo for $160,000 by putting 10% down and financing the rest using...

An investor buys a condo for $160,000 by putting 10% down and financing the rest using the 15 year fixed rate in the WSJ. What are her monthly payments? Another investor decides to use an interest only loan to finance the same type of condo, the rate is 3.5% a year, what are the monthly payments? Three years later the condo is worth $170,000, what will be the return on the investment for each investor?

You are considering the purchase of a home priced at $175,000, using 15% down.

You are considering the purchase of a home priced at $175,000, using 15% down. The rest will be financed at 4.29% over 360 months. What is your monthly payment expected to be?A) $853B) $1,187C) $735.25D) $556.7914) For the loan in #13, what is the loan constant?A) 5.93%B) 4.29%C) 8.17%D) 6.77%15) If there were 2 points and $790 in fees to the lender to be paid to obtain the interest rate in problem #13, what would be the APR of the...

PLEASE SHOW IN EXCEL You purchase a $325,000 town home and you pay 25 percent down....

PLEASE SHOW IN EXCEL You purchase a $325,000 town home and you pay 25 percent down. You obtain a 30-year fixed-rate mortgage with an annual interest rate of 5.75 percent and monthly payments. After five years you refinance the mortgage for 25 years at a 5.1 percent annual interest rate. After you refinance, what is the new monthly payment (to the nearest dollar)?

1. Every day, Eric takes the same street from his home to the university. There are...

1. Every day, Eric takes the same street from his home to the university. There are 4 street lights along his way, and Eric has noticed the following Markov dependence. If he sees a green light at an intersection, then 60% of time the next light is also green, and 40% of time the next light is red. However, if he sees a red light, then 75% of time the next light is also red, and 25% of time the...

Suppose that you want to purchase a home for $220,000 using a down payment as described...

Suppose that you want to purchase a home for $220,000 using a down payment as described below and you finance the rest of the purchase by taking out a 15-year mortgage at 4.5% interest compounded monthly. The amount of the down payment is $56,083. Construct an amortization table for the entire length of the loan making sure to include columns for the payment number, the amount of the payment, the amount of each payment that goes towards the principal, the amount...

Solve using excel: A. Suppose a potential home buyer is interested in taking a $500,000 mortgage...

Solve using excel: A. Suppose a potential home buyer is interested in taking a $500,000 mortgage loan that has a term of 30 years and a fixed mortgage rate of 5.25%. What is the monthly mortgage payment that the homeowner would need to make if this loan is fully amortizing?   B. You have taken out a $350,000, 3/1 ARM. The initial rate of 6.0% (annual) is locked in for 3 years. Calculate the outstanding balance on the loan after 3...

Question