Question

In: Finance

Mortgage Loan Analysis: A resident in Victoria is planning to buy a new house in March...

Mortgage Loan Analysis: A resident in Victoria is planning to buy a new house in March 2018. The sale price of the house is $336,000. He plans to pay 20% down payments and borrow additional 80% from Wells Fargo with a 30-year, 4.375% fixed-rate mortgage loan. He is expected to pay an equal MONTHLY payment starting from April 2018 for a total of 30 years.

(1) Calculate the required monthly mortgage payment for Mr. Davidson.

(2) Construct the 2018~2047 amortization table for Mr. Davidson.

(1)   Mr. Davidson should prepare his 2018 tax filings in early 2019. Please compute the total mortgage interest payments which he can use for his 2018 tax deductions.

Need step by step help on how to solve this on excel!

Solutions

Expert Solution

Sale price of house 336000
Downpayment 67,200.00 20%* sale price
Loan value 268,800.00 80%*sale price
Interest rate 4.375% Annual
Int rate monthly 0.365% Annual rate/12
No of periods 360.00 30 years*12
1 Monthly payment $1,342.08 using pmt formula with monthly payment rate and no of periods as 360
2 No of period Initial principle Amount paid Interest Principle paid Remaining principle Formulas used
1 268,800.00 $1,342.08 980.00 $362.08 268,437.92 Initial principle(t)= remaining principle(t-1)
2 268,437.92 $1,342.08 978.68 $363.40 268,074.52 Interest = Initial principle(t)*monthly rate
3 268,074.52 $1,342.08 977.36 $364.72 267,709.80 principle paid = Amount paid - interest
4 267,709.80 $1,342.08 976.03 $366.05 267,343.75 Remaining principle(t) = Initial principle (t)-principle paid(t)
5 267,343.75 $1,342.08 974.69 $367.39 266,976.36
6 266,976.36 $1,342.08 973.35 $368.73 266,607.63
7 266,607.63 $1,342.08 972.01 $370.07 266,237.56
8 266,237.56 $1,342.08 970.66 $371.42 265,866.14
9 265,866.14 $1,342.08 969.30 $372.78 265,493.36
10 265,493.36 $1,342.08 967.94 $374.13 265,119.23
11 265,119.23 $1,342.08 966.58 $375.50 264,743.73
12 264,743.73 $1,342.08 965.21 $376.87 264,366.86
13 264,366.86 $1,342.08 963.84 $378.24 263,988.62
14 263,988.62 $1,342.08 962.46 $379.62 263,609.00
15 263,609.00 $1,342.08 961.07 $381.00 263,228.00
16 263,228.00 $1,342.08 959.69 $382.39 262,845.60
17 262,845.60 $1,342.08 958.29 $383.79 262,461.82
18 262,461.82 $1,342.08 956.89 $385.19 262,076.63
19 262,076.63 $1,342.08 955.49 $386.59 261,690.04
20 261,690.04 $1,342.08 954.08 $388.00 261,302.04
21 261,302.04 $1,342.08 952.66 $389.42 260,912.62
22 260,912.62 $1,342.08 951.24 $390.83 260,521.79
23 260,521.79 $1,342.08 949.82 $392.26 260,129.53
24 260,129.53 $1,342.08 948.39 $393.69 259,735.84
25 259,735.84 $1,342.08 946.95 $395.13 259,340.71
26 259,340.71 $1,342.08 945.51 $396.57 258,944.15
27 258,944.15 $1,342.08 944.07 $398.01 258,546.13
28 258,546.13 $1,342.08 942.62 $399.46 258,146.67
29 258,146.67 $1,342.08 941.16 $400.92 257,745.75
30 257,745.75 $1,342.08 939.70 $402.38 257,343.37
31 257,343.37 $1,342.08 938.23 $403.85 256,939.52
32 256,939.52 $1,342.08 936.76 $405.32 256,534.20
33 256,534.20 $1,342.08 935.28 $406.80 256,127.41
34 256,127.41 $1,342.08 933.80 $408.28 255,719.13
35 255,719.13 $1,342.08 932.31 $409.77 255,309.36
36 255,309.36 $1,342.08 930.82 $411.26 254,898.09
37 254,898.09 $1,342.08 929.32 $412.76 254,485.33
38 254,485.33 $1,342.08 927.81 $414.27 254,071.06
39 254,071.06 $1,342.08 926.30 $415.78 253,655.28
40 253,655.28 $1,342.08 924.78 $417.29 253,237.99
41 253,237.99 $1,342.08 923.26 $418.82 252,819.18
42 252,819.18 $1,342.08 921.74 $420.34 252,398.83
43 252,398.83 $1,342.08 920.20 $421.87 251,976.96
44 251,976.96 $1,342.08 918.67 $423.41 251,553.55
45 251,553.55 $1,342.08 917.12 $424.96 251,128.59
46 251,128.59 $1,342.08 915.57 $426.51 250,702.08
47 250,702.08 $1,342.08 914.02 $428.06 250,274.02
48 250,274.02 $1,342.08 912.46 $429.62 249,844.40
49 249,844.40 $1,342.08 910.89 $431.19 249,413.21
50 249,413.21 $1,342.08 909.32 $432.76 248,980.45
51 248,980.45 $1,342.08 907.74 $434.34 248,546.12
52 248,546.12 $1,342.08 906.16 $435.92 248,110.20
53 248,110.20 $1,342.08 904.57 $437.51 247,672.68
54 247,672.68 $1,342.08 902.97 $439.11 247,233.58
55 247,233.58 $1,342.08 901.37 $440.71 246,792.87
56 246,792.87 $1,342.08 899.77 $442.31 246,350.56
57 246,350.56 $1,342.08 898.15 $443.93 245,906.63
58 245,906.63 $1,342.08 896.53 $445.54 245,461.09
59 245,461.09 $1,342.08 894.91 $447.17 245,013.92
60 245,013.92 $1,342.08 893.28 $448.80 244,565.12
61 244,565.12 $1,342.08 891.64 $450.44 244,114.69
62 244,114.69 $1,342.08 890.00 $452.08 243,662.61
63 243,662.61 $1,342.08 888.35 $453.73 243,208.88
64 243,208.88 $1,342.08 886.70 $455.38 242,753.51
65 242,753.51 $1,342.08 885.04 $457.04 242,296.47
66 242,296.47 $1,342.08 883.37 $458.71 241,837.76
67 241,837.76 $1,342.08 881.70 $460.38 241,377.38
68 241,377.38 $1,342.08 880.02 $462.06 240,915.32
69 240,915.32 $1,342.08 878.34 $463.74 240,451.58
70 240,451.58 $1,342.08 876.65 $465.43 239,986.15
71 239,986.15 $1,342.08 874.95 $467.13 239,519.02
72 239,519.02 $1,342.08 873.25 $468.83 239,050.19
73 239,050.19 $1,342.08 871.54 $470.54 238,579.65
74 238,579.65 $1,342.08 869.82 $472.26 238,107.39
75 238,107.39 $1,342.08 868.10 $473.98 237,633.41
76 237,633.41 $1,342.08 866.37 $475.71 237,157.70
77 237,157.70 $1,342.08 864.64 $477.44 236,680.26
78 236,680.26 $1,342.08 862.90 $479.18 236,201.08
79 236,201.08 $1,342.08 861.15 $480.93 235,720.15
80 235,720.15 $1,342.08 859.40 $482.68 235,237.47
81 235,237.47 $1,342.08 857.64 $484.44 234,753.03
82 234,753.03 $1,342.08 855.87 $486.21 234,266.82
83 234,266.82 $1,342.08 854.10 $487.98 233,778.84
84 233,778.84 $1,342.08 852.32 $489.76 233,289.08
85 233,289.08 $1,342.08 850.53 $491.55 232,797.53
86 232,797.53 $1,342.08 848.74 $493.34 232,304.19
87 232,304.19 $1,342.08 846.94 $495.14 231,809.06
88 231,809.06 $1,342.08 845.14 $496.94 231,312.12
89 231,312.12 $1,342.08 843.33 $498.75 230,813.36
90 230,813.36 $1,342.08 841.51 $500.57 230,312.79
91 230,312.79 $1,342.08 839.68 $502.40 229,810.39
92 229,810.39 $1,342.08 837.85 $504.23 229,306.16
93 229,306.16 $1,342.08 836.01 $506.07 228,800.10
94 228,800.10 $1,342.08 834.17 $507.91 228,292.19
95 228,292.19 $1,342.08 832.32 $509.76 227,782.42
96 227,782.42 $1,342.08 830.46 $511.62 227,270.80
97 227,270.80 $1,342.08 828.59 $513.49 226,757.31
98 226,757.31 $1,342.08 826.72 $515.36 226,241.95
99 226,241.95 $1,342.08 824.84 $517.24 225,724.72
100 225,724.72 $1,342.08 822.95 $519.12 225,205.59
101 225,205.59 $1,342.08 821.06 $521.02 224,684.58
102 224,684.58 $1,342.08 819.16 $522.92 224,161.66
103 224,161.66 $1,342.08 817.26 $524.82 223,636.84
104 223,636.84 $1,342.08 815.34 $526.74 223,110.10
105 223,110.10 $1,342.08 813.42 $528.66 222,581.44
106 222,581.44 $1,342.08 811.49 $530.58 222,050.86
107 222,050.86 $1,342.08 809.56 $532.52 221,518.34
108 221,518.34 $1,342.08 807.62 $534.46 220,983.88
109 220,983.88 $1,342.08 805.67 $536.41 220,447.47
110 220,447.47 $1,342.08 803.71 $538.36 219,909.11
111 219,909.11 $1,342.08 801.75 $540.33 219,368.78
112 219,368.78 $1,342.08 799.78 $542.30 218,826.49
113 218,826.49 $1,342.08 797.80 $544.27 218,282.21
114 218,282.21 $1,342.08 795.82 $546.26 217,735.95
115 217,735.95 $1,342.08 793.83 $548.25 217,187.70
116 217,187.70 $1,342.08 791.83 $550.25 216,637.45
117 216,637.45 $1,342.08 789.82 $552.25 216,085.20
118 216,085.20 $1,342.08 787.81 $554.27 215,530.93
119 215,530.93 $1,342.08 785.79 $556.29 214,974.64
120 214,974.64 $1,342.08 783.76 $558.32 214,416.33
121 214,416.33 $1,342.08 781.73 $560.35 213,855.97
122 213,855.97 $1,342.08 779.68 $562.40 213,293.58
123 213,293.58 $1,342.08 777.63 $564.45 212,729.13
124 212,729.13 $1,342.08 775.57 $566.50 212,162.63
125 212,162.63 $1,342.08 773.51 $568.57 211,594.06
126 211,594.06 $1,342.08 771.44 $570.64 211,023.42
127 211,023.42 $1,342.08 769.36 $572.72 210,450.69
128 210,450.69 $1,342.08 767.27 $574.81 209,875.88
129 209,875.88 $1,342.08 765.17 $576.91 209,298.98
130 209,298.98 $1,342.08 763.07 $579.01 208,719.97
131 208,719.97 $1,342.08 760.96 $581.12 208,138.85
132 208,138.85 $1,342.08 758.84 $583.24 207,555.61
133 207,555.61 $1,342.08 756.71 $585.37 206,970.24
134 206,970.24 $1,342.08 754.58 $587.50 206,382.74
135 206,382.74 $1,342.08 752.44 $589.64 205,793.10
136 205,793.10 $1,342.08 750.29 $591.79 205,201.31
137 205,201.31 $1,342.08 748.13 $593.95 204,607.36
138 204,607.36 $1,342.08 745.96 $596.11 204,011.25
139 204,011.25 $1,342.08 743.79 $598.29 203,412.96
140 203,412.96 $1,342.08 741.61 $600.47 202,812.49
141 202,812.49 $1,342.08 739.42 $602.66 202,209.83
142 202,209.83 $1,342.08 737.22 $604.86 201,604.98
143 201,604.98 $1,342.08 735.02 $607.06 200,997.92
144 200,997.92 $1,342.08 732.80 $609.27 200,388.64
145 200,388.64 $1,342.08 730.58 $611.50 199,777.15
146 199,777.15 $1,342.08 728.35 $613.72 199,163.42
147 199,163.42 $1,342.08 726.12 $615.96 198,547.46
148 198,547.46 $1,342.08 723.87 $618.21 197,929.25
149 197,929.25 $1,342.08 721.62 $620.46 197,308.79
150 197,308.79 $1,342.08 719.35 $622.72 196,686.07
151 196,686.07 $1,342.08 717.08 $624.99 196,061.07
152 196,061.07 $1,342.08 714.81 $627.27 195,433.80
153 195,433.80 $1,342.08 712.52 $629.56 194,804.24
154 194,804.24 $1,342.08 710.22 $631.85 194,172.38
155 194,172.38 $1,342.08 707.92 $634.16 193,538.23
156 193,538.23 $1,342.08 705.61 $636.47 192,901.76
157 192,901.76 $1,342.08 703.29 $638.79 192,262.96
158 192,262.96 $1,342.08 700.96 $641.12 191,621.84
159 191,621.84 $1,342.08 698.62 $643.46 190,978.39
160 190,978.39 $1,342.08 696.28 $645.80 190,332.58
161 190,332.58 $1,342.08 693.92 $648.16 189,684.43
162 189,684.43 $1,342.08 691.56 $650.52 189,033.90
163 189,033.90 $1,342.08 689.19 $652.89 188,381.01
164 188,381.01 $1,342.08 686.81 $655.27 187,725.74
165 187,725.74 $1,342.08 684.42 $657.66 187,068.08
166 187,068.08 $1,342.08 682.02 $660.06 186,408.02
167 186,408.02 $1,342.08 679.61 $662.47 185,745.55
168 185,745.55 $1,342.08 677.20 $664.88 185,080.67
169 185,080.67 $1,342.08 674.77 $667.31 184,413.36
170 184,413.36 $1,342.08 672.34 $669.74 183,743.63
171 183,743.63 $1,342.08 669.90 $672.18 183,071.45
172 183,071.45 $1,342.08 667.45 $674.63 182,396.81
173 182,396.81 $1,342.08 664.99 $677.09 181,719.72
174 181,719.72 $1,342.08 662.52 $679.56 181,040.16
175 181,040.16 $1,342.08 660.04 $682.04 180,358.13
176 180,358.13 $1,342.08 657.56 $684.52 179,673.61
177 179,673.61 $1,342.08 655.06 $687.02 178,986.59
178 178,986.59 $1,342.08 652.56 $689.52 178,297.06
179 178,297.06 $1,342.08 650.04 $692.04 177,605.03
180 177,605.03 $1,342.08 647.52 $694.56 176,910.47
181 176,910.47 $1,342.08 644.99 $697.09 176,213.37
182 176,213.37 $1,342.08 642.44 $699.63 175,513.74
183 175,513.74 $1,342.08 639.89 $702.18 174,811.55
184 174,811.55 $1,342.08 637.33 $704.74 174,106.81
185 174,106.81 $1,342.08 634.76 $707.31 173,399.49
186 173,399.49 $1,342.08 632.19 $709.89 172,689.60
187 172,689.60 $1,342.08 629.60 $712.48 171,977.12
188 171,977.12 $1,342.08 627.00 $715.08 171,262.04
189 171,262.04 $1,342.08 624.39 $717.69 170,544.35
190 170,544.35 $1,342.08 621.78 $720.30 169,824.05
191 169,824.05 $1,342.08 619.15 $722.93 169,101.12
192 169,101.12 $1,342.08 616.51 $725.56 168,375.56
193 168,375.56 $1,342.08 613.87 $728.21 167,647.35
194 167,647.35 $1,342.08 611.21 $730.86 166,916.49
195 166,916.49 $1,342.08 608.55 $733.53 166,182.96
196 166,182.96 $1,342.08 605.88 $736.20 165,446.75
197 165,446.75 $1,342.08 603.19 $738.89 164,707.87
198 164,707.87 $1,342.08 600.50 $741.58 163,966.28
199 163,966.28 $1,342.08 597.79 $744.29 163,222.00
200 163,222.00 $1,342.08 595.08 $747.00 162,475.00
201 162,475.00 $1,342.08 592.36 $749.72 161,725.28
202 161,725.28 $1,342.08 589.62 $752.46 160,972.82
203 160,972.82 $1,342.08 586.88 $755.20 160,217.62
204 160,217.62 $1,342.08 584.13 $757.95 159,459.67
205 159,459.67 $1,342.08 581.36 $760.72 158,698.96
206 158,698.96 $1,342.08 578.59 $763.49 157,935.47
207 157,935.47 $1,342.08 575.81 $766.27 157,169.20
208 157,169.20 $1,342.08 573.01 $769.07 156,400.13
209 156,400.13 $1,342.08 570.21 $771.87 155,628.26
210 155,628.26 $1,342.08 567.39 $774.68 154,853.58
211 154,853.58 $1,342.08 564.57 $777.51 154,076.07
212 154,076.07 $1,342.08 561.74 $780.34 153,295.72
213 153,295.72 $1,342.08 558.89 $783.19 152,512.54
214 152,512.54 $1,342.08 556.04 $786.04 151,726.49
215 151,726.49 $1,342.08 553.17 $788.91 150,937.58
216 150,937.58 $1,342.08 550.29 $791.79 150,145.80
217 150,145.80 $1,342.08 547.41 $794.67 149,351.13
218 149,351.13 $1,342.08 544.51 $797.57 148,553.56
219 148,553.56 $1,342.08 541.60 $800.48 147,753.08
220 147,753.08 $1,342.08 538.68 $803.40 146,949.68
221 146,949.68 $1,342.08 535.75 $806.32 146,143.36
222 146,143.36 $1,342.08 532.81 $809.26 145,334.09
223 145,334.09 $1,342.08 529.86 $812.21 144,521.88
224 144,521.88 $1,342.08 526.90 $815.18 143,706.70
225 143,706.70 $1,342.08 523.93 $818.15 142,888.56
226 142,888.56 $1,342.08 520.95 $821.13 142,067.42
227 142,067.42 $1,342.08 517.95 $824.12 141,243.30
359 2,669.55 $1,342.08 9.73 $1,332.35 1,337.20
360 1,337.20 $1,342.08 4.88 $1,337.20 0.00

Related Solutions

Mortgage Loan Analysis: A resident in Victoria is planning to buy a new house in March...
Mortgage Loan Analysis: A resident in Victoria is planning to buy a new house in March 2018. The sale price of the house is $336,000. He plans to pay 20% down payments and borrow additional 80% from Wells Fargo with a 30-year, 4.375% fixed-rate mortgage loan. He is expected to pay an equal MONTHLY payment starting from April 2018 for a total of 30 years. (1) Calculate the required monthly mortgage payment for Mr. Davidson. (2) Construct the 2018~2047 amortization...
: Mortgage Loan Analysis: A resident in Victoria is planning to buy a new house in...
: Mortgage Loan Analysis: A resident in Victoria is planning to buy a new house in March 2018. The sale price of the house is $336,000. He plans to pay 20% down payments and borrow additional 80% from Wells Fargo with a 30-year, 4.375% fixed-rate mortgage loan. He is expected to pay an equal MONTHLY payment starting from April 2018 for a total of 30 years. (1) Calculate the required monthly mortgage payment for Mr. Davidson. (2) Construct the 2018~2047...
Mortgage Loan Analysis: Mr. Davidson plans to buy a new house at Sugar Land in June...
Mortgage Loan Analysis: Mr. Davidson plans to buy a new house at Sugar Land in June 2019. The sale price of the house is $500,000. He plans to pay 20% down payments and borrow additional 80% from Bank of America with a 30-year, 3.875% fixed-rate mortgage loan. He is expected to pay an equal MONTHLY payment starting from July 2019 for a total of 30 years. (1) Calculate the required monthly payment for Mr. Davidson. (2) Construct 2019~2021 amortization table...
You decide to buy a house for a total of $214452. To get a mortgage loan,...
You decide to buy a house for a total of $214452. To get a mortgage loan, you make a 10% down payment, and the bank will lend you the rest. The interest rate quoted for this loan is 5% APR, and the loan will be paid (and interest compounded) every month, for the next 30 years. How much is the TOTAL monthly payment for this mortgage?
You are planning to buy a house appraised for $350,000 and finance it through a mortgage...
You are planning to buy a house appraised for $350,000 and finance it through a mortgage of $300,000. What is the loan-to-value ratio and is it be above or below the normal cutoff set by your lender of 80 percent for a prime mortgage? Being securely employed, your take-home pay is $2,300 per month and you have no substantial other debts. Your lender has offered you a 2.5 percent, 30 year, fixed-rate mortgage. What is the amount that you will...
On March 1, you borrow $239,000 to buy a house. The mortgage rate is 7.75%. The...
On March 1, you borrow $239,000 to buy a house. The mortgage rate is 7.75%. The loan is to be repaid in equal monthly payments over 20 years. The first payment is due on April 1. How much of the third payment applies to the principal balance? (Assume that each month is equal to 1/12 of a summer).
1.You decide to buy a house for a total of $198842. To get a mortgage loan,...
1.You decide to buy a house for a total of $198842. To get a mortgage loan, you make a 10% down payment, and the bank will lend you the rest. The interest rate quoted for this loan is 6% APR, and the loan will be paid (and interest compounded) every month, for the next 30 years. How much is the TOTAL monthly payment for this mortgage? 2.A company has $96 million in outstanding bonds, and 10 million shares of stock...
You have borrowed $56000 as a mortgage loan to buy a house. The bank will charge...
You have borrowed $56000 as a mortgage loan to buy a house. The bank will charge interest at the rate of 9% annually and requires a minimum monthly payment of $500. At the end of five years, you must pay off the entire mortgage by a “balloon payment”. You plan to pay only the minimum amount each month and then pay off the loan with the final payment. Find this balloon payment. (Answer: $49966.07) please answer in excel format
You borrow $149000 to buy a house. The mortgage rate is 7.5% and the loan period...
You borrow $149000 to buy a house. The mortgage rate is 7.5% and the loan period is 30 years. Payments are made monthly. What is the monthly mortgage payment.
The bank has agreed to give you a mortgage for $400,000 to buy a new house....
The bank has agreed to give you a mortgage for $400,000 to buy a new house. Current mortgage rates have an APR of 4% compounded semi-annually for a term of 25 years. How much interest will you pay in total over the life of the loan? Select one: a. $121,433.25 b. $143,645.25 c. $231,224.25 d. $237,378.25 e. None of the above.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT