Here is the question to the Chapter 5 Excel Master it problem. I need help on this.

www.mhhe.e EL MASTER IT! PROBLEM case, the pon the portfolso are dedicated risk Reinvestment risk occurs bec ture Lability Im ono is sabjet to reinvestment the company will be r pyments it recelves I the YTM on similar bonds fats, the upon payments wi be reinvestes at Of course, if interest ,ate, which win resuit in a portfolio value that is lower than desired ot maturity. , erest rates increase, the portfolio value Suppose ice c ot maturity will be higher than needed has the following iability due in tive years. The company is going to buy fiue year roday to meet the future obligstion. The liability and curre nt YTM are below Amount oft liability Current YTM $100,000,000 At rhe current YTM, what its futiure obligation? the current YTM and these bond is the face value of the bonds the company has to purchase today to meet a. Assume that the bonds in the relevant range will have the same coupon rate as s make semiannual coupon payments b. Assume the interest rate s remain constant for the next five years. Thus, when the company reinvests thie coupon payments, it will reinvest at the current YTM. What is the value of the portfolio in five years c. Assume that immediately after the company purchases the bonds, interest rates either rise or fal by t percent. What is the value of the portfolio in five years under these circumstances? t 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 ded- cated portfolio, if the interest rate falls, the future value of the reinvested coupon payments decreases. Howevet as interest rates fall, the price of bonds increases. These effects offset each other in an i Another advantage of using duration to immunize a portfolio is that the duration of a portfolio is the io, you simply take the weight of each asset multiplied by its duration and then sum the results. weighted average of the duration of the assets in the portfolio. In other words, to find the duration of a portio- 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? BOND A 1/1/2000 1/1/2003 7.00% 7.50% BOND B Settlement Maturity Coupon rate 1/1/2000 1/1/2008 8.00% YTM 9.00% Coupons per year SING CASE CING EAST COAST YACHTS' EXPANSION PLANS BOND ISSUE N analysis for East Coast Yachts(see the Closing Case in Chapter 3), Larissa has decided www.mhhe.e EL MASTER IT! PROBLEM case, the pon the portfolso are dedicated risk Reinvestment risk occurs bec ture Lability Im ono is sabjet to reinvestment the company will be r pyments it recelves I the YTM on similar bonds fats, the upon payments wi be reinvestes at Of course, if interest ,ate, which win resuit in a portfolio value that is lower than desired ot maturity. , erest rates increase, the portfolio value Suppose ice c ot maturity will be higher than needed has the following iability due in tive years. The company is going to buy fiue year roday to meet the future obligstion. The liability and curre nt YTM are below Amount oft liability Current YTM $100,000,000 At rhe current YTM, what its futiure obligation? the current YTM and these bond is the face value of the bonds the company has to purchase today to meet a. Assume that the bonds in the relevant range will have the same coupon rate as s make semiannual coupon payments b. Assume the interest rate s remain constant for the next five years. Thus, when the company reinvests thie coupon payments, it will reinvest at the current YTM. What is the value of the portfolio in five years c. Assume that immediately after the company purchases the bonds, interest rates either rise or fal by t percent. What is the value of the portfolio in five years under these circumstances? t 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 ded- cated portfolio, if the interest rate falls, the future value of the reinvested coupon payments decreases. Howevet as interest rates fall, the price of bonds increases. These effects offset each other in an i Another advantage of using duration to immunize a portfolio is that the duration of a portfolio is the io, you simply take the weight of each asset multiplied by its duration and then sum the results. weighted average of the duration of the assets in the portfolio. In other words, to find the duration of a portio- 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? BOND A 1/1/2000 1/1/2003 7.00% 7.50% BOND B Settlement Maturity Coupon rate 1/1/2000 1/1/2008 8.00% YTM 9.00% Coupons per year SING CASE CING EAST COAST YACHTS' EXPANSION PLANS BOND ISSUE N analysis for East Coast Yachts(see the Closing Case in Chapter 3), Larissa has decided