Q5 (1 mark) New Scenario (Independent): At the end of the month 95, the interest rate increases. To pay off the loan by the original date, you need to increase the original month-end repayment amount by 6%. Calculate the increased interest rate p.a. compounded monthly. US lo ch in 7 Q6 (1 mark) New Scenario (Independent): At the end of the year 7, the interest rate is increased. The new rate is 6% p.a. compounded monthly more than the original rate. To pay off the loan by the original date, calculate the increased month-end repayment amount. Us loc ch: ins 8 07 (1 mark) New Scenario (Independent): Because of COVID-19, the bank agrees to extend your total loan ter by 8 years. The interest rate is unchanged. Calculate how much more total interest you need to repay in this case. Use int 9 Q8 (1 mark) New Scenario (Independent): Suppose you only repay the interest amount of the loan each month end for the first 6 years. To be able to pay off the loan by the original date, you will repay a higher month-end repayment including the principal and the interest when the interest only period expires. Calculate the loan outstanding balance at the end of year 9. Use req off fun fun 10 09 (1 mark) New Scenario (Independent): You lose your job because of COVID-19 and the bank agrees that you do not make any repayment for the first 72 months. The interest keeps accumulating during this period. From month 73, you will need to repay equal month-end repayments including the principal and the interest (P&l). Calculate the month-end repayment you need to pay off the loan by the original date. Use log rep inp 11 12 13 E D F G H I J K L M WACT1001 Excel Assignment Part A: In this part of the assignment, you are required to prepare a loan repayment schedule for a mortgage loan you borrowed from the bank. The loan is to be repaid by equal month-end repayments starting from the end of the first month. The monthly repayments include both the interest and the 1 principal of the loan (P&I). The term of the loan is n years. 2 Complete the table below showing the variables you need for the loan repayment schedule Student number digit by digit 46456872 3 4 Number of years you have to repay the loann 19 5 Number of months you have to repay the loan, n'm 228 6 Nominal annual interest ratep.a compounded monthly u.) 10.00N 7 Effective monthly interest rate i./m) 0.83% 8 Original loan amount $929,744 9 Fixed monthly repayment (P81) amount 59,123.20 Answer must be positive greater than o) 10 DO NOT Round any amounts in the Amortisation Schedule. Use format commands to display as currency 11 Loan Amortisation Schedule 12 Month Balance at the beginning of month Interest pald Principal paid Balance at the end of month 13 1 $929,744.00 $7,747.87 $1.375.33 5928,368.67 14 2 $928,368.67 $7,736,41 $1,386.79 $926,981.88 15 3 $926,981.88 $7,724.85 $1,398.35 5925,583.53 16 $925,583.53 $7,713.20 $1.410.00 5924,173.53 17 5 $924,173.53 $7,701.45 $1,421.75 $922,751.78 18 6 $922,751.78 $7,689.60 $1,433.60 $921,318.19 19 7 $921,318.19 $7,677.65 $1,445.54 5919,872.64 20 8 $919,872.64 $7,665.61 $1,457.59 $918,415.05 21 9 $918,415.05 $7,653.46 $1,469.74 5916,945.31 22 10 $916,945.31 $7,641.21 $1,481.98 $915,463.33 23 11 $915,463.33 $7,628.86 $1,494.33 5913,968.99 24 12 $913,968.99 $7,616.41 $1,506.79 $912.462.21 25 13 $912,462.21 $7,603.85 $1,519.34 $910,942.86 26 14 $910.942.86 $7,591.19 $1,532.01 $909,410.86 27 15 5909.410.86 $7.578.42 $1,544.77 5907,866.09 28 5907,866.09 16 $7,565.55 $1,557,65 5906,308.44 29 17 $906,308,44 $7.552.57 $1,570.63 5904,737.81 30 18 $904,737.81 $7.539.48 $903,154.10 $1,583.71 $903,154,10 $7,526.28 31 19 $901,557.19 $1,596.91 32 20 $901,557.19 $7,512.98 $1,610.22 $899,946.97 $899.946.97 21 33 $7,499.56 $1,623.64 $898,323.33 1998 17133 52 486.03 $1,637.17 $896,686.16 0 WACT1001 Excel Assignment PART B: Based on your amortisation schedule, calculate answers to Q1 to Q7 in the highlighted cells below using excel TVM functions and referencing the relevant cells in worksheet Amortisation Schedule Answer each question using AT LEAST ONE of Excel's TVM functions from among =PV, PV, PMT, RATE-NPER, HIPMT, PPMT, CUMIPMT, CUMPRINC. Question 1 to Question 4 are based on the Amortisation Schedule you created in Part A. Question 5 to Question 9 are new independent scenarios which are not related to each other. For all the questions below, your answer must be positive (greater than 0). Do NOT round your answer (unless required by the questions) but format as dollars and cents or percentages if necessary DO NOT indude any words such as "months" in your answer. Smark) Calculate the amount of principal paid in the month 38. $1,869.65 Calculate the total amount of interest paid from the beginning of month 76 to the end of month $116,559.45 mark) 114 mark) Calculate the amount of the loan outstanding balance at the end of month 57. $829,919.47 nark) At the end of which month will it be for the loan outstanding balance to be less than 80% of the original loan amount for the first time? Useful clue: Which function gives the number of time periods? Think about the rounding rules for the final answer. Roundup or rounddown? Remember you can check your answer in the amortisation schedule. ark) New Scenario (Independent): At the end of the month 95, the interest rate increases. To pay off the loan by the original date, you need to increase the original month-end repayment amount by 6%. Calculate the increased interest rate p.a.compounded monthly Useful clue. You can start with calculating the joan outstanding balance when the interest changes. You can use a function as an input/argument for another function ark) New Scenario (Independent): At the end of the year 7, the interest rate is increased. The new rate is 6% p.a. compounded monthly more than the original rate. To pay off the loan by the original date, calculate the increased month-end repayment amount. Useful clut: You can start with calculating the loan outstanding balance when the interest changes. You can use a function as an input/orgument for another function . rk) New Scenario (Independent): Because of COVID-19, the bank agrees to extend your total loan term by 8 years. The interest rate is unchanged. Calculate how much more total interest you need to repay in this case. A Amortisation Schedule A Added Questions + Useful clue: Which function gives the total $37,731,702.84 interest pold