The objective of this assignment is to confirm some of the results presentedto you in week 07, on slides 40–42. We proceed as follows.In a spreadsheet, create three separate sheets, labelled ‘Part a’, ‘Part b’,and ‘Part c’. Then answer the following questions.1. [3 marks] On your sheet labelled ‘Part a’ do the following.Firstly, you are … Continue reading “ACST2001 Financial Modelling Group | My Assignment Tutor”
The objective of this assignment is to confirm some of the results presentedto you in week 07, on slides 40–42. We proceed as follows.In a spreadsheet, create three separate sheets, labelled ‘Part a’, ‘Part b’,and ‘Part c’. Then answer the following questions.1. [3 marks] On your sheet labelled ‘Part a’ do the following.Firstly, you are required to price a number of bonds (30 in all). Westart by considering a 2% Treasury bond, maturing in 7 years. Pricethis bond at yields to maturity of• 47 basis points under CY20.• 153 basis points over CY20.• 353 basis points over CY20.…• 1 753 basis points over CY20.where CY20 is the Australian 10-year government bond yield for calendar year 2020 (i.e., the 10-year government bond yield on 31 December2020).∗Next, consider a 10% Treasury bond, maturing in 7 years. Also pricethis bond at yields to maturity of• 47 basis points under CY20.• 153 basis points over CY20.• 353 basis points over CY20.…• 1 753 basis points over CY20.Finally, consider a 20% Treasury bond, maturing in 7 years. Also pricethis bond at yields to maturity of• 47 basis points under CY20.• 153 basis points over CY20.• 353 basis points over CY20.∗You will need to use FactSet to find this value.1ACST2001 Financial Modelling Group spreadsheet project S1 2021 [10 marks] Version 1.01c 2021 Department of Actuarial Studies and Business Analytics, Macquarie University (All rights reserved)…• 1 753 basis points over CY20.2. [3 marks] On your sheet labelled ‘Part b’ do the following.Produce one graph of the prices (against yields) you calculated abovefor the three different bonds—that is, all three bond results should beplotted in the one graph. Price should be the vertical axis of yourgraph. Label the three curves you have plotted as ‘2% coupon’, ‘10%coupon’, and ‘20% coupon’.3. [4 marks] On your sheet labelled ‘Part c’ do the following.Consider the move in yield from i0 = 7% to i1 = 5%. By adjustingour approximate formula for modified duration, calculate the (approximate) duration of each of the three Treasury bonds maturing in 7 yearswe worked with above (one with 2% coupon, one with 10% coupon, andone with 20% coupon).Next, consider the move in yield from i0 = 15% to i1 = 17%. Usingyour formula above, calculate the approximate duration of each of thethree Treasury bonds maturing in 7 years we worked with above (onewith 2% coupon, one with 10% coupon, and one with 20% coupon).End of taskThe submission deadline for the spreadsheet project is 9.00 a.m. on 4 May2021. You will need to submit your solutions (in one functional .xlsx or.xls file) to the link on iLearn prior to this time. Your spreadsheet shouldbe clearly labelled and easy to understand. Make sure you identify what the“inputs” and “outputs” are. Include necessary information (e.g., title, axistitles, etc.) in your plot. Document and describe the steps in the developmentof each tab of your spreadsheet.Please note that uploading a file can take up to 15 minutes. You need tosubmit your file at least 20 minutes before the deadline to ensure a successfulsubmission.2ACST2001 Financial Modelling Group spreadsheet project S1 2021 [10 marks] Version 1.01c 2021 Department of Actuarial Studies and Business Analytics, Macquarie University (All rights reserved)
