Hii please help, if you could answer in excel and write down all formulas used it would be really helpful :)
The objective of this assignment is to confirm some of the results presented to 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). We start by considering a 2% Treasury bond, maturing in 7 years. Price this bond at yields to maturity of 47 basis points under CY20- 153 basis points over CY20- 353 basis points over CY20. : 1753 basis points over CY20- where CY20 is the Australian 10-year government bond yield for calen- dar year 2020 (i.e., the 10-year government bond yield on 31 December 2020).* Next, consider a 10% Treasury bond, maturing in 7 years. Also price this bond at yields to maturity of 47 basis points under CY20- 153 basis points over CY20. 353 basis points over CY20- : 1753 basis points over CY20. Finally, consider a 20% Treasury bond, maturing in 7 years. Also price this bond at yields to maturity of 47 basis points under CY20. 153 basis points over CY20- 353 basis points over C'Y20- * You will need to use FactSet to find this value. : 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 above for the three different bondsthat is, all three bond results should be plotted in the one graph. Price should be the vertical axis of your graph. 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 io = 7% to 11 = 5%. By adjusting our approximate formula for modified duration, calculate the approxi- mate) duration of each of the three Treasury bonds maturing in 7 years we worked with above (one with 2% coupon, one with 10% coupon, and one with 20% coupon). Next, consider the move in yield from 10 = 15% to 11 = 17%. Using your formula above, calculate the approximate duration of each of the three Treasury bonds maturing in 7 years we worked with above (one with 2% coupon, one with 10% coupon, and one with 20% coupon). End of task The objective of this assignment is to confirm some of the results presented to 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). We start by considering a 2% Treasury bond, maturing in 7 years. Price this bond at yields to maturity of 47 basis points under CY20- 153 basis points over CY20- 353 basis points over CY20. : 1753 basis points over CY20- where CY20 is the Australian 10-year government bond yield for calen- dar year 2020 (i.e., the 10-year government bond yield on 31 December 2020).* Next, consider a 10% Treasury bond, maturing in 7 years. Also price this bond at yields to maturity of 47 basis points under CY20- 153 basis points over CY20. 353 basis points over CY20- : 1753 basis points over CY20. Finally, consider a 20% Treasury bond, maturing in 7 years. Also price this bond at yields to maturity of 47 basis points under CY20. 153 basis points over CY20- 353 basis points over C'Y20- * You will need to use FactSet to find this value. : 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 above for the three different bondsthat is, all three bond results should be plotted in the one graph. Price should be the vertical axis of your graph. 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 io = 7% to 11 = 5%. By adjusting our approximate formula for modified duration, calculate the approxi- mate) duration of each of the three Treasury bonds maturing in 7 years we worked with above (one with 2% coupon, one with 10% coupon, and one with 20% coupon). Next, consider the move in yield from 10 = 15% to 11 = 17%. Using your formula above, calculate the approximate duration of each of the three Treasury bonds maturing in 7 years we worked with above (one with 2% coupon, one with 10% coupon, and one with 20% coupon). End of task
