# Finance Ch4 In-class Excel Exercise Payment Values I would like you to answer both sheets c & d Question about mortgage loans and amortization You just dec

Finance Ch4 In-class Excel Exercise Payment Values I would like you to answer both sheets c & d Question about mortgage loans and amortization
You just decided to buy a house at the price of \$500,000. When you go to the bank, they
fixed-interest loan with monthly payment; (2) a 15-year fixed-interest loan with quarterly
these two options, the bank also said “we will offer the same APR (APR=8%) regardless
quarterly for the fixed-interest loans”.
(a) If you decide to take the 15-year loan with fixed-interest and quarterly payment, wha
payment in year 5 versus total interest payment over the entire schedule?
APR =
Yeas-to-Maturity
PV =
Compounding Periods per Year
PMT (quarterly)
\$
8%
15
500,000.00
4
=PMT()
Total Int Payment =
Int Payment in Year 5 =
% of Int Pay in Y5/Total Int Payment from Y1 to Y15 =
=sum(F15:F114)
=sum(F31:F34)
Year
Beginning
Balance
Quarter
1
1
1
1
2
2
2
2
3
3
3
3
4
4
4
4
5
5
5
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
5
6
6
6
6
7
7
7
7
8
8
8
8
9
9
9
9
10
10
10
10
11
11
11
11
12
12
12
12
13
13
13
13
14
14
14
14
15
15
15
15
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
go to the bank, they give you two choices: (1) a 10-year
loan with quarterly payment. While you are considering
R=8%) regardless you are paying the mortgage monthly or
terly payment, what is the percentage of total interest
e?
(F15:F114)
Total Payment
Interest
Payment
Principal
Payment
Ending
Balance
(b) If your objective is to minimize the total interest payments (not in present value or futur
amount of the sum of the total interests you pay on the mortgage), which loan you should ta
APR =
Yeas-to-Maturity
PV =
Compounding Periods per Year
PMT (monthly)
\$
8%
10
500,000.00
12
Total Int Payment =
=sum(F14:F133)
Year
Month
1
1
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
2
2
3
3
3
3
3
Beginning
Balance
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Total Payment
3
3
3
3
3
3
3
4
4
4
4
4
4
4
4
4
4
4
4
5
5
5
5
5
5
5
5
5
5
5
5
6
6
6
6
6
6
6
6
6
6
6
6
7
7
7
7
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
7
7
7
7
7
7
7
7
8
8
8
8
8
8
8
8
8
8
8
8
9
9
9
9
9
9
9
9
9
9
9
9
10
10
10
10
10
10
10
10
10
10
10
10
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
in present value or future value, — simply the dollar
which loan you should take?
Interest Payment
Principal Payment
Ending Balance
(c) In another scenario (not related to part a or b), let’s assume that you prefer the 10
year variable-interest mortgage loan with the first 3 years locked with an APR of 3%.
Somehow you believe that the floating interest rate is going to be within range of 1%
First, calculate the two separete amortization schedules for (a) first 3 years with fixed
Next, conduct a sensitivity analysis of how your monthly payment (PMT) and total in
for the 7 years.
First, assuming that the APR is 3% throughout the 10 years, calculate the PMT:
For the first 3 years:
APR =
3%
Yeas-to-Maturity
10
PV =
\$ 500,000.00
Compounding Periods per Year
12
PMT (quarterly)
Total Int Payment =
Year
1
1
1
1
1
1
1
1
1
1
Month
Beginning Balance
1
2
3
4
5
6
7
8
9
10
1
1
2
2
2
2
2
2
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3
3
3
3
After the first 3 years, we have:
APR =
Yeas-to-Maturity
PV =
Compounding Periods per Year
PMT (quarterly)
Year
4
4
4
4
4
4
4
4
4
4
4
4
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
\$
4.50%
7
=ending balance at end of Y3
12
= the monthly payment from Y4 to Y10
Month
Beginning Balance
37
38
39
40
41
42
43
44
45
46
47
48
5
5
5
5
5
5
5
5
5
5
5
5
6
6
6
6
6
6
6
6
6
6
6
6
7
7
7
7
7
7
7
7
7
7
7
7
8
8
8
8
8
8
8
8
8
8
8
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
8
9
9
9
9
9
9
9
9
9
9
9
9
10
10
10
10
10
10
10
10
10
10
10
10
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
s assume that you prefer the 10-year loan because you want to pay off the loan faster. Now the bank also off
ears locked with an APR of 3%. And after 3 years, the bank will use floating interest rate based on market con
going to be within range of 1% to 10%, with 4.5% being the most likely number.
es for (a) first 3 years with fixed 3% APR; (b) the remaining 7 years with 4.5% APR.
hly payment (PMT) and total interest payment for these 10 years are going to differ across different assumptio
Sensitivity Analysis Using Data -> What-if Analysis -> Data Table
PMT for 7 years
Total Int Payment for 10 Years
APR
1%
1.50%
2.00%
2.50%
3.00%
3.50%
4.00%
4.50%
5.00%
5.50%
6.00%
6.50%
7.00%
7.50%
8.00%
8.50%
9.00%
9.50%
10.00%
Total Payment
Interest Payment
Principal Payment
Ending Balance
ance at end of Y3
y payment from Y4 to Y10
Total Payment
Interest Payment
Principal Payment
Ending Balance
Now the bank also offers a 10te based on market condition.
oss different assumptions of APR
(d) Bonus Question, 5 points. Following (c), what is the average annual interest from the
terms of total interest payments?
First, assuming that the APR is 3% throughout the 10 years, calculate the PMT (same as in C)
For the first 3 years:
APR =
3%
Yeas-to-Maturity
10
PV =
\$ 500,000.00
Compounding Periods per Year
12
PMT (quarterly)
Total Int Payment =
Total Int Payment for Fixed 10-year APR
Year
1
1
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
2
2
3
3
3
3
3
3
3
Month
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
3
3
3
3
3
After the first 3 years, we have:
APR =
Yeas-to-Maturity
PV =
Compounding Periods per Year
PMT (quarterly)
Year
4
4
4
4
4
4
4
4
4
4
4
4
5
5
5
5
5
5
5
5
5
5
5
5
6
6
6
6
6
6
6
6
6
32
33
34
35
36
\$
4.50%
7
12
Month
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
6
6
6
7
7
7
7
7
7
7
7
7
7
7
7
8
8
8
8
8
8
8
8
8
8
8
8
9
9
9
9
9
9
9
9
9
9
9
9
10
10
10
10
10
10
10
10
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
10
10
10
10
117
118
119
120
is the average annual interest from the 4th year to the 10th year that makes the variable-interest loan and fixed
late the PMT (same as in C)
Use Excel Solver
Hint: take the answer from Part(b)
Beginning Balance
Total Payment
Interest Payment
Principal Payment
=ending balance at end of Y3
= the monthly payment from Y4 to Y10
Beginning Balance
Total Payment
Interest Payment
Principal Payment
variable-interest loan and fixed-interest loan equivalent in
Ending Balance
Ending Balance

Purchase answer to see full
attachment