D E G F Input Output First Idea 2 Loan Data 3 Commencement Date 28/5/10 4 Borrowed Amount 600000 5 Interest Rate (i_1) 2.5288% 6 Loan term (years) 30 7 Interest Rate (i_12) 2.50% 8 No. Periods per Year 12 9 Repayment AUD 2,370.71 per month 10 11 Date Today 28/5/20 12 Number of Periods Remaining 240 month 13 0/S Loan Value before early repay AUD 447,387.86 14 Early Repayment AUD 20,000.00 15 0/S Loan value after early repayme AUD 427,387.86 16 Reduced monthly repayment AUD 2,264.73 Figure 1: Excerpt of spreadsheet for parts a, b, c and d of question 3. QUESTION 3 [15 marks] Sacai took out a 30-year home loan on 28 May 2010. He borrowed $600,000 at a rate ji = 2.5288%. Repayments were made monthly in arrears. a. [2 marks] From Sacai's perspective, draw a carefully labelled cash flow diagram that describes the above information. Ten years later, Sacai has financial difficulties in maintaining the repayments due to the corona crisis. Fortunately, he is able to withdraw funds from his superannuation account to reduce his financial stress. Sacai knows you have just completed the unit ACST2001 and asks you to help with the spreadsheet modelling. He has two ideas. First idea: use the proceeds from his superannuation account to repay part of his debt and reduce his monthly repayment amount. Refer to figure 1 in answering parts b, c and d below. b. [1 mark] Provide an Excel formula to calculate the value of cell B7. c. [2 marks] Provide Excel formulto calculate the value of cells B9 and B16. d. [2 marks] Provide an Excel formula to calculate the value of cell B13. Second Idea: Use the proceeds from his superannuation account to repay part of his debt and defer his remaining repayments by half a year. Note: the monthly repayment amount is unchanged. Refer to figure 2 in answering part e below. B D F Input Output 28/5/10 600000 2.5288% 30 2.50% 12 2,370.73 per month A 1 Second Idea 2 Loan Data 3 Commencement Date 4 Borrowed Amount 5 Interest Rate (j_1) 6 Loan term (years) 7 Interest Rate (12) 8 No. Periods per Year 9 Repayment AUD 10 11 Date Today Nurnber of Periods Remaining 12 (Month) 13 0/S Loan Value before early repayrr AUD 14 Early Repayment AUD 0/Loan value after early AUD 15 repayment and deferral period Repayment under the New 0/S AUD 16 Loan Amount 17 New Loan Term (Month) 28/5/20 240 447,388.65 20,000.00 432,758.91 2,370.73 229.93 From Goal Seek Figure 2: Excerpt of spreadsheet for part e of question 3. e. [3 marks] Assume cell B15 is the new loan outstanding amount after deferring the repayments. Describe the steps to generate the result in cell B17 by using Goal Seek. (Note: Cell B16 is the only cell referencing the cell B17.) Upon negotiation, the bank agreed on Sacai's second idea but imposed a penalty term: if his early repayment is over $10 000, then the interest rate applied during the 6 month deferral period will be based on the original rate plus 15 basis points. The interest rate applied to the balance outstanding af- ter the deferral period is the same as the original rate (that is, ji = 2.5288%). Refer to figure 3 in answering parts f and g below. f. [2 marks] Develop an Excel formula to calculate the value of cell B15. g. [3 marks] Briefly describe the steps of how to include a plot which shows both the amount of the early repayment and the new loan outstanding amount after the deferral period. D E F G Input Output A B Second Idea (with Bank's penalty) 2 Loan Data 3 Commencement Date 28/5/10 4 Borrowed Amount 600000 Interest Rate (i_1) 2.5288% 6 Loan term (years) 30 7 Interest Rate (i_12) 2.50% 8 No. Periods per Year 12 9 Repayment AUD 2,370.71 per month 10 11 Date Today 28/5/20 12 Number of Periods Remaining 240 month 13 0/5 Loan Value before early repayment AUD 447,387.86 14 Early Repayment AUD 12,000.00 15 0/5 Loan value after early repayment and deferral PAUD 441,188.59 16 Penalty data Early Repayment Range Interest Rate (i_12) Penalty Below $10000 O Basis Point Otherwise 15 Basis Point 17 Figure 3: Excerpt of spreadsheet for parts f and g of question 3. D E G F Input Output First Idea 2 Loan Data 3 Commencement Date 28/5/10 4 Borrowed Amount 600000 5 Interest Rate (i_1) 2.5288% 6 Loan term (years) 30 7 Interest Rate (i_12) 2.50% 8 No. Periods per Year 12 9 Repayment AUD 2,370.71 per month 10 11 Date Today 28/5/20 12 Number of Periods Remaining 240 month 13 0/S Loan Value before early repay AUD 447,387.86 14 Early Repayment AUD 20,000.00 15 0/S Loan value after early repayme AUD 427,387.86 16 Reduced monthly repayment AUD 2,264.73 Figure 1: Excerpt of spreadsheet for parts a, b, c and d of question 3. QUESTION 3 [15 marks] Sacai took out a 30-year home loan on 28 May 2010. He borrowed $600,000 at a rate ji = 2.5288%. Repayments were made monthly in arrears. a. [2 marks] From Sacai's perspective, draw a carefully labelled cash flow diagram that describes the above information. Ten years later, Sacai has financial difficulties in maintaining the repayments due to the corona crisis. Fortunately, he is able to withdraw funds from his superannuation account to reduce his financial stress. Sacai knows you have just completed the unit ACST2001 and asks you to help with the spreadsheet modelling. He has two ideas. First idea: use the proceeds from his superannuation account to repay part of his debt and reduce his monthly repayment amount. Refer to figure 1 in answering parts b, c and d below. b. [1 mark] Provide an Excel formula to calculate the value of cell B7. c. [2 marks] Provide Excel formulto calculate the value of cells B9 and B16. d. [2 marks] Provide an Excel formula to calculate the value of cell B13. Second Idea: Use the proceeds from his superannuation account to repay part of his debt and defer his remaining repayments by half a year. Note: the monthly repayment amount is unchanged. Refer to figure 2 in answering part e below. B D F Input Output 28/5/10 600000 2.5288% 30 2.50% 12 2,370.73 per month A 1 Second Idea 2 Loan Data 3 Commencement Date 4 Borrowed Amount 5 Interest Rate (j_1) 6 Loan term (years) 7 Interest Rate (12) 8 No. Periods per Year 9 Repayment AUD 10 11 Date Today Nurnber of Periods Remaining 12 (Month) 13 0/S Loan Value before early repayrr AUD 14 Early Repayment AUD 0/Loan value after early AUD 15 repayment and deferral period Repayment under the New 0/S AUD 16 Loan Amount 17 New Loan Term (Month) 28/5/20 240 447,388.65 20,000.00 432,758.91 2,370.73 229.93 From Goal Seek Figure 2: Excerpt of spreadsheet for part e of question 3. e. [3 marks] Assume cell B15 is the new loan outstanding amount after deferring the repayments. Describe the steps to generate the result in cell B17 by using Goal Seek. (Note: Cell B16 is the only cell referencing the cell B17.) Upon negotiation, the bank agreed on Sacai's second idea but imposed a penalty term: if his early repayment is over $10 000, then the interest rate applied during the 6 month deferral period will be based on the original rate plus 15 basis points. The interest rate applied to the balance outstanding af- ter the deferral period is the same as the original rate (that is, ji = 2.5288%). Refer to figure 3 in answering parts f and g below. f. [2 marks] Develop an Excel formula to calculate the value of cell B15. g. [3 marks] Briefly describe the steps of how to include a plot which shows both the amount of the early repayment and the new loan outstanding amount after the deferral period. D E F G Input Output A B Second Idea (with Bank's penalty) 2 Loan Data 3 Commencement Date 28/5/10 4 Borrowed Amount 600000 Interest Rate (i_1) 2.5288% 6 Loan term (years) 30 7 Interest Rate (i_12) 2.50% 8 No. Periods per Year 12 9 Repayment AUD 2,370.71 per month 10 11 Date Today 28/5/20 12 Number of Periods Remaining 240 month 13 0/5 Loan Value before early repayment AUD 447,387.86 14 Early Repayment AUD 12,000.00 15 0/5 Loan value after early repayment and deferral PAUD 441,188.59 16 Penalty data Early Repayment Range Interest Rate (i_12) Penalty Below $10000 O Basis Point Otherwise 15 Basis Point 17 Figure 3: Excerpt of spreadsheet for parts f and g of question 3