The question in purple uses the values in the purple box- the others are independent
Answer each question using AT LEAST ONE of Excel's TVM functions among =PV, =FV, =PMT, =RATE, =NPER, =IPMT, =PPMT, =CUMIPMT, =CUMPRINC. |
Q4 At the end of which month will it be for the loan outstanding balance to be less than 30% of the original loan amount for the first time? Useful clue: Which function gives the number of time period? Think about the rounding rules for the final answer. Roundup or rounddown? Remember you can check your answer in the amortisation schedule. Q5 New Scenario (Independent): At the end of the month 96, the interest rate increases. To pay off the loan by the original date, you need to increase the original month-end repayment amount by $500. Calculate the increased interest rate p.a. compounded monthly. Useful clue: Which function gives the interest rate? What is the loan outstanding balance when the interest rate changes? Q6 New Scenario (Independent): From the beginning of the loan term, you repay a higher amount of month-end repayment in order to pay off the loan 5 years earlier than the original date. The interest rate does not change. Calculate the amount of interest you can save by paying off the loan earlier. Useful clue: Which function gives the total interest paid? Q7 New Scenario (Independent): Suppose you only repay the interest amount of the loan each month end for the first 16 months. To be able to pay off the loan by the original date, you will need to repay higher equal month-end repayments when the interest-only period expires. Calculate the amount of principal paid from the beginning of month 64 to the end of month 96. Useful clue: Think about how the interest-only term would affect the nper? How would it affect the start_period and end_period ? Q8 New Scenario (Independent): You lose your job because of COVID-19 and the bank agrees that you do not make any repayment from the beginning of month 112 to the end of month 128. The interest keeps accumulating during this period. From month 129, you will need to repay higher equal month-end repayments to pay off the loan by the original date. Calculate the increased month-end repayment amount. Useful clue: You can start with calculating the loan outstanding balance when you resume the repayments. You can use a function as an input/argument for another function. Number of years you have to repay the loan, n. 16 192 Number of months you have to repay the loan, n*m Nominal annual interest rate p.a compounded monthly (jm) Effective monthly interest rate (i=jm/m) 7.00% 0.58% Original loan amount $907,242 Fixed monthly repayment (P&I) amount $7,867.68 A