assuming face value =100

The spreadsheet provided shows a list of bonds issued by the Australian government and their relevant characteristics as of the 27th of March 2020. Based on the information provided, complete the following calculations in excel. A. Calculate the yield of each bond as of the 28th of March (5 Marks) B. What is the weighted average yield of this portfolio of bonds? (15 Marks) C. If on the 30th of April all bonds have a yield that is 1% higher, what would the value of the portfolio be? What about if the yield was 1% lower? Graph your results. (10 Marks) D. Using your results from part C, rank the bonds in order of most sensitive to changes in the yield to least sensitive. Explain what characteristics lead to a presence of absence of sensitivity and relate this back to your results. (5 Marks) E. Do you think that the yield on Australian government debt is likely to increase or decrease over the next 9 month? Why? (5 Marks) Bond Number Coupon Im L Maturity date 4.50% 15/04/2020 1.75% 21/11/2020 5.75% 15/05/2021 2.00% 21/12/2021 5.75% 15/07/2022 2.25% 21/11/2022 5.50% 21/04/2023 2.75% 21/04/2024 Current Price Payment Frequer Next Payment Date Number of Bonds on Issue 102.23 2 15/04/2020 800000 101.853 2 21/05/2020 250000 108.9 2 15/05/2020 400000 103.71 2 22/06/2020 350000 113.94 2 15/07/2020 100000 106.237 2 21/05/2020 478000 118.58 2 21/04/2020 450000 110.91 2 21/04/2020 700000 N001 The spreadsheet provided shows a list of bonds issued by the Australian government and their relevant characteristics as of the 27th of March 2020. Based on the information provided, complete the following calculations in excel. A. Calculate the yield of each bond as of the 28th of March (5 Marks) B. What is the weighted average yield of this portfolio of bonds? (15 Marks) C. If on the 30th of April all bonds have a yield that is 1% higher, what would the value of the portfolio be? What about if the yield was 1% lower? Graph your results. (10 Marks) D. Using your results from part C, rank the bonds in order of most sensitive to changes in the yield to least sensitive. Explain what characteristics lead to a presence of absence of sensitivity and relate this back to your results. (5 Marks) E. Do you think that the yield on Australian government debt is likely to increase or decrease over the next 9 month? Why? (5 Marks) Bond Number Coupon Im L Maturity date 4.50% 15/04/2020 1.75% 21/11/2020 5.75% 15/05/2021 2.00% 21/12/2021 5.75% 15/07/2022 2.25% 21/11/2022 5.50% 21/04/2023 2.75% 21/04/2024 Current Price Payment Frequer Next Payment Date Number of Bonds on Issue 102.23 2 15/04/2020 800000 101.853 2 21/05/2020 250000 108.9 2 15/05/2020 400000 103.71 2 22/06/2020 350000 113.94 2 15/07/2020 100000 106.237 2 21/05/2020 478000 118.58 2 21/04/2020 450000 110.91 2 21/04/2020 700000 N001