QUESTION 3 [14 marks] Bill has bought a new home in Canberra. He borrowed $600 000 at a 12 rate given as 150 basis points over the Australian 10-year government bond yield for 2018. The loan is to be repaid in annual instalments over a thirty year period. The first instalment was due on 19 March 2020. Penny has prepared a spreadsheet that models the financial products discussed in this question. Excerpts from her spreadsheet appear in figures 1-3 below, where these are drawn from three sheets in her spreadsheet titled Part A', 'Part B' and 'Part C', respectively. ive an Excel cell formula for Penny's cell B7 in figure 1. b. [2 marks] In Penny's spreadsheet shown in figure 1, she calculated Bill's annual repayment using Excel's Goal Seek function. Explain how she would have done this. c. [2 marks] Develop Excel cell formul that replicate Penny's calcula- tions in cells B13:014 of figure 1 (six in all). Like Bill, Scott has bought a house in Canberra, borrowing the same amount, and on the the same terms. Scott's bank, however, offers an 'interest offset? account facility with the loan. Like Bill, Scott's first payment is on 19 March 2020. On the day Scott takes the loan of $600 000 out (19 March 2019), Malcolm gives Scott $100 000. Scott immediately puts the money into his interest offset account. This account also earns the same rate compound interest charged by the bank. Over the term of the loan Scott does not put any more money into the interest offset account. The interest offset account pays interest annually, and its first payment will be on 19 March 2020. Scott's interest offset account pays its interest payments to Scott's loan, and Penny's first attempt to model his loan, in which she modelled Scott's payments as consisting of his loan repayment plus the interest credit from his offset account appears in figure 2 below. d. [3 marks] Penny's first attempt to model Scott's loan in Excel appears in figure 2 below. Develop Excel cell formul that replicate Penny's calculations in cells B13:E14 of figure 2 (eight in all). Alternatively, rather than viewing the Scott's payments as consisting of his loan repayment plus the interest credit from his offset account, we can view his capital outstanding as being reduced by the amount of his mortgage offset account, with interest charges being calculated on this reduced balance-to which only his loan repayment amount (the same as Bill's) is applied. e. [3 marks] Penny's second attempt to model Scott's loan in Excel ap- pears in figure 3 below. Develop Excel cell formul that replicate Penny's calculations in cells B13:F14 of figure 3 (ten in all). f. [3 marks] If Penny wanted to plot Bill and Scott's outstanding loan balance at the beginning of each year of the loan against each other in her Excel spreadsheet, how would she proceed? A Inputs Outputs Data 2 issue date of loan 3 Term 4 Australian 10-year government bond yield for 2018 5 Repayment frequency 6 Loan amount 7 Effective annual interest rate 8 Annual repayment 19/3/19 30 years 2.32% From FactSet Annual 600000 3.86% $34,095.97 From GoalSeek: click 0.89 Loan o/s BoY: immediately a er payment then due Figure 1: Excerpt of spreadsheet for part a. of question 3. Date Loan o/s BoY Interest paid Principal repaid 19/3/19 $600,000.00 $23,138.89 $10,957.08 19/3/20 $589,042.92 $22,716.33 $11,379.64 19/3/21 $577,663.28 $22,277.47 $11,818.49 19/3/22 $565,844.78 $21,821.70 $12,274.27 19/3/23 $553,570.51 $21,348.34 $12,747.63 19/3/24 $540,822.88 $20,856.73 $13,239.24 19/3/25 $527,583.65 $20,346.16 $13,749.81 19/3/26 $513,833.84 $19,815.90 $14,280.06 19/3/27 $499,553.78 $19,265.20 A B E F G H I 19/3/42 19/3/43 19/3/44 19/3/45 19/3/46 19/3/47 19/3/48 19/3/49 $205,735.67 $179,573.86 $152,403.13 $124,184.56 $94,877.74 $64,440.71 $32,829.89 $0.00 $7,934.16 $6,925.23 $5,877.40 $4,789.15 $3,658.94 $2,485.14 $1,266.08 $0.00 I $26,161.81 $27,170.74 $28,218.57 $29,306.81 $30,437.03 $31,610.82 $32,829.89 $34,095.97 BC E | F | G | H | I Inputs Outputs Loan repayment date Reduced final payment Number of annual payments 19/3/44 28961.8303 | 2 issue date of loan Term 4 Australian 10-year government bond yield for 2018 Repayment frequency Loan amount Effective annual interest rate Annual repayment Interest offset a/c balance 19/3/19 30 years 0.0232 Annual 600000 0.03856481 34095.9687 100000 Date Figure 2: Excerpt of spreadsheet for part b. of question 3. Loan o/s Boy Interest pa Repayment+IOA 19/3/19 6 00000 23138.89 19/3/20 585 186.436 22567.6 19/3/21 569801.59 21974.29 19/3/22 553823.431 21358.1 19/3/23 537229.077 20718.14 19/3/24 519994.764 20053.5 19/3/25 502095.814 19363.23 19/3/26 483506.594 18646.34 19/3/27 464 200.484 17901.8 19/3/28 444149.838 17128.55 19/3/29 423325.942 16325.48 Principal rep Repaid? |Reduced final payment (not including the int 37952.44967 148 13.5637 37952.44967 15384.8459 37952.44967 15978.1596 37952.44967 16594.3543 37952.44967 17234.3124 37952.44967 17898.9504 37952.44967 18589.22 37952.44967 19306.1097 37952.44967 20050.6462 37952.44967 20823.8956 37952.44967 21626.9651 | G | H | | 28961.83 19/3/41 101025.867 3896.043 19/3/42 66969.461 2582.665 19/3/43 31599.6758 1218.635 19/3/44 -5134.1383 -197.997 19/3/45 -43284.585 -1669.26 19/3/46 -82906.297 -3197.27 19/3/47 -124056.01 -4784.2 19/3/48 -166792.66 -6432.33 19/3/49 -211177.43 -8144.02 F 37952.44967 34056.4063 37952.44967 35369.7851 37952.44967 36733.8142 Repaid 37952.44967 38150.4467 Repaid 37952.44967 39621.7115 Repaid 37952.44967 41149.7152 Repaid 37952.44967 42736.6462 Repaid 37952.44967 44384.7768 Repaid 37952.44967 46096.4673 B C D F G IH Inputs Outputs Loan repayment date Reduced final payment Number of annual payments 19/3/44 28961.8303 25 1 Data 2 issue date of loan 3 Term 4 Australian 10-year government bond yield for 2018 5 Repayment frequency Loan amount Effective annual interest rate Annual repayment 9 Interest offset a/c balance 19/3/19 30 years 0.0232 Annual 600000 0.03856481 34095.9687 100000 6 Interest is charged here based on the "Nominal Loan o/s" below 12 Date Reduced final payment (not inclu Figure 3: Excerpt of spreadsheet for part c.of question 3. Loan o/s Boy Nominal Loa Interest pa Repayment+IOA 19/3/19 6 00000 500000 19282.4 19/3/20 585 186.436 485186.436 18711.12 19/3/21 569801.59 469801.59 18117.81 19/3/22 553823.431 453823.431 17501.61 19/3/23 537229.077 4372 29.077 16861.66 19/3/24 519994.764 419994.764 16197.02 19/3/25 502095.814 402095.814 15506.75 19/3/26 483506.594 383506.594 14789.86 19/3/27 464 200.484 364200.484 14045.32 Principal rep Repaid? 34095.96867 148 13.5637 34095.96867 15384.8459 34095.96867 15978. 1596 34095.96867 16594.3543 34095.96867 17234.3124 34095.96867 17898.9504 34095.96867 18589.22 34095.96 867 19306. 1097 34095.96867 20050.6462 28961.83 B 19/3/41 101025.867 1025.86725 39.56238 19/3/42 66969.461 -33030.539 -1273.82 19/3/43 31599.6758 -68400.324 -2637.85 19/3/44 -5134.1383 -105134.14 -4054.48 19/3/45 -43284.585 -143284.59-5525.74 19/3/46 -82906.297 -182906.3 -7053.75 19/3/47 -124056.01 -224056.01 -8640.68 19/3/48 -166792.66 -266792.66 -10288.8 19/3/49 -211177.43 -311177.43 -12000.5 34095.96867 34056.4063 34095.96867 35369.7851 34095.96867 36733.8142 Repaid 34095.96867 38150.4467 Repaid 34095.96867 39621.7115 Repaid 34095.96867 41149.7152 Repaid 34095.96867 42736.6462 Repaid 34095.96867 44384.7768 Repaid 34095.96867 46096.4673 QUESTION 3 [14 marks] Bill has bought a new home in Canberra. He borrowed $600 000 at a 12 rate given as 150 basis points over the Australian 10-year government bond yield for 2018. The loan is to be repaid in annual instalments over a thirty year period. The first instalment was due on 19 March 2020. Penny has prepared a spreadsheet that models the financial products discussed in this question. Excerpts from her spreadsheet appear in figures 1-3 below, where these are drawn from three sheets in her spreadsheet titled Part A', 'Part B' and 'Part C', respectively. ive an Excel cell formula for Penny's cell B7 in figure 1. b. [2 marks] In Penny's spreadsheet shown in figure 1, she calculated Bill's annual repayment using Excel's Goal Seek function. Explain how she would have done this. c. [2 marks] Develop Excel cell formul that replicate Penny's calcula- tions in cells B13:014 of figure 1 (six in all). Like Bill, Scott has bought a house in Canberra, borrowing the same amount, and on the the same terms. Scott's bank, however, offers an 'interest offset? account facility with the loan. Like Bill, Scott's first payment is on 19 March 2020. On the day Scott takes the loan of $600 000 out (19 March 2019), Malcolm gives Scott $100 000. Scott immediately puts the money into his interest offset account. This account also earns the same rate compound interest charged by the bank. Over the term of the loan Scott does not put any more money into the interest offset account. The interest offset account pays interest annually, and its first payment will be on 19 March 2020. Scott's interest offset account pays its interest payments to Scott's loan, and Penny's first attempt to model his loan, in which she modelled Scott's payments as consisting of his loan repayment plus the interest credit from his offset account appears in figure 2 below. d. [3 marks] Penny's first attempt to model Scott's loan in Excel appears in figure 2 below. Develop Excel cell formul that replicate Penny's calculations in cells B13:E14 of figure 2 (eight in all). Alternatively, rather than viewing the Scott's payments as consisting of his loan repayment plus the interest credit from his offset account, we can view his capital outstanding as being reduced by the amount of his mortgage offset account, with interest charges being calculated on this reduced balance-to which only his loan repayment amount (the same as Bill's) is applied. e. [3 marks] Penny's second attempt to model Scott's loan in Excel ap- pears in figure 3 below. Develop Excel cell formul that replicate Penny's calculations in cells B13:F14 of figure 3 (ten in all). f. [3 marks] If Penny wanted to plot Bill and Scott's outstanding loan balance at the beginning of each year of the loan against each other in her Excel spreadsheet, how would she proceed? A Inputs Outputs Data 2 issue date of loan 3 Term 4 Australian 10-year government bond yield for 2018 5 Repayment frequency 6 Loan amount 7 Effective annual interest rate 8 Annual repayment 19/3/19 30 years 2.32% From FactSet Annual 600000 3.86% $34,095.97 From GoalSeek: click 0.89 Loan o/s BoY: immediately a er payment then due Figure 1: Excerpt of spreadsheet for part a. of question 3. Date Loan o/s BoY Interest paid Principal repaid 19/3/19 $600,000.00 $23,138.89 $10,957.08 19/3/20 $589,042.92 $22,716.33 $11,379.64 19/3/21 $577,663.28 $22,277.47 $11,818.49 19/3/22 $565,844.78 $21,821.70 $12,274.27 19/3/23 $553,570.51 $21,348.34 $12,747.63 19/3/24 $540,822.88 $20,856.73 $13,239.24 19/3/25 $527,583.65 $20,346.16 $13,749.81 19/3/26 $513,833.84 $19,815.90 $14,280.06 19/3/27 $499,553.78 $19,265.20 A B E F G H I 19/3/42 19/3/43 19/3/44 19/3/45 19/3/46 19/3/47 19/3/48 19/3/49 $205,735.67 $179,573.86 $152,403.13 $124,184.56 $94,877.74 $64,440.71 $32,829.89 $0.00 $7,934.16 $6,925.23 $5,877.40 $4,789.15 $3,658.94 $2,485.14 $1,266.08 $0.00 I $26,161.81 $27,170.74 $28,218.57 $29,306.81 $30,437.03 $31,610.82 $32,829.89 $34,095.97 BC E | F | G | H | I Inputs Outputs Loan repayment date Reduced final payment Number of annual payments 19/3/44 28961.8303 | 2 issue date of loan Term 4 Australian 10-year government bond yield for 2018 Repayment frequency Loan amount Effective annual interest rate Annual repayment Interest offset a/c balance 19/3/19 30 years 0.0232 Annual 600000 0.03856481 34095.9687 100000 Date Figure 2: Excerpt of spreadsheet for part b. of question 3. Loan o/s Boy Interest pa Repayment+IOA 19/3/19 6 00000 23138.89 19/3/20 585 186.436 22567.6 19/3/21 569801.59 21974.29 19/3/22 553823.431 21358.1 19/3/23 537229.077 20718.14 19/3/24 519994.764 20053.5 19/3/25 502095.814 19363.23 19/3/26 483506.594 18646.34 19/3/27 464 200.484 17901.8 19/3/28 444149.838 17128.55 19/3/29 423325.942 16325.48 Principal rep Repaid? |Reduced final payment (not including the int 37952.44967 148 13.5637 37952.44967 15384.8459 37952.44967 15978.1596 37952.44967 16594.3543 37952.44967 17234.3124 37952.44967 17898.9504 37952.44967 18589.22 37952.44967 19306.1097 37952.44967 20050.6462 37952.44967 20823.8956 37952.44967 21626.9651 | G | H | | 28961.83 19/3/41 101025.867 3896.043 19/3/42 66969.461 2582.665 19/3/43 31599.6758 1218.635 19/3/44 -5134.1383 -197.997 19/3/45 -43284.585 -1669.26 19/3/46 -82906.297 -3197.27 19/3/47 -124056.01 -4784.2 19/3/48 -166792.66 -6432.33 19/3/49 -211177.43 -8144.02 F 37952.44967 34056.4063 37952.44967 35369.7851 37952.44967 36733.8142 Repaid 37952.44967 38150.4467 Repaid 37952.44967 39621.7115 Repaid 37952.44967 41149.7152 Repaid 37952.44967 42736.6462 Repaid 37952.44967 44384.7768 Repaid 37952.44967 46096.4673 B C D F G IH Inputs Outputs Loan repayment date Reduced final payment Number of annual payments 19/3/44 28961.8303 25 1 Data 2 issue date of loan 3 Term 4 Australian 10-year government bond yield for 2018 5 Repayment frequency Loan amount Effective annual interest rate Annual repayment 9 Interest offset a/c balance 19/3/19 30 years 0.0232 Annual 600000 0.03856481 34095.9687 100000 6 Interest is charged here based on the "Nominal Loan o/s" below 12 Date Reduced final payment (not inclu Figure 3: Excerpt of spreadsheet for part c.of question 3. Loan o/s Boy Nominal Loa Interest pa Repayment+IOA 19/3/19 6 00000 500000 19282.4 19/3/20 585 186.436 485186.436 18711.12 19/3/21 569801.59 469801.59 18117.81 19/3/22 553823.431 453823.431 17501.61 19/3/23 537229.077 4372 29.077 16861.66 19/3/24 519994.764 419994.764 16197.02 19/3/25 502095.814 402095.814 15506.75 19/3/26 483506.594 383506.594 14789.86 19/3/27 464 200.484 364200.484 14045.32 Principal rep Repaid? 34095.96867 148 13.5637 34095.96867 15384.8459 34095.96867 15978. 1596 34095.96867 16594.3543 34095.96867 17234.3124 34095.96867 17898.9504 34095.96867 18589.22 34095.96 867 19306. 1097 34095.96867 20050.6462 28961.83 B 19/3/41 101025.867 1025.86725 39.56238 19/3/42 66969.461 -33030.539 -1273.82 19/3/43 31599.6758 -68400.324 -2637.85 19/3/44 -5134.1383 -105134.14 -4054.48 19/3/45 -43284.585 -143284.59-5525.74 19/3/46 -82906.297 -182906.3 -7053.75 19/3/47 -124056.01 -224056.01 -8640.68 19/3/48 -166792.66 -266792.66 -10288.8 19/3/49 -211177.43 -311177.43 -12000.5 34095.96867 34056.4063 34095.96867 35369.7851 34095.96867 36733.8142 Repaid 34095.96867 38150.4467 Repaid 34095.96867 39621.7115 Repaid 34095.96867 41149.7152 Repaid 34095.96867 42736.6462 Repaid 34095.96867 44384.7768 Repaid 34095.96867 46096.4673