In: Finance
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!
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 |