Now that they have accumulated a deposit of $100,000, Joe and Jenny wish to use the deposit and t...
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.
Homework Answers
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 |
Add Answer to:
Now that they have accumulated a deposit of $100,000, Joe and Jenny wish to use the deposit and t...
You wish to buy a house five years from now, which will expectantly cost $550,000 after...
You wish to buy a house five years from now, which will expectantly cost $550,000 after five years. You will pay parts of this price from your personal deposit. You will cover the rest of the purchase price by taking two types of loans at the time of purchase: a 4-year fixed-interest personal loan of $20,000 - a 25-year fixed-interest house loan for the rest of the house price (.e. after paying from the personal deposit and the $20,000 taken...
Samuel and Sandra Sharp wish to borrow $600,000 to buya home. The loan from the Highway...
Samuel and Sandra Sharp wish to borrow $600,000 to buya home. The loan from the Highway Bank requires equal monthly repayments over 20 years, and carries an interest rate of 5-1 % per annum, compounded monthly. The first repayment is due at the end of one month after the loan proceeds are received. You are required to calculate the following. i) The effective annual interest rate on the above loan (show as a percentage correct to 3 decimal places). li)...
QUESTION 6 You have just obtained home financing from a bank. The principal amount is RM750,000...
QUESTION 6 You have just obtained home financing from a bank. The principal amount is RM750,000 and the loan is to be repaid over a period of 20 years via equal end-of-the-month monthly installment payments. The bank charges an annual nominal rate of 6.6%. The calculations are based on the concept of amortization. Required: i. Calculate the monthly installment payment amount. ii. How much of the third monthly installment payment will go towards the repayment of principal? iii. How much...
James King bought a house three years ago that cost $750,000. James put up 20% deposit and borrowed the rest from FC Bank at a rate of 7.2% per annum, compounded monthly, for 10 years. Three months ag...
James King bought a house three years ago that cost $750,000. James put up 20% deposit and borrowed the rest from FC Bank at a rate of 7.2% per annum, compounded monthly, for 10 years. Three months ago, FC Bank notified James that after the last monthly payment for the third year, the interest rate on his loan will increase to 9.6% per annum, compounded monthly, in line with market rates. Also, from the fourth year of his loan James...
Please explain in details on how to calculate (b) and (c). Thank you. You wish to...
Please explain in details on how to calculate (b) and (c).
Thank you.
You wish to buy a house five years from now, which will expectantly cost $550,000 after five years. You will pay parts of this price from your personal deposit. You will cover the rest of the purchase price by taking two types of loans at the time of purchase: a 4-year fixed-interest personal loan of $20,000 - a 25-year fixed-interest house loan for the rest of the...
Problem 3 (Required, 25 marks) Recently, Mr. A has 2 outstanding loans: • Loan A -...
Problem 3 (Required, 25 marks) Recently, Mr. A has 2 outstanding loans: • Loan A - He needs to repay $1720 at the end of every month and there are 36 repayments remaining. The loan charges interest at an annual nominal interest rate 12% compounded monthly Loan B - He needs to repay an amount X at the end of every month and there are 36 repayments remaining. The loan charges interest at an annual nominal interest rate 18% compounded...
Problem 1 (Required, 25 marks) A borrower has borrowed $2000000 from the bank. It is given...
Problem 1 (Required, 25 marks) A borrower has borrowed $2000000 from the bank. It is given that the loan charges interest at an annual effective interest rate 16.0755% and compound interest is assumed. (a) Suppose that the borrower decides to repay the loan by 180 monthly payments made at the end of every month, (i) Using retrospective method, calculate the outstanding balance at 60th repayment date. (ii) Calculate the interest due and principal repaid in 120th repayment. (b) Suppose that...
QUESTION: 1 (6 marks) In March 2019 AAR Ltd reported net profits after tax of $500,000 for the year 2018 andannounced that it will pay the annual dividend on 1 June 2019. AAR expects the net p...
QUESTION: 1 (6 marks) In March 2019 AAR Ltd reported net profits after tax of $500,000 for the year 2018 andannounced that it will pay the annual dividend on 1 June 2019. AAR expects the net profits after tax for 2019 to be 20% higher and it will maintain its current dividend payout ratio of 60%. AAR will pay the annual dividend for 2019 in 1 June, 2020. Karina Adams owns 10% of the ordinary share capital of AAR Pty....
3. Mr. and Mrs. Mercado decided to sell their house and to deposit the fund in a bank. After computing the interest,...
3. Mr. and Mrs. Mercado decided to sell their house and to deposit the fund in a bank. After computing the interest, they found out that they may withdraw P350,000 yearly for 4 years starting at the end of 7 years when their child will be in college. How much is the fund deposited if the interest rate is 3% compounded annually? 4. A group of employees decided to invest a portion of their bonus. After 3 months from today,...
Problem 1. If you deposit $1,000 every 2 years starting now over 10-year period, how much...
Problem 1. If you deposit $1,000 every 2 years starting now over 10-year period, how much money will you accumulate if annual interest rate is 10% compounded monthly? Problem 2. How long will it take for money to double at 10% nominal interest rate, compounded continuously? Problem 3. If $100 in year 0 will be worth $110 a year later, and it was worth $90 a year ago compute the interest rate for the past year and the interest...
You wish to buy a house five years from now, which will expectantly cost $550,000 after five years. You will pay parts of this price from your personal deposit. You will cover the rest of the purchase price by taking two types of loans at the time of purchase: a 4-year fixed-interest personal loan of $20,000 - a 25-year fixed-interest house loan for the rest of the house price (.e. after paying from the personal deposit and the $20,000 taken...
Samuel and Sandra Sharp wish to borrow $600,000 to buya home. The loan from the Highway Bank requires equal monthly repayments over 20 years, and carries an interest rate of 5-1 % per annum, compounded monthly. The first repayment is due at the end of one month after the loan proceeds are received. You are required to calculate the following. i) The effective annual interest rate on the above loan (show as a percentage correct to 3 decimal places). li)...
Please explain in details on how to calculate (b) and (c).
Thank you.
You wish to buy a house five years from now, which will expectantly cost $550,000 after five years. You will pay parts of this price from your personal deposit. You will cover the rest of the purchase price by taking two types of loans at the time of purchase: a 4-year fixed-interest personal loan of $20,000 - a 25-year fixed-interest house loan for the rest of the...
Problem 3 (Required, 25 marks) Recently, Mr. A has 2 outstanding loans: • Loan A - He needs to repay $1720 at the end of every month and there are 36 repayments remaining. The loan charges interest at an annual nominal interest rate 12% compounded monthly Loan B - He needs to repay an amount X at the end of every month and there are 36 repayments remaining. The loan charges interest at an annual nominal interest rate 18% compounded...
Problem 1 (Required, 25 marks) A borrower has borrowed $2000000 from the bank. It is given that the loan charges interest at an annual effective interest rate 16.0755% and compound interest is assumed. (a) Suppose that the borrower decides to repay the loan by 180 monthly payments made at the end of every month, (i) Using retrospective method, calculate the outstanding balance at 60th repayment date. (ii) Calculate the interest due and principal repaid in 120th repayment. (b) Suppose that...
QUESTION: 1 (6 marks) In March 2019 AAR Ltd reported net profits after tax of $500,000 for the year 2018 andannounced that it will pay the annual dividend on 1 June 2019. AAR expects the net profits after tax for 2019 to be 20% higher and it will maintain its current dividend payout ratio of 60%. AAR will pay the annual dividend for 2019 in 1 June, 2020. Karina Adams owns 10% of the ordinary share capital of AAR Pty....
3. Mr. and Mrs. Mercado decided to sell their house and to deposit the fund in a bank. After computing the interest, they found out that they may withdraw P350,000 yearly for 4 years starting at the end of 7 years when their child will be in college. How much is the fund deposited if the interest rate is 3% compounded annually? 4. A group of employees decided to invest a portion of their bonus. After 3 months from today,...
Most questions answered within 3 hours.
-
The factory method and the abstract factory pattern use
different object oriented
mechanisms are used to...
asked 2 hours ago -
Given the data below for the monthly sales data at each branch in
your region for...
asked 3 hours ago -
Blossom Company owns 10000 of the 50000 outstanding shares of
Taylor, Inc. common stock. During 2018,...
asked 4 hours ago -
Calculate the following: ***SHOW ALL WORK!!!! Or
NO CREDIT*** Circle your answers. 8pts
each
In the...
asked 6 hours ago -
Bank Z is currently advertising interest rates on its checking
account. They claim to pay an...
asked 5 hours ago -
List two ways of transformation on the response variable that
can be used to deal with...
asked 6 hours ago -
If a 2000 ohm resistor has a -3.90 mA current going through it.
What is the...
asked 6 hours ago -
Please comment on the sentences.
Some types of jobs require more training than others. Some
companies...
asked 8 hours ago -
The )G01 for the hydrolysis of phosphorarginine
reaction depicted below is –32 kJ mol-1.
Phosphoarginine ...
asked 8 hours ago -
Cross a heterozygous blue-eyed goat with a homozygous brown-eyed
goat. Be sure to indicate which kids...
asked 9 hours ago -
Use the following information to answer the next two
questions.
Please refer to question 9-90. A...
asked 9 hours ago -
A solution containing 0.050 g of an unknown electrolyte in 2.50
g of cyclohexane was found...
asked 9 hours ago