Please use the exact same format shown here. Also show the excel formulas!

136 JX D E H Q R TREASURY BOND CALCULATION Computing the accrued Interest Current date 20-Feb-2015 Previous Interest payment date 15-Oct-2014 TREASURY BOND VALUATION Next Interest payment date 15-Apr-2015 Semi-annual coupon On Feb. 20, 2015, you are offered a US Treasury note. Here are the terms of the note: Days since last coupon date - The note has face value of $100,000 and 6.5% coupon rate. The note matures on Oct. 15, 2020 Days between last coupon date and next coupon date - The semi-annual interest on the note is paid on April 15 and Oct 15 of each year. The last interest payment was Oct. 15, 2014, and the next interest payments are on Apr 15, 2015 and Oct 15, 2015. 13 Accrued Interest #N/A - On the last date, the bond's principal of $100,000 is also returned. Computing the YTM - On Feb. 20, 2015, the bond was priced at $109,477.71. Bond price Accrued Interest Invoice price (bond price + accrued) Part a. What is the semi-annual coupon payment? Date Bond cash flow Part b. What is the accrued interest? 20-Feb-2015 Part c. Use XIRR to calculate the annualized yield to maturity (YTM). 15-Apr-2015 Part d. Use YIELD function to calculate the YTM. Is there a difference between the two YTMs and why? 23 15-Oct-2015 24 15-Apr-2016 Part e. Calculate the annualized rate using the YTM with the YIELD function 25 15-Oct-2016 26 15-Apr-2017 27 15-Oct-2017 28 15-Apr-2018 29 15-Oct-2018 30 15-Apr-2019 31 15-Oct-2019 32 15-Apr-2020 33 15-Oct-2020 34 35 C. YTM Using XIRR function 36 d. YTM using YIELD function 37 9. Excel's Ylield annualized 38 39 40 41 42 43