10. (Mortgage EAIR) Your local bank has offered you a 20-year, $100,000 mortgage
ID: 2817013 • Letter: 1
Question
10. (Mortgage EAIR) Your local bank has offered you a 20-year, $100,000 mortgage. The bank is charging 1.5 points, with "processing" costs of $750; both points and processing costs are deducted from the mortgage when it is given. The mortgage carries a 10% annual interest rate and is paid in equal monthly payments. Note that the monthly payments on the mortgage are cal- culated on the full amount of the mortgage (that is, $100,000). Calculate the monthly payment on the mortgage, show the amortiza- tion table, and compute the EAIR. Will the EAIR of the mortgage change if the loan period is 6 years? a. b.Explanation / Answer
Answer a. The monthly payment on the loan can be calculated either using a financial calculator or the PMT function in Microsoft Excel. The function requires us to input three values i.e. the interest rate adjusted for compounding frequency (rate), number of periods in months (nper) and present value or the total principal value of the loan (PV).
The three values in this case are as given below:
rate = 10%/12 (because the loan has a monthly payment frequency) = 0.833%
nper = 20*12 (as it needs to be expressed in months based on the payment frequency) = 240
PV = -$100,000 (mortgage value expressed in negative as required by the function so that payment value is displayed in positive)
The Excel function calculates the monthly payment based on these values, which comes out to $965.02.
The Effective Annual Interest Rate (EAIR) is primarily based on the stated annual interest rate and the associated compounding frequency. In this case the stated annual interest rate is 10% (int) and the compounding frequency is the same as the payment frequency per year which is 12 (n). The EAIR is calculated using the below formula:
EAIR = ( 1 + (int / n) )^n - 1
Replacing the variables with the values, we get:
EAIR = (1 + (10% / 12) )^12 - 1 = 10.47%
Thus the EAIR for this scenario will be 10.47%.
The detailed amortization schedule for the mortgage is tabulated in the comments section below (schedule for the first 5 years is presented below due to character limitation per answer):
Month
Beginning Balance
Total Payment
Interest Paid
Principal Paid
Ending Balance
1
$100,000.00
$965.02
$833.33
$131.69
$99,868.31
2
$99,868.31
$965.02
$832.24
$132.79
$99,735.53
3
$99,735.53
$965.02
$831.13
$133.89
$99,601.63
4
$99,601.63
$965.02
$830.01
$135.01
$99,466.63
5
$99,466.63
$965.02
$828.89
$136.13
$99,330.49
6
$99,330.49
$965.02
$827.75
$137.27
$99,193.23
7
$99,193.23
$965.02
$826.61
$138.41
$99,054.81
8
$99,054.81
$965.02
$825.46
$139.56
$98,915.25
9
$98,915.25
$965.02
$824.29
$140.73
$98,774.52
10
$98,774.52
$965.02
$823.12
$141.90
$98,632.62
11
$98,632.62
$965.02
$821.94
$143.08
$98,489.54
12
$98,489.54
$965.02
$820.75
$144.28
$98,345.26
13
$98,345.26
$965.02
$819.54
$145.48
$98,199.78
14
$98,199.78
$965.02
$818.33
$146.69
$98,053.09
15
$98,053.09
$965.02
$817.11
$147.91
$97,905.18
16
$97,905.18
$965.02
$815.88
$149.15
$97,756.04
17
$97,756.04
$965.02
$814.63
$150.39
$97,605.65
18
$97,605.65
$965.02
$813.38
$151.64
$97,454.01
19
$97,454.01
$965.02
$812.12
$152.90
$97,301.10
20
$97,301.10
$965.02
$810.84
$154.18
$97,146.92
21
$97,146.92
$965.02
$809.56
$155.46
$96,991.46
22
$96,991.46
$965.02
$808.26
$156.76
$96,834.70
23
$96,834.70
$965.02
$806.96
$158.07
$96,676.63
24
$96,676.63
$965.02
$805.64
$159.38
$96,517.25
25
$96,517.25
$965.02
$804.31
$160.71
$96,356.54
26
$96,356.54
$965.02
$802.97
$162.05
$96,194.49
27
$96,194.49
$965.02
$801.62
$163.40
$96,031.09
28
$96,031.09
$965.02
$800.26
$164.76
$95,866.33
29
$95,866.33
$965.02
$798.89
$166.14
$95,700.19
30
$95,700.19
$965.02
$797.50
$167.52
$95,532.67
31
$95,532.67
$965.02
$796.11
$168.92
$95,363.75
32
$95,363.75
$965.02
$794.70
$170.32
$95,193.43
33
$95,193.43
$965.02
$793.28
$171.74
$95,021.69
34
$95,021.69
$965.02
$791.85
$173.17
$94,848.51
35
$94,848.51
$965.02
$790.40
$174.62
$94,673.90
36
$94,673.90
$965.02
$788.95
$176.07
$94,497.82
37
$94,497.82
$965.02
$787.48
$177.54
$94,320.28
38
$94,320.28
$965.02
$786.00
$179.02
$94,141.26
39
$94,141.26
$965.02
$784.51
$180.51
$93,960.75
40
$93,960.75
$965.02
$783.01
$182.02
$93,778.74
41
$93,778.74
$965.02
$781.49
$183.53
$93,595.20
42
$93,595.20
$965.02
$779.96
$185.06
$93,410.14
43
$93,410.14
$965.02
$778.42
$186.60
$93,223.54
44
$93,223.54
$965.02
$776.86
$188.16
$93,035.38
45
$93,035.38
$965.02
$775.29
$189.73
$92,845.65
46
$92,845.65
$965.02
$773.71
$191.31
$92,654.35
47
$92,654.35
$965.02
$772.12
$192.90
$92,461.44
48
$92,461.44
$965.02
$770.51
$194.51
$92,266.93
49
$92,266.93
$965.02
$768.89
$196.13
$92,070.80
50
$92,070.80
$965.02
$767.26
$197.76
$91,873.04
51
$91,873.04
$965.02
$765.61
$199.41
$91,673.63
52
$91,673.63
$965.02
$763.95
$201.07
$91,472.55
53
$91,472.55
$965.02
$762.27
$202.75
$91,269.80
54
$91,269.80
$965.02
$760.58
$204.44
$91,065.36
55
$91,065.36
$965.02
$758.88
$206.14
$90,859.22
56
$90,859.22
$965.02
$757.16
$207.86
$90,651.36
57
$90,651.36
$965.02
$755.43
$209.59
$90,441.76
58
$90,441.76
$965.02
$753.68
$211.34
$90,230.42
59
$90,230.42
$965.02
$751.92
$213.10
$90,017.32
60
$90,017.32
$965.02
$750.14
$214.88
$89,802.44
Answer b. As explained above, since the Effective Annual Interest Rate (EAIR) is primarily based on the stated annual interest rate and the associated compounding frequency, a change in the tenure of the loan from 20 years to 6 years will not affect its EAIR. Thus the EAIR will be 10.47% irrespective of the tenure of the loan as long as the stated annual interest rate and compounding frequency (repayment frequency) continue to be the same.
Answer c. We can refer to the amortization table given above to calculate the total interest paid in each year of the mortgage. The annual interest payments made are summarized below:
Year
Interest Paid
1
$9,925.52
2
$9,752.25
3
$9,560.83
4
$9,349.37
5
$9,115.77
6
$8,857.70
7
$8,572.62
8
$8,257.68
9
$7,909.76
10
$7,525.41
11
$7,100.81
12
$6,631.76
13
$6,113.58
14
$5,541.15
15
$4,908.78
16
$4,210.19
17
$3,438.44
18
$2,585.89
19
$1,644.06
20
$603.61
Month
Beginning Balance
Total Payment
Interest Paid
Principal Paid
Ending Balance
1
$100,000.00
$965.02
$833.33
$131.69
$99,868.31
2
$99,868.31
$965.02
$832.24
$132.79
$99,735.53
3
$99,735.53
$965.02
$831.13
$133.89
$99,601.63
4
$99,601.63
$965.02
$830.01
$135.01
$99,466.63
5
$99,466.63
$965.02
$828.89
$136.13
$99,330.49
6
$99,330.49
$965.02
$827.75
$137.27
$99,193.23
7
$99,193.23
$965.02
$826.61
$138.41
$99,054.81
8
$99,054.81
$965.02
$825.46
$139.56
$98,915.25
9
$98,915.25
$965.02
$824.29
$140.73
$98,774.52
10
$98,774.52
$965.02
$823.12
$141.90
$98,632.62
11
$98,632.62
$965.02
$821.94
$143.08
$98,489.54
12
$98,489.54
$965.02
$820.75
$144.28
$98,345.26
13
$98,345.26
$965.02
$819.54
$145.48
$98,199.78
14
$98,199.78
$965.02
$818.33
$146.69
$98,053.09
15
$98,053.09
$965.02
$817.11
$147.91
$97,905.18
16
$97,905.18
$965.02
$815.88
$149.15
$97,756.04
17
$97,756.04
$965.02
$814.63
$150.39
$97,605.65
18
$97,605.65
$965.02
$813.38
$151.64
$97,454.01
19
$97,454.01
$965.02
$812.12
$152.90
$97,301.10
20
$97,301.10
$965.02
$810.84
$154.18
$97,146.92
21
$97,146.92
$965.02
$809.56
$155.46
$96,991.46
22
$96,991.46
$965.02
$808.26
$156.76
$96,834.70
23
$96,834.70
$965.02
$806.96
$158.07
$96,676.63
24
$96,676.63
$965.02
$805.64
$159.38
$96,517.25
25
$96,517.25
$965.02
$804.31
$160.71
$96,356.54
26
$96,356.54
$965.02
$802.97
$162.05
$96,194.49
27
$96,194.49
$965.02
$801.62
$163.40
$96,031.09
28
$96,031.09
$965.02
$800.26
$164.76
$95,866.33
29
$95,866.33
$965.02
$798.89
$166.14
$95,700.19
30
$95,700.19
$965.02
$797.50
$167.52
$95,532.67
31
$95,532.67
$965.02
$796.11
$168.92
$95,363.75
32
$95,363.75
$965.02
$794.70
$170.32
$95,193.43
33
$95,193.43
$965.02
$793.28
$171.74
$95,021.69
34
$95,021.69
$965.02
$791.85
$173.17
$94,848.51
35
$94,848.51
$965.02
$790.40
$174.62
$94,673.90
36
$94,673.90
$965.02
$788.95
$176.07
$94,497.82
37
$94,497.82
$965.02
$787.48
$177.54
$94,320.28
38
$94,320.28
$965.02
$786.00
$179.02
$94,141.26
39
$94,141.26
$965.02
$784.51
$180.51
$93,960.75
40
$93,960.75
$965.02
$783.01
$182.02
$93,778.74
41
$93,778.74
$965.02
$781.49
$183.53
$93,595.20
42
$93,595.20
$965.02
$779.96
$185.06
$93,410.14
43
$93,410.14
$965.02
$778.42
$186.60
$93,223.54
44
$93,223.54
$965.02
$776.86
$188.16
$93,035.38
45
$93,035.38
$965.02
$775.29
$189.73
$92,845.65
46
$92,845.65
$965.02
$773.71
$191.31
$92,654.35
47
$92,654.35
$965.02
$772.12
$192.90
$92,461.44
48
$92,461.44
$965.02
$770.51
$194.51
$92,266.93
49
$92,266.93
$965.02
$768.89
$196.13
$92,070.80
50
$92,070.80
$965.02
$767.26
$197.76
$91,873.04
51
$91,873.04
$965.02
$765.61
$199.41
$91,673.63
52
$91,673.63
$965.02
$763.95
$201.07
$91,472.55
53
$91,472.55
$965.02
$762.27
$202.75
$91,269.80
54
$91,269.80
$965.02
$760.58
$204.44
$91,065.36
55
$91,065.36
$965.02
$758.88
$206.14
$90,859.22
56
$90,859.22
$965.02
$757.16
$207.86
$90,651.36
57
$90,651.36
$965.02
$755.43
$209.59
$90,441.76
58
$90,441.76
$965.02
$753.68
$211.34
$90,230.42
59
$90,230.42
$965.02
$751.92
$213.10
$90,017.32
60
$90,017.32
$965.02
$750.14
$214.88
$89,802.44
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.