You just purchased a $400,000 house and gave a 20% down payment. For the remaining portion,...

You just purchased a $400,000 house and gave a 20% down payment. For the remaining portion, you obtained a 30-year mortgage at a 6% interest rate. (6 points) What are the monthly payments on this mortgage? If the house appreciates at a 3 percent annually, what will be the value of the house in ten years? In ten years, how much equity will you have on this home?

Answer all questions and show work in Excel.

SHOW ME IN EXCEL PLEASE

Expert Solution

Below are the formulas used to solve the question.

To find out equity on this home in 10 years we need to prepare loan amortisation schedule.

Period	Opening bal.	Interest	EMI	Principal repaid	Ending bal.
1	3,20,000.00	1,600.00	1,918.56	318.56	3,19,681.44
2	3,19,681.44	1,598.41	1,918.56	320.15	3,19,361.29
3	3,19,361.29	1,596.81	1,918.56	321.75	3,19,039.53
4	3,19,039.53	1,595.20	1,918.56	323.36	3,18,716.17
5	3,18,716.17	1,593.58	1,918.56	324.98	3,18,391.19
6	3,18,391.19	1,591.96	1,918.56	326.60	3,18,064.59
7	3,18,064.59	1,590.32	1,918.56	328.24	3,17,736.35
8	3,17,736.35	1,588.68	1,918.56	329.88	3,17,406.47
9	3,17,406.47	1,587.03	1,918.56	331.53	3,17,074.95
10	3,17,074.95	1,585.37	1,918.56	333.19	3,16,741.76
11	3,16,741.76	1,583.71	1,918.56	334.85	3,16,406.91
12	3,16,406.91	1,582.03	1,918.56	336.53	3,16,070.38
13	3,16,070.38	1,580.35	1,918.56	338.21	3,15,732.18
14	3,15,732.18	1,578.66	1,918.56	339.90	3,15,392.28
15	3,15,392.28	1,576.96	1,918.56	341.60	3,15,050.68
16	3,15,050.68	1,575.25	1,918.56	343.31	3,14,707.37
17	3,14,707.37	1,573.54	1,918.56	345.02	3,14,362.35
18	3,14,362.35	1,571.81	1,918.56	346.75	3,14,015.60
19	3,14,015.60	1,570.08	1,918.56	348.48	3,13,667.12
20	3,13,667.12	1,568.34	1,918.56	350.22	3,13,316.89
21	3,13,316.89	1,566.58	1,918.56	351.98	3,12,964.92
22	3,12,964.92	1,564.82	1,918.56	353.74	3,12,611.18
23	3,12,611.18	1,563.06	1,918.56	355.50	3,12,255.68
24	3,12,255.68	1,561.28	1,918.56	357.28	3,11,898.40
25	3,11,898.40	1,559.49	1,918.56	359.07	3,11,539.33
26	3,11,539.33	1,557.70	1,918.56	360.86	3,11,178.46
27	3,11,178.46	1,555.89	1,918.56	362.67	3,10,815.80
28	3,10,815.80	1,554.08	1,918.56	364.48	3,10,451.32
29	3,10,451.32	1,552.26	1,918.56	366.30	3,10,085.01
30	3,10,085.01	1,550.43	1,918.56	368.13	3,09,716.88
31	3,09,716.88	1,548.58	1,918.56	369.98	3,09,346.90
32	3,09,346.90	1,546.73	1,918.56	371.83	3,08,975.08
33	3,08,975.08	1,544.88	1,918.56	373.68	3,08,601.39
34	3,08,601.39	1,543.01	1,918.56	375.55	3,08,225.84
35	3,08,225.84	1,541.13	1,918.56	377.43	3,07,848.41
36	3,07,848.41	1,539.24	1,918.56	379.32	3,07,469.09
37	3,07,469.09	1,537.35	1,918.56	381.21	3,07,087.88
38	3,07,087.88	1,535.44	1,918.56	383.12	3,06,704.76
39	3,06,704.76	1,533.52	1,918.56	385.04	3,06,319.72
40	3,06,319.72	1,531.60	1,918.56	386.96	3,05,932.76
41	3,05,932.76	1,529.66	1,918.56	388.90	3,05,543.86
42	3,05,543.86	1,527.72	1,918.56	390.84	3,05,153.02
43	3,05,153.02	1,525.77	1,918.56	392.79	3,04,760.23
44	3,04,760.23	1,523.80	1,918.56	394.76	3,04,365.47
45	3,04,365.47	1,521.83	1,918.56	396.73	3,03,968.73
46	3,03,968.73	1,519.84	1,918.56	398.72	3,03,570.02
47	3,03,570.02	1,517.85	1,918.56	400.71	3,03,169.31
48	3,03,169.31	1,515.85	1,918.56	402.71	3,02,766.59
49	3,02,766.59	1,513.83	1,918.56	404.73	3,02,361.87
50	3,02,361.87	1,511.81	1,918.56	406.75	3,01,955.12
51	3,01,955.12	1,509.78	1,918.56	408.78	3,01,546.33
52	3,01,546.33	1,507.73	1,918.56	410.83	3,01,135.50
53	3,01,135.50	1,505.68	1,918.56	412.88	3,00,722.62
54	3,00,722.62	1,503.61	1,918.56	414.95	3,00,307.67
55	3,00,307.67	1,501.54	1,918.56	417.02	2,99,890.65
56	2,99,890.65	1,499.45	1,918.56	419.11	2,99,471.55
57	2,99,471.55	1,497.36	1,918.56	421.20	2,99,050.34
58	2,99,050.34	1,495.25	1,918.56	423.31	2,98,627.04
59	2,98,627.04	1,493.14	1,918.56	425.42	2,98,201.61
60	2,98,201.61	1,491.01	1,918.56	427.55	2,97,774.06
61	2,97,774.06	1,488.87	1,918.56	429.69	2,97,344.37
62	2,97,344.37	1,486.72	1,918.56	431.84	2,96,912.53
63	2,96,912.53	1,484.56	1,918.56	434.00	2,96,478.53
64	2,96,478.53	1,482.39	1,918.56	436.17	2,96,042.37
65	2,96,042.37	1,480.21	1,918.56	438.35	2,95,604.02
66	2,95,604.02	1,478.02	1,918.56	440.54	2,95,163.48
67	2,95,163.48	1,475.82	1,918.56	442.74	2,94,720.74
68	2,94,720.74	1,473.60	1,918.56	444.96	2,94,275.78
69	2,94,275.78	1,471.38	1,918.56	447.18	2,93,828.60
70	2,93,828.60	1,469.14	1,918.56	449.42	2,93,379.18
71	2,93,379.18	1,466.90	1,918.56	451.66	2,92,927.52
72	2,92,927.52	1,464.64	1,918.56	453.92	2,92,473.59
73	2,92,473.59	1,462.37	1,918.56	456.19	2,92,017.40
74	2,92,017.40	1,460.09	1,918.56	458.47	2,91,558.93
75	2,91,558.93	1,457.79	1,918.56	460.77	2,91,098.16
76	2,91,098.16	1,455.49	1,918.56	463.07	2,90,635.10
77	2,90,635.10	1,453.18	1,918.56	465.38	2,90,169.71
78	2,90,169.71	1,450.85	1,918.56	467.71	2,89,702.00
79	2,89,702.00	1,448.51	1,918.56	470.05	2,89,231.95
80	2,89,231.95	1,446.16	1,918.56	472.40	2,88,759.55
81	2,88,759.55	1,443.80	1,918.56	474.76	2,88,284.79
82	2,88,284.79	1,441.42	1,918.56	477.14	2,87,807.65
83	2,87,807.65	1,439.04	1,918.56	479.52	2,87,328.13
84	2,87,328.13	1,436.64	1,918.56	481.92	2,86,846.21
85	2,86,846.21	1,434.23	1,918.56	484.33	2,86,361.88
86	2,86,361.88	1,431.81	1,918.56	486.75	2,85,875.13
87	2,85,875.13	1,429.38	1,918.56	489.18	2,85,385.95
88	2,85,385.95	1,426.93	1,918.56	491.63	2,84,894.32
89	2,84,894.32	1,424.47	1,918.56	494.09	2,84,400.23
90	2,84,400.23	1,422.00	1,918.56	496.56	2,83,903.67
91	2,83,903.67	1,419.52	1,918.56	499.04	2,83,404.63
92	2,83,404.63	1,417.02	1,918.56	501.54	2,82,903.09
93	2,82,903.09	1,414.52	1,918.56	504.04	2,82,399.05
94	2,82,399.05	1,412.00	1,918.56	506.56	2,81,892.48
95	2,81,892.48	1,409.46	1,918.56	509.10	2,81,383.38
96	2,81,383.38	1,406.92	1,918.56	511.64	2,80,871.74
97	2,80,871.74	1,404.36	1,918.56	514.20	2,80,357.54
98	2,80,357.54	1,401.79	1,918.56	516.77	2,79,840.77
99	2,79,840.77	1,399.20	1,918.56	519.36	2,79,321.41
100	2,79,321.41	1,396.61	1,918.56	521.95	2,78,799.46
101	2,78,799.46	1,394.00	1,918.56	524.56	2,78,274.89
102	2,78,274.89	1,391.37	1,918.56	527.19	2,77,747.71
103	2,77,747.71	1,388.74	1,918.56	529.82	2,77,217.89
104	2,77,217.89	1,386.09	1,918.56	532.47	2,76,685.42
105	2,76,685.42	1,383.43	1,918.56	535.13	2,76,150.28
106	2,76,150.28	1,380.75	1,918.56	537.81	2,75,612.48
107	2,75,612.48	1,378.06	1,918.56	540.50	2,75,071.98
108	2,75,071.98	1,375.36	1,918.56	543.20	2,74,528.78
109	2,74,528.78	1,372.64	1,918.56	545.92	2,73,982.86
110	2,73,982.86	1,369.91	1,918.56	548.65	2,73,434.22
111	2,73,434.22	1,367.17	1,918.56	551.39	2,72,882.83
112	2,72,882.83	1,364.41	1,918.56	554.15	2,72,328.68
113	2,72,328.68	1,361.64	1,918.56	556.92	2,71,771.76
114	2,71,771.76	1,358.86	1,918.56	559.70	2,71,212.06
115	2,71,212.06	1,356.06	1,918.56	562.50	2,70,649.56
116	2,70,649.56	1,353.25	1,918.56	565.31	2,70,084.25
117	2,70,084.25	1,350.42	1,918.56	568.14	2,69,516.11
118	2,69,516.11	1,347.58	1,918.56	570.98	2,68,945.13
119	2,68,945.13	1,344.73	1,918.56	573.83	2,68,371.30
120	2,68,371.30	1,341.86	1,918.56	576.70	2,67,794.60

Equity on home = 537566.55 - 267794.6 = $269,771.96

Note:

1. Interest = Opening bal*6%*1/12
2. Principal repaid = EMI - Interest
3. Ending bal. = Opening bal - principal repaid

jeff jeffy answered 1 year ago

some wants to borrow 400,000 dollars for a house with 20% down payment at 5% compounding...

some wants to borrow 400,000 dollars for a house with 20% down payment at 5% compounding monthly.calculate his monthly payment and the total interest for each of the following periods : a- 30 years, b- 15 years. c- compare the savings in interest if mortgage is for 15years?

1. (10 pts) You just bought a house for $400,000. You put 20% down and financed...

1. (10 pts) You just bought a house for $400,000. You put 20% down and financed the rest over 30 years at 6% nominal interest. Assuming equal monthly payments over the term of the loan, what are the monthly payments? What is the effective rate? (Chapter 4) 2. (10 pts) What would you need to invest today in an account that had a nominal rate of 8% compounded quarterly, if you wanted $12,000 in 4 years? What would be the...

You just bought a house for $300,000. You put $100,000 as a down payment and borrow the remaining $200,000 from a bank.

You just bought a house for $300,000. You put $100,000 as a down payment and borrow the remaining $200,000 from a bank. You take out a 30-year mortgage that charges you 6 percent APR but with monthly compounding. A. (15 points) How much is your monthly mortgage payment? Show your work. B. (5 points) How much of your second mortgage payment goes towards interest? Show your work.

You bought a $225,000 house with a $40,000 down payment and financed the remaining $185,000 with...

You bought a $225,000 house with a $40,000 down payment and financed the remaining $185,000 with a 30-year mortgage (home loan). The interest rate on the loan is 5%. a) Find the monthly payment. b) After 12 years of paying on this mortgage, you decide to explore refinancing. With 18 years left to go, what is your remaining loan balance? c) If you do refinance the amount from part (b) with a new 15-year loan at 3% interest, how much...

The adam smith family just purchased a 350,000 house with an 110,000 down payment and a...

The adam smith family just purchased a 350,000 house with an 110,000 down payment and a 30 year mortgage loan at 6.50% annually, with a monthly compounding. payments are made monthly. What is the remaining balance on the mortage after 15 years?

2) You want to buy a house for $400,000 with 10% down payment. You take two...

2) You want to buy a house for $400,000 with 10% down payment. You take two mortgages to over the $360,000 needed to buy the house. They are: • Loan 1: Loan amount $160,000, fully amortizing 30-year fixed rate mortgage for 4.2%. This loan has 2 points and 1% pre-payment penalty. • Loan 2: Loan amount $200,000, fully amortizing 15-year, 5-year ARM with reset every five years. Initial interest rate is 3.6%. Margin set at 2% over prime rate. Expected...

You want to buy a house for $400,000 with 10% down payment. You take two mortgages to...

You want to buy a house for $400,000 with 10% down payment. You take two mortgages to over the $360,000 needed to buy the house. They are: Loan 1: Loan amount $160,000, fully amortizing 30-year fixed rate mortgage for 4.2%. This loan has 2 points and 1% pre-payment penalty. Loan 2: Loan amount $200,000, fully amortizing 15-year, 5-year ARM with reset every five years. Initial interest rate is 3.6%. Margin set at 2% over prime rate. Expected prime rates: 4% at end of...

Fifteen years ago a couple purchased a house for $230,000.00 by paying a 20% down payment...

Fifteen years ago a couple purchased a house for $230,000.00 by paying a 20% down payment and financing the remaining balance with a 30-year mortgage at 4.7% compounded monthly. (a) Find the monthly payment for this loan. (b) Find the balance of the loan after 16 years and after 17 years? (c) Find the total amount of interest paid by the couple during the 17th year.

1. You just bought a house for $300,000. You put $100,000 as a down payment and...

1. You just bought a house for $300,000. You put $100,000 as a down payment and borrow the remaining $200,000 from a bank. You take out a 30-year mortgage that charges you 6 percent APR but with monthly compounding. a. How much is your monthly mortgage payment? Show your work. b. How much of your second mortgage payment goes towards interest? Show your work.

You are planning to buy a house in New Jersey. You put a 20% down payment,...

You are planning to buy a house in New Jersey. You put a 20% down payment, and 15-year mortgage rates are at 4.2% -Price of the house is $400,000. a. Calculate the monthly payments. b- Calculate the 1st month interest payment. c-Calculate the 1st month principal payments

Question