ACST2001 S2 2024 Spreadsheet

ACST2001 S2 2024 Spreadsheet Project Task

In a spreadsheet, create four separate sheets, labelled ‘Part a’, ‘Part b’, ‘Part c’ and ‘Partd’. Part a to c confirm some of the results presented to you in week 7, on slide 40-42.Then answer the following questions

1. On your sheet labelled ‘Part a’ do the following.Assume each bond has a face value of $100.Firstly, you are required to price a number of bonds (30 in all). We start byconsidering a 4% Treasury bond, maturing in 8 years. Price this bond (in 3decimal places) at yields to maturity of 𝑗2 = 4%, 6%, 8%, …, 20% and 22% p.a.Next consider a 10% Treasury bond, maturing in 8 years. Also price this bond (in3 decimal places) at yields to maturity of 𝑗2 = 4%, 6%, 8%, …, 20% and 22% p.a.Finally, consider a 22% Treasury bond, maturing in 8 years. Also price this bond(in 3 decimal places) at yields to maturity of 𝑗2 = 4%, 6%, 8%, …, 20% and 22%p.a.The table below is the recommended format for the 30 bond prices, one value isgiven as a check value.Yield to Maturity22%b. On your sheet labelled ‘Part b’ do the following.Produce one graph of the prices (vertical axis) against yields (horizontal axis) youcalculated above for the three different bonds in part a – that is, all three bondresults should be plotted in the one graph. Price should be the vertical axis ofyour graph. Label the three curves you have plotted as ‘4% coupon’, ‘10%coupon’, and ‘22% coupon’.

1. On your sheet labelled ‘Part c’ do the following.Consider the move in yield from 𝑖0 = 8% to 𝑖1 = 6%. By adjusting ourapproximate formula for modified duration, calculate the (approximate) durationin years) of each of the three Treasury bonds maturing in 8 years we worked withabove (one with 4% coupon, one with 10% coupon, and one with 22% coupon).Next, consider the move in yield from 𝑖0 = 16% to 𝑖1 = 18%. Using your formulaabove, calculate the (approximate) duration (in years) of each of the threeTreasury bonds maturing in 8 years we worked with above (one with 4% coupon,one with 10% coupon, and one with 22% coupon).The approximate formula for modified duration, shown in the week 8 lecturenotes on slide 31. You are not required to calculate the exact duration, but youneed to calculate the approximate duration.The table below is the recommended format, you can add more columns ifnecessary.8-year18%You should also conduct a reasonable check in your documentation.

• If Duration of a coupon paying bond should always be less than the term of thebond. Are your answers consistent with this statement?

• Compare the Duration of the low coupon bonds to the Duration of highercoupon bonds. Which bonds have a longer duration? Is this what you expected?

• Compare the Duration of the bonds at low yields to the Duration of the samebonds at higher yields. When is the Duration longer, at low yields or high yields?Is this what you expected?

1. On your sheet labelled ‘Part d’ do the followingYou need to model the net bond price of a 15 September 2030 Treasury bondwith a coupon rate 𝑗2 = 5% p.a. and a face value of $100 that matures at par, theyield to maturity is 𝑗2 = 6%. You should account for a 30% tax on interest andcapital gain, assuming the tax on interest is deferred 6 months, and tax oncapital gain is deferred 12 months.You are required to create a bond price model that can calculate the net bondrice from 16 March 2024 to 15 September 2024. Your marker would input thesates in cell B1 (you can start with any dates from 16 March 2024 to 15September 2024 in cell B1), you are required to determine the net bond price incell E1. You can use any Excel cells in ‘part d’ to aid you to calculate your finalanswer in cell E1.You will be awarded with 1 bonus mark 代 写ACST2001 S2 2024 Spreadsheet   if your model can correctly determine thenet bond price after 15 September 2024. More information about the bonus markcan be found at the end of document.The deadline for Spreadsheet Project Task (group part) is 1 October 2024, 11:55 PM.resent your answers to the above questions in a functional Excel spreadsheet (itshould be .xlsx or .xls file), give each solution on a separate sheet (labelled Part a, Partb, Part c and Part d). Your first page (part a) should also give the name and student idnumber of each person in the group. Your spreadsheet should be clearly labelled andeasy to understand. Make sure you identify what the inputs and outputs are for eachworksheet. (You can check week 2 – week 5 practical solution and follow the format forinput and output). You need to include the necessary information, e.g., title, axis title,etc., in your plot. You need to document and describe your working steps. Note thatuploading a file can take up to 10 or 15 minutes. You need to submit your file at least 20minutes before the deadline to ensure a successful submission.Marking break down

1. Formatting and documentation for Part a-d [3 marks]
2. Part a [3 marks]
3. Part b [3 marks]
4. Part c [3 marks]
5. Part d [3 marks + 1 bonus mark]The maximum mark you can receive for this group project task is 15/15. If you get 14marks + 1 bonus mark your final mark would be 15/15. If you get 15 marks + 1 bonusmark your final mark would be 15/15.