really need help in part 2 (yellow color)

all the information relevant in part 1

AutoSave OFF BFI-Assignment-2019-1 Q v Search Sheet Home Insert Draw Page Layout Formulas Data Review View Share Comments La X Calibri (Body) 9 A" A Number Insert v x Delete v Paste B I E E $ Conditional Format Cell Format X Sort & Sensitivity 6 Formatting as lable Styles Filter J14 X fx PART 2 - DURATION In this part, round all answers to FOUR decimal places. What is the price of each of Sydney's corporate bonds? (Calculate the price based on the current yield for the Box Q $ 337.1159 corporate bonds, but as at the date the most recent coupon payment was made prior to today's date - the date in Box D in Part 1. For example, if the yield on these bonds is currently 2.5%, and the last coupon payment was on 21 March 2016, what would the price have been on 21 March at a yield of 2.5% p.a. ?) 2. What is the duration of Sydney's corporate bonds? Box R 1.8984 years 3. What is the price of each of Sydney's Treasury bonds? (Assume a face value for each bond of $100. Calculate the Box S price in the same manner as described above - based on the current yield, but as at the last date a coupon $ payment was made prior to today's date - the date on Box B in Part 1.) 4. What is the duration of Sydney's Treasury bonds? Box T years 5. If the yield on the corporate bonds were to decrease by 1 basis point: (a) Recalculate the price of the corporate bonds. Box U (b) What is the actual change in price of the corporate bonds for a 1 basis point decrease in yield (i.e. the Box V percentage change in price between Boxes Box Q and Box U)? (Include a negative sign if the value is negative.) (c) What is the estimated change in price of the corporate bonds (based on duration) for a 1 basis point Box W decrease in yield ? (Include a negative sign if the value is negative.) (d) Compare the actual and estimated changes in price (the values in Boxes Box V and Box W). What would you conclude from this about the usefulness of duration in estimating the change in the price of a bond with a short duration for small changes in viold? Instructions Group ID Requirements Fin Stmts Part 1 Part 2 Part 2 Wkgs Part 3 Part 3 Wkgs Part 4 Part 4 Wkgs Q M + + 100%AutoSave OFF BFI-Assignment-2019-1 Q v Search Sheet Home Insert Draw Page Layout Formulas Data Review View Share Comments La X Calibri (Body) 9 A" A = ab Number Insert x Delete v NV Paste B U v = HE $ Conditional Format Cell X Sensitivity 6 Format Sort & Formatting as lable Styles Filter 147 X fx 1.6142 FIN2BFI - BANKING AND FINANCIAL INSTITUTIONS - ASSIGNMENT PART 1 - INTEREST RATE AND DERIVATIVES DATA Follow the instructions below to locate interest rate data and derivatives data that you will need for your Assignment. Unless everyone in the team reads every word on this page carefully you will make mistakes in later parts of the Assignment. When do the Treasury bonds mature? Box A 21/5/41 On what date was a coupon payment last made? Box B 21/11/18 You will determine the value and duration of the Treasury bonds as at this date. Find the credit spread on the bank's corporate bonds. When do the corporate bonds mature? Box C 15/4/20 On what date was a coupon payment last made? Box D 15/10/18 You will determine the value and duration of the corporate bonds as at this date. How many years are there between the dates in Boxes C and D? (If this is a fraction, round up to the nearest whole number of years.) Box E 2 years (Note: The value in Box E will ONLY be used to determine the credit spread on the corporate bonds (Box F below). We need a whole number of years to find the credit spread. But when you calculate the price and duration of the corporate bond in Part 5 you need to work out the number of future coupon payments based on the actual time to maturity of the bonds - not necessary using the value in Box E. You need to determine the yield on the bank's corporate bonds, and to do this you need to find the credit spread for bonds of that maturity and with Sydney's credit rating. Download the file "Corporate Bond Spreads" from the Assignment section of the LMS. Credit spreads are expressed in basis points, where a basis point is 0.01%. A credit spread is the amount we need to add to the risk-free rate to find the yield on a bond with a given credit rating (or debt rating). You need to add the credit spread in Box F to the relevant Australian risk-free rate (which you will enter in Box G below) to get the yield on the corporate bonds. Determine the credit spread for the corporate bonds and enter this v is points) in Box F below. Credit spread for Sydney's bonds - Box F 111 bp Find the yield on Sydney's corporate and Treasury bonds. Go to the Reserve Bank www.rba.gov.au Click on "Statistics" near the top of the page, and then, under the subheading "Economic and Financial Statistics", click on "Interest Rates". (Depending on your browser, you may then have to scroll up until you see the Interest Rates section.) Click on the 'XLS' link to open the spreadsheet containing "Indicative Mid Rates of Australian Government Securities - F16". This spreadsheet contains daily yield data for Australian Treasury bonds on issue on a given date. Row 3 contains the maturity date and the coupon rate of each bond. Search along Row 3 to find the column containing the bonds that mature on the same day as Sydney's corporate and Treasury bonds. Then go down that column to find the last value in the columns. The values you see at the bottom of each column are the current yields on these bonds - as at the date in Column A. The date in Column A, at the start of that row, is the most recent date (the date closest to today) for which yields are available. Enter the yields in Boxes G & I below (as percentages). In other words, if the yield is 2.225%, just enter 2.225. Do not enter 2.225% or 0.0225. These instructions apply to ALL percentage values in this Assignment. Yield on Treasury bonds maturing on the same day as the corporate bonds - B Box G 1.405 Box H 2.515 % Yield on Sydney's corporate bonds Instructions Group ID Requirements Fin Stmts Part 1 Part 2 Part 2 Wkgs Part 3 Part 3 Wkgs Part 4 Part 4 Wkgs Q M + + 50%