# 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