(For this part, you MUST present sufficient solution steps, and MUST apply speci
ID: 2621159 • Letter: #
Question
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =PV(…), =FV(…), =PMT(…), =NPER(…), =RATE(…), =PRICE(…) or =YIELD(…) whenever applicable. Please show me the EXCEL functions that was used to help me better understand was equals what. Using Excel finance formulas
We find the data for a municipal bond issued by the Illinois state government.
The bond’s coupon rate is fixed as “5.000%” per year (coupon paid semiannually).
The bond’s yield-to-maturity (YTM, or YIELD) is quoted as “3.818%” per year.
The bond’s last sale settlement date is June 28, 2018.
The bond’s maturity date is October 01, 2054.
Based on the aforementioned settlement date, maturity date, coupon rate, coupon frequency and yield to maturity, what shall be the corresponding bond PRICE (as proportion of par value, in Excel bond function output)?
(b) If the Fed tightens its monetary policy now, the financial market interest rates generally rise, and thus the aforementioned bond’s yield-to-maturity also rises from 3.818% to “4.818%” per year. Will the corresponding bond PRICE go up or go down then? By how much?
(c) If the Fed loosens its monetary policy now, the financial market interest rates generally drop, and thus the aforementioned bond’s yield-to-maturity also drops from 3.818% to “2.818%” per year. Will the corresponding bond PRICE go up or go down then? By how much?
(d) Based on your answers to (b) and (c), is there a positive, negative or zero association between bond YIELD and its PRICE? What kind of risk is this called?
Explanation / Answer
a. The price of the bond is calculated as =PRICE(DATE(2018,6,28),DATE(2054,10,1),5%,3.818%,100,2,0) in excel which gives the output = $123.10
The price of the bond = $123.10
b. When yield increases to 4.818%, the price is calculated as =PRICE(DATE(2018,6,28),DATE(2054,10,1),5%,4.818%,100,2,0) = 103.10
When the yield increases, the price of the bond will decrease to $103.10
c. When yield decreases to 2.818%, the price is calculated as =PRICE(DATE(2018,6,28),DATE(2054,10,1),5%,2.818%,100,2,0) = 149.35
When the yield decreases, the price of the bond will increase to $149.35
d. There is a NEGATIVE association between price and yield. As yield increases, the price decreases and vice-versa. There is an inverse relationship here.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.