Question

In: Finance

Now that they have accumulated a deposit of $100,000, Joe and Jenny wish to use the...

Now that they have accumulated a deposit of $100,000, Joe and Jenny wish to use the deposit and take out a housing loan to purchase a home. The home costs $900,000. The loan is to be repaid in equal monthly instalments over a term of 20 years. Jenny recalls that the interest rate quoted by the bank is an annual nominal rate of 6.0%pa compounded monthly. After 5 years (60th repayment just made), the bank announces the interest rate will change to an annual nominal rate of 7.2%pa compounded monthly.

(i) How much is the original monthly repayment?

(ii) How much is the monthly repayment after the interest rate rise, assuming the term is unchanged?

(ii) Provide Joe and Jenny with a repayment schedule after the interest rate rise using excel.

Solutions

Expert Solution

Solution i) Calculation of Original Monthly repayment installment

Following are the steps to be followed on Microsoft Excel to calculate the Monthly Installment:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "PMT"
Step 4: Insert Rate = 0.06/12 Nper = 20*12 PV = -800000

PMT = $5731.45

Therefore, the original monthly installment is $5731.45.

Solution ii) Calculation of Monthly repayment installment after the change in Interest rate to 7.2% per year

After 60th Installment, the outstanding loan = $679196.78

Following are the steps to be followed on Microsoft Excel to calculate the Interest payment:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "PMT"
Step 4: Insert Rate = 0.072/12    Nper = 15*12 PV = -679196.78

PMT = $6181.01

Therefore, the new monthly installment after 60th Month is $6181.01.

Solution iii) Loan Repayment Schedule

Following are the steps to be followed on Microsoft Excel to calculate the Interest payment:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "IPMT"
Step 4: Insert Rate = 0.072/12    Per = 1 Nper = 15*12 PV = -679196.78

IPMT = $4075.18

Principal payment = Total payment – Interest payment

Ending balance = Beginning balance – Principal payment

Keeping the Rate, Nper and PV as constant drag the formula till 180th Month, you will derive the schedule

The Entire repayment schedule cannot be pasted. Hence, I am providing the repayment schedule for next 5 years.

Month

Beginning Balance

Total Payment

Interest Payment

Principal Payment

Ending Balance

61

6,79,196.79

6,181.01

4,075.18

2,105.83

6,77,090.96

62

6,77,090.96

6,181.01

4,062.55

2,118.46

6,74,972.50

63

6,74,972.50

6,181.01

4,049.83

2,131.17

6,72,841.32

64

6,72,841.32

6,181.01

4,037.05

2,143.96

6,70,697.36

65

6,70,697.36

6,181.01

4,024.18

2,156.82

6,68,540.54

66

6,68,540.54

6,181.01

4,011.24

2,169.76

6,66,370.78

67

6,66,370.78

6,181.01

3,998.22

2,182.78

6,64,187.99

68

6,64,187.99

6,181.01

3,985.13

2,195.88

6,61,992.11

69

6,61,992.11

6,181.01

3,971.95

2,209.06

6,59,783.06

70

6,59,783.06

6,181.01

3,958.70

2,222.31

6,57,560.75

71

6,57,560.75

6,181.01

3,945.36

2,235.64

6,55,325.10

72

6,55,325.10

6,181.01

3,931.95

2,249.06

6,53,076.04

73

6,53,076.04

6,181.01

3,918.46

2,262.55

6,50,813.49

74

6,50,813.49

6,181.01

3,904.88

2,276.13

6,48,537.37

75

6,48,537.37

6,181.01

3,891.22

2,289.78

6,46,247.58

76

6,46,247.58

6,181.01

3,877.49

2,303.52

6,43,944.06

77

6,43,944.06

6,181.01

3,863.66

2,317.34

6,41,626.71

78

6,41,626.71

6,181.01

3,849.76

2,331.25

6,39,295.47

79

6,39,295.47

6,181.01

3,835.77

2,345.24

6,36,950.23

80

6,36,950.23

6,181.01

3,821.70

2,359.31

6,34,590.92

81

6,34,590.92

6,181.01

3,807.55

2,373.46

6,32,217.46

82

6,32,217.46

6,181.01

3,793.30

2,387.70

6,29,829.76

83

6,29,829.76

6,181.01

3,778.98

2,402.03

6,27,427.73

84

6,27,427.73

6,181.01

3,764.57

2,416.44

6,25,011.29

85

6,25,011.29

6,181.01

3,750.07

2,430.94

6,22,580.35

86

6,22,580.35

6,181.01

3,735.48

2,445.53

6,20,134.82

87

6,20,134.82

6,181.01

3,720.81

2,460.20

6,17,674.62

88

6,17,674.62

6,181.01

3,706.05

2,474.96

6,15,199.66

89

6,15,199.66

6,181.01

3,691.20

2,489.81

6,12,709.85

90

6,12,709.85

6,181.01

3,676.26

2,504.75

6,10,205.10

91

6,10,205.10

6,181.01

3,661.23

2,519.78

6,07,685.32

92

6,07,685.32

6,181.01

3,646.11

2,534.90

6,05,150.43

93

6,05,150.43

6,181.01

3,630.90

2,550.11

6,02,600.32

94

6,02,600.32

6,181.01

3,615.60

2,565.41

6,00,034.92

95

6,00,034.92

6,181.01

3,600.21

2,580.80

5,97,454.12

96

5,97,454.12

6,181.01

3,584.72

2,596.28

5,94,857.83

97

5,94,857.83

6,181.01

3,569.15

2,611.86

5,92,245.97

98

5,92,245.97

6,181.01

3,553.48

2,627.53

5,89,618.44

99

5,89,618.44

6,181.01

3,537.71

2,643.30

5,86,975.14

100

5,86,975.14

6,181.01

3,521.85

2,659.16

5,84,315.98

101

5,84,315.98

6,181.01

3,505.90

2,675.11

5,81,640.87

102

5,81,640.87

6,181.01

3,489.85

2,691.16

5,78,949.71

103

5,78,949.71

6,181.01

3,473.70

2,707.31

5,76,242.40

104

5,76,242.40

6,181.01

3,457.45

2,723.55

5,73,518.85

105

5,73,518.85

6,181.01

3,441.11

2,739.90

5,70,778.95

106

5,70,778.95

6,181.01

3,424.67

2,756.33

5,68,022.62

107

5,68,022.62

6,181.01

3,408.14

2,772.87

5,65,249.74

108

5,65,249.74

6,181.01

3,391.50

2,789.51

5,62,460.23

109

5,62,460.23

6,181.01

3,374.76

2,806.25

5,59,653.99

110

5,59,653.99

6,181.01

3,357.92

2,823.08

5,56,830.90

111

5,56,830.90

6,181.01

3,340.99

2,840.02

5,53,990.88

112

5,53,990.88

6,181.01

3,323.95

2,857.06

5,51,133.82

113

5,51,133.82

6,181.01

3,306.80

2,874.21

5,48,259.61

114

5,48,259.61

6,181.01

3,289.56

2,891.45

5,45,368.16

115

5,45,368.16

6,181.01

3,272.21

2,908.80

5,42,459.36

116

5,42,459.36

6,181.01

3,254.76

2,926.25

5,39,533.11

117

5,39,533.11

6,181.01

3,237.20

2,943.81

5,36,589.30

118

5,36,589.30

6,181.01

3,219.54

2,961.47

5,33,627.83

119

5,33,627.83

6,181.01

3,201.77

2,979.24

5,30,648.59

120

5,30,648.59

6,181.01

3,183.89

2,997.12

5,27,651.47

jeff jeffy answered 1 year ago
Next > < Previous

Related Solutions

Suppose there are two investors: Joe and Bob. Both have pension funds, into which they deposit...
Suppose there are two investors: Joe and Bob. Both have pension funds, into which they deposit money each month from their paychecks. Both are in their early 30s, and anticipate retiring at around age 65. Neither anticipates withdrawing any money from his pension fund prior to retirement. Joe watches his pension fund closely, looking each week at whether has gone up or down in value. On a week to week basis, the US equity markets are down almost as often...
You wish to retire in 27 years, at which time you want to have accumulated enough...
You wish to retire in 27 years, at which time you want to have accumulated enough money to receive an annuity of $60,000 for 30 years after retirement. During the period before retirement you can earn 9% annually, while after retirement you can earn 7% on your money. What annual contributions to the retirement fund will allow you to receive the $60,000 annuity? $5,380.73 b. $7,248.05 c. $4,648.14 d. $8,417.52
You wish to retire in 12 years, at which time you want to have accumulated enough...
You wish to retire in 12 years, at which time you want to have accumulated enough money to receive an annual annuity of $15,000 for 17 years after retirement. During the period before retirement you can earn 11 percent annually, while after retirement you can earn 13 percent on your money. What annual contributions to the retirement fund will allow you to receive the $15,000 annuity?
(1) You wish to retire in 10 years, at which time you want to have accumulated...
(1) You wish to retire in 10 years, at which time you want to have accumulated enough money to receive an annual annuity of $13,000 for 15 years after retirement. During the period before retirement you can earn 9 percent annually, while after retirement you can earn 11 percent on your money. What annual contributions to the retirement fund will allow you to receive the $13,000 annuity? Use Appendix C and Appendix D for an approximate answer, but calculate your...
We now wish to decide if there “use of e-cigarettes” and “income category” are dependent. To...
We now wish to decide if there “use of e-cigarettes” and “income category” are dependent. To assist with this process, the table from before has been augmented with most of the expected frequencies (listed in parentheses): <$35,000 $35,000-$99,999 $100,000+ Total Vape 47 (34) 57 (58) 19 (31) 123 Do Not Vape 381 (394) 659 (???) 362 (???) 1402 Total 428 716 381 1525 2.a. Find the missing two expected frequencies, labeled as (???). For credit, be sure to show your...
An initial deposit of $10000 is made into an account now, and a second deposit of...
An initial deposit of $10000 is made into an account now, and a second deposit of $18000 is made into the same account after 15 years. During the first 10 years, the account earns interest quoted at a nominal quarterly discount rate of d %. After 10 years have passed, any money in the account earns a nominal semi-annual interest rate of 4.6%. At the end of 30 years, the total value of the account will be $86440. What is...
The Johnsons have accumulated a nest egg of $50,000 that they intend to use as a...
The Johnsons have accumulated a nest egg of $50,000 that they intend to use as a down payment toward the purchase of a new house. Because their present gross income has placed them in a relatively high tax bracket, they have decided to invest a minimum of $2700/month in monthly payments (to take advantage of the tax deduction) toward the purchase of their house. However, because of other financial obligations, their monthly payments should not exceed $3000. If the Johnsons...
The Johnsons have accumulated a nest egg of $40,000 that they intend to use as a...
The Johnsons have accumulated a nest egg of $40,000 that they intend to use as a down payment toward the purchase of a new house. Because their present gross income has placed them in a relatively high tax bracket, they have decided to invest a minimum of $2300/month in monthly payments (to take advantage of the tax deduction) toward the purchase of their house. However, because of other financial obligations, their monthly payments should not exceed $2900. If local mortgage...
The Johnsons have accumulated a nest egg of $50,000 that they intend to use as a...
The Johnsons have accumulated a nest egg of $50,000 that they intend to use as a down payment toward the purchase of a new house. Because their present gross income has placed them in a relatively high tax bracket, they have decided to invest a minimum of $2900/month in monthly payments (to take advantage of the tax deduction) toward the purchase of their house. However, because of other financial obligations, their monthly payments should not exceed $3500. If local mortgage...
The Johnsons have accumulated a nest egg of $50,000 that they intend to use as a...
The Johnsons have accumulated a nest egg of $50,000 that they intend to use as a down payment toward the purchase of a new house. Because their present gross income has placed them in a relatively high tax bracket, they have decided to invest a minimum of $2400/month in monthly payments (to take advantage of the tax deduction) toward the purchase of their house. However, because of other financial obligations, their monthly payments should not exceed $3000. If local mortgage...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT