PLEASE PROVIDE SPREADSHEET WORKING

For example, if there is a fixed liability equal to $1$ million dollars five years from now, an institution can buy a discount bond maturing in five years with a face value of $1$ million dollars. Unfortunately, there may not be the $$right$$ discount bonds for a fixed future liability and coupon bonds must be used. Then an institution faces reinvestment risk on the coupons. For example, suppose that the yield curve is flat at $10\%$ and we have the following coupon bonds $($paying annual coupons$)$:

For example, suppose that the yield curve is flat at $10\%$ and we have the following coupon bonds $($paying annual coupons$)$:and we have a $1$ million liability five years from now.

BOND PRICES PRINCIPAL COUPON YEAR TO MATURITY

A

B $118\mathrm{.}95$

$130\mathrm{.}72100$

$10015$

$155$

$10$

$($a$)$Suppose that the yield curve will remain unchanged for the following five years and you have decided to use bond A to fund the liability. That is$,$ you want to invest in bond A and invest the coupons at the prevailing interest rates to produce a future value at the end of year five of $1$million. How much should you invest in bond A$?$

$($b$)$Now suppose that right after you invested in bond A$,$ the yield curve makes a parallel move downby $1\%$ to $9\%.$ What is the future value five years from now of your investment?

What is the futurevalue if the yield curve moves up by $1\%$ to $11\%?$ Please explain why the future value changesdifferently depending on the direction of the change in the yield curve.

$($c$)$Part $($b$)$ shows that the future value of your investment is sensitive to interest ratefluctuations, and you face the risk that your future liabilities may not be met. You should try to$$immunize$$ this interest rate risk.

But how? Do the following:

i$.$Construct a portfolio of the two coupon bonds so that the future value of the portfolio is $1$million and the duration of this portfolio is equal to five years, assuming that the yield curve will remain flat at $10\%.$

ii$.$ Show that if immediately after you purchased this portfolio the yield curve makes a permanent parallel downward or upward move of $1\%,$ the future value of this portfolio at the end of year $5$ will still be approximately $1$ million. You have immunized the portfolio of the risk associated with parallel movements of the yield curve by buying a portfolio of coupon bonds so that the duration of the portfolio matches the number of years to the payment of the fixed liability.

iii. Suppose now that you have held your portfolio for one year after a $1\%$ decrease in the yield curve to $9\%$ which occurred immediately after you constructed your initial portfolio with a duration of five. There are now four years to the payment of the fixed liability. Use the money at your disposal $($the market value of your investment at the end of year one$)$ to construct a portfolio of coupon bonds with duration equal to four years and a future value four years hence equal to approximately $1$ million, given the new flat yield curve at $9\%.$ Show that if the yield curve then makes a parallel upward or downward move of $1\%,$ the future value of your portfolio four years from now will be unchanged. You have approximately funded your liability of $1$ million at the end of the fifth year. $($Compare the difference between the future value of your portfolio and your fixed liability here and in part $($b$).)$ This technique is called $$duration matching$$: if you adjust your portfolio over time so that its duration always matches the years to the payment date of your fixed liabilities, you will approximately immunize the risk of parallel shifts in the yield curve.