Please Answer using EXCEL!!!!!!!!

Excel Master It$!$ Problem

Companies often buy bonds to meet a future liability or cash outlay. Such an

investment is called a dedicated portfolio because the proceeds of the portfolio

are dedicated to the future liability. In such a case, the portfolio is subject to

reinvestment risk. Reinvestment risk occurs because the company will be

EXCEL MASTER

coverage online

reinvesting the coupon payments it receives. If the YTM on similar bonds falls,

these coupon payments will be reinvested at a lower interest rate, which will result in a portfolio value that is lower than desired at maturity.

Of course, if interest rates increase, the portfolio value at maturity will be higher than needed.

Suppose Ice Cubes, Inc., has the following liability due in five years. The company is going to buy five$-$year bonds today in order to meet the

future obligation. The liability and current YTM are below:

a$.$ At the current YTM$,$ what is the face value of the bonds the company has to purchase today in order to meet its future obligation?

Assume that the bonds in the relevant range will have the same coupon rate as the current YTM and these bonds make semiannual

coupon payments.

b$.$ Assume that the interest rates remain constant for the next five years. Thus, when the company reinvests the coupon payments,

it will reinvest at the current YTM$.$ What will be the value of the portfolio in five years?

c$.$ Assume that immediately after the company purchases the bonds, interest rates either rise or fall by $1$ percent. What will be the value of

the portfolio in five years under these circumstances?

One way to eliminate reinvestment risk is called immunization. Rather than buying bonds with the same maturity as the liability, the

company instead buys bonds with the same duration as the liability. If you think about the dedicated portfolio, if the interest rate falls, the

future value of the reinvested coupon payments decreases. However, as interest rates fall, the price of the bond increases. These effects offset

each other in an immunized portfolio.

Another advantage of using duration to immunize a portfolio is that the duration of a portfolio is the weighted average of the duration of the

assets in the portfolio. In other words, to find the duration of a portfolio, you take the weight of each asset multiplied by its duration and

then sum the results.

d$.$ What is the duration of the liability for Ice Cubes, Inc.?

e$.$ Suppose the two bonds shown below are the only bonds available to immunize the liability. What face amount of each bond will the

company need to purchase to immunize the portfolio?