Can someone please do this and solve with actual excel formulas. thank you
Finra-mar Excel Project Due: March 3, 2020 As the bond portfolio manager you are concerned about the impact of changing interest rates on your portfolio value. Use the information below and excel functions to determine the value of the current bond portfolio, the portfolio yield and the portfolio duration. Assuming that insterest rate changes will impact all bonds equally what is the value of the bond portfolio if interest rates increase by 0.25% or 0.5%? From the additional bonds provided, which bond, if added to the bond portfolio will help insulate the portfolio from interest rate risk? Coupons/Year Total Value Portfolio Yield Duration Portfolio Duration S&P AA A- Issuer Name Walmart Wells Fargo Ford Motors Johnson & Johnson Caterpillar Coupon 7.550% 3.250% 9.375% 6.730% 2.850% Maturity 2/15/30 8/2/27 3/1/20 11/15/23 6/1/22 Yield 2.197% 1.265% 4.233% 1.619% 1.717% Date 2/18/20 2/18/20 2/18/20 2/18/20 2/18/20 Price 147.78 100.39 100.17 118.48 102.54 $ $ $ $ $ Face Value $ 100.00 $ 100.00 $ 100.00 $ 100.00 $ 100.00 Quantity of Bonds in Portfolio 490 780 350 510 660 BBB AAA What is the value of the bond portfolio? Answer: What is the portfolio yield? Answer: What is the duration of the bond portfolio? Answer: If interest rates increase by 0.25% what is the value of bond portfolio? Issuer Name Yield Price Total Value Walmart Wells Fargo Ford Motors Johnson & Johnson Caterpillar Answer: If you wanted to reduce the interest rate sensitivity of your bond portfolio which of the following bonds would you buy? Why? Issuer Name Coupon Coupons/Year Maturity S&P Yield Date Price Apple 2.400% 5/3/23 AA+ 1.589% 2/18/20 $ 102.52 Delta Airlines 2.875% 3/13/20 BB+ 2.662% 2/18/20 $ 100.01 Macys 3.875% 2 1/15/22 BBB 2.414% 2/18/20 $ 102.36 Answer: Finra-mar Excel Project Due: March 3, 2020 As the bond portfolio manager you are concerned about the impact of changing interest rates on your portfolio value. Use the information below and excel functions to determine the value of the current bond portfolio, the portfolio yield and the portfolio duration. Assuming that insterest rate changes will impact all bonds equally what is the value of the bond portfolio if interest rates increase by 0.25% or 0.5%? From the additional bonds provided, which bond, if added to the bond portfolio will help insulate the portfolio from interest rate risk? Coupons/Year Total Value Portfolio Yield Duration Portfolio Duration S&P AA A- Issuer Name Walmart Wells Fargo Ford Motors Johnson & Johnson Caterpillar Coupon 7.550% 3.250% 9.375% 6.730% 2.850% Maturity 2/15/30 8/2/27 3/1/20 11/15/23 6/1/22 Yield 2.197% 1.265% 4.233% 1.619% 1.717% Date 2/18/20 2/18/20 2/18/20 2/18/20 2/18/20 Price 147.78 100.39 100.17 118.48 102.54 $ $ $ $ $ Face Value $ 100.00 $ 100.00 $ 100.00 $ 100.00 $ 100.00 Quantity of Bonds in Portfolio 490 780 350 510 660 BBB AAA What is the value of the bond portfolio? Answer: What is the portfolio yield? Answer: What is the duration of the bond portfolio? Answer: If interest rates increase by 0.25% what is the value of bond portfolio? Issuer Name Yield Price Total Value Walmart Wells Fargo Ford Motors Johnson & Johnson Caterpillar Answer: If you wanted to reduce the interest rate sensitivity of your bond portfolio which of the following bonds would you buy? Why? Issuer Name Coupon Coupons/Year Maturity S&P Yield Date Price Apple 2.400% 5/3/23 AA+ 1.589% 2/18/20 $ 102.52 Delta Airlines 2.875% 3/13/20 BB+ 2.662% 2/18/20 $ 100.01 Macys 3.875% 2 1/15/22 BBB 2.414% 2/18/20 $ 102.36
