You have decided to issue a 30 year fixed rate conventional mortgage to the bank
ID: 2643417 • Letter: Y
Question
You have decided to issue a 30 year fixed rate conventional mortgage to the bank to finance the purchase of a $300,000 home. You are required to make a 20% down payment. The mortgage rate in 5% per year. You will make payments monthly. Please use Excel to calculate the monthly interest payments, monthly principle payments and total monthly payments. You are in the 30% tax bracket. What are your after tax mortgage payments. Remember interest on mortgages used to finance a primary residence are tax deductible. You must be able to explain how you arrived at your answer.
Explanation / Answer
Down payment=300,000*20%=$60,000
Mortgage Amount=$300,000-$60,000=$240,000
PmtNo.
Beginning Balance
Scheduled Payment
Total Payment
Principal
Interest
Ending Balance
1
240000.00
1288.37
1288.37
288.37
1000.00
239711.63
2
239711.63
1288.37
1288.37
289.57
998.80
239422.05
3
239422.05
1288.37
1288.37
290.78
997.59
239131.27
4
239131.27
1288.37
1288.37
291.99
996.38
238839.28
5
238839.28
1288.37
1288.37
293.21
995.16
238546.07
6
238546.07
1288.37
1288.37
294.43
993.94
238251.64
7
238251.64
1288.37
1288.37
295.66
992.72
237955.99
8
237955.99
1288.37
1288.37
296.89
991.48
237659.10
9
237659.10
1288.37
1288.37
298.13
990.25
237360.97
10
237360.97
1288.37
1288.37
299.37
989.00
237061.61
11
237061.61
1288.37
1288.37
300.62
987.76
236760.99
12
236760.99
1288.37
1288.37
301.87
986.50
236459.12
13
236459.12
1288.37
1288.37
303.13
985.25
236156.00
14
236156.00
1288.37
1288.37
304.39
983.98
235851.61
15
235851.61
1288.37
1288.37
305.66
982.72
235545.95
16
235545.95
1288.37
1288.37
306.93
981.44
235239.02
17
235239.02
1288.37
1288.37
308.21
980.16
234930.81
18
234930.81
1288.37
1288.37
309.49
978.88
234621.32
19
234621.32
1288.37
1288.37
310.78
977.59
234310.54
20
234310.54
1288.37
1288.37
312.08
976.29
233998.46
PmtNo.
Beginning Balance
Scheduled Payment
Total Payment
Principal
Interest
Ending Balance
1
240000.00
1288.37
1288.37
288.37
1000.00
239711.63
2
239711.63
1288.37
1288.37
289.57
998.80
239422.05
3
239422.05
1288.37
1288.37
290.78
997.59
239131.27
4
239131.27
1288.37
1288.37
291.99
996.38
238839.28
5
238839.28
1288.37
1288.37
293.21
995.16
238546.07
6
238546.07
1288.37
1288.37
294.43
993.94
238251.64
7
238251.64
1288.37
1288.37
295.66
992.72
237955.99
8
237955.99
1288.37
1288.37
296.89
991.48
237659.10
9
237659.10
1288.37
1288.37
298.13
990.25
237360.97
10
237360.97
1288.37
1288.37
299.37
989.00
237061.61
11
237061.61
1288.37
1288.37
300.62
987.76
236760.99
12
236760.99
1288.37
1288.37
301.87
986.50
236459.12
13
236459.12
1288.37
1288.37
303.13
985.25
236156.00
14
236156.00
1288.37
1288.37
304.39
983.98
235851.61
15
235851.61
1288.37
1288.37
305.66
982.72
235545.95
16
235545.95
1288.37
1288.37
306.93
981.44
235239.02
17
235239.02
1288.37
1288.37
308.21
980.16
234930.81
18
234930.81
1288.37
1288.37
309.49
978.88
234621.32
19
234621.32
1288.37
1288.37
310.78
977.59
234310.54
20
234310.54
1288.37
1288.37
312.08
976.29
233998.46
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.