figure 1:
Figure for (f) and (g):
D E F G Input Output B 1 First Idea 2 Loan Data 3 Commencement Date 28/5/10 4 Loan term (years) 30 5 Borrowed Amount 600000 6 Interest Rate (i_11 2.5288% 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 repayr AUD 447,387.86 14 Early Repayment AUD 20,000.00 15 O/S Loan value after early repayme AUD 427,387.86 16 Reduced monthly repayment AUD 2,264.73 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. B C D E F Input Output 28/5/10 600000 2.5288% 30 2.50% 12 2,370.73 per month 1 Second Idea 2 Loan Data 3 Commencement Date 4 Borrowed Amount 5 Interest Rate (1) 6 Loan term (years) 7 Interest Rate (12) 8 No. Periods per Year 9 Repayment AUD 10 11 Date Today Number of Periods Remaining 12 (Month) 13 0/S Loan Value before early repayer AUD 14 Early Repayment AUD 15 New Loan Term Month) 0/5 Loan value after early AUD 16 repayment and deferral period Repayment under the New O/ AUD 17 Loan Amount 28/5/20 240 447,388.65 20,000.00 229.93 From Goal Seek 432,758.91 2,370.73 Figure 2: Excerpt of spreadsheet for part e of question 3. e. [3 marks] Assume cell B16 is the new loan outstanding amount after deferring the repayments. Describe the steps to generate the result in cell B15 by using Goal Seek. (Note: Cell B17 is the only cell referencing the cell B15.) Upon negotiation, the bank agreed on Sachin'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 G Input Output B 1 Second Idea (with Bank's penalty) 2 Loan Data 3 Commencement Date 28/5/10 4 Borrowed Amount 600000 5 Loan term (years) 30 6 Interest Rate (11) 2.5288% 7 No. Periods per Year 12 8 Interest Rate (i_12) 2.50% 9 Repayment 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 pe AUD 441,188.59 16 AUD Penalty data Early Repayment Rani Interest Rate (i_12) Penalty Greater than $10000 15 Basis Point Otherwise O Basis Point D E F G Input Output B 1 First Idea 2 Loan Data 3 Commencement Date 28/5/10 4 Loan term (years) 30 5 Borrowed Amount 600000 6 Interest Rate (i_11 2.5288% 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 repayr AUD 447,387.86 14 Early Repayment AUD 20,000.00 15 O/S Loan value after early repayme AUD 427,387.86 16 Reduced monthly repayment AUD 2,264.73 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. B C D E F Input Output 28/5/10 600000 2.5288% 30 2.50% 12 2,370.73 per month 1 Second Idea 2 Loan Data 3 Commencement Date 4 Borrowed Amount 5 Interest Rate (1) 6 Loan term (years) 7 Interest Rate (12) 8 No. Periods per Year 9 Repayment AUD 10 11 Date Today Number of Periods Remaining 12 (Month) 13 0/S Loan Value before early repayer AUD 14 Early Repayment AUD 15 New Loan Term Month) 0/5 Loan value after early AUD 16 repayment and deferral period Repayment under the New O/ AUD 17 Loan Amount 28/5/20 240 447,388.65 20,000.00 229.93 From Goal Seek 432,758.91 2,370.73 Figure 2: Excerpt of spreadsheet for part e of question 3. e. [3 marks] Assume cell B16 is the new loan outstanding amount after deferring the repayments. Describe the steps to generate the result in cell B15 by using Goal Seek. (Note: Cell B17 is the only cell referencing the cell B15.) Upon negotiation, the bank agreed on Sachin'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 G Input Output B 1 Second Idea (with Bank's penalty) 2 Loan Data 3 Commencement Date 28/5/10 4 Borrowed Amount 600000 5 Loan term (years) 30 6 Interest Rate (11) 2.5288% 7 No. Periods per Year 12 8 Interest Rate (i_12) 2.50% 9 Repayment 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 pe AUD 441,188.59 16 AUD Penalty data Early Repayment Rani Interest Rate (i_12) Penalty Greater than $10000 15 Basis Point Otherwise O Basis Point
