Here is a excel project from Fin 421 class. itrequired excelformula to answer those questions. Our professor said it will only take 10 min to finish it if you know those formulas. but he was never teach us how to use excel. help me out plz.

Excel Project Due: October 26, 2016 As the bond portfolio manager you are concerned about the impact of changing interest rates on your the information below and excel functions to determine the value of the current bond portfolio, the po portfolio duration. this assignment you are assigned a portfolio of bonds. Assuming that insterest rate all bonds equally what is the value of the bond portfolio if interest rates increase by 0.25% or 0.5%? F bonds provided, which bond, if added to the bond portfolio will help insulate the portfolio from interes Issuer Name Coca Cola Co IBM Apple Inc Wal-Mart Stores Inc Johnson & Johnson Coupon 3.200% 1.625% 3.450% 7.550% 5.150% Coupons/Year 2 2 2 2 2 Maturity 11/1/2023 5/15/2020 2/9/2045 2/15/2030 7/15/2018 S&P AAAAAA+ AA 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 Coca Cola Co IBM Apple Inc Wal-Mart Stores Inc Johnson & Johnson Answer: If interest rates increase by 0.50% what is the value of bond portfolio? Issuer Name Yield Price Total Value Coca Cola Co IBM Apple Inc Wal-Mart Stores Inc Johnson & Johnson Answer: If you wanted to reduce the interest rate sensitivity of your bond portfolio which Issuer Name Coupon Coupons/Year Maturity Ford Motor Co 3.200% 2 1/15/2021 Intel 4.000% 2 12/15/2032 Kroger 8.000% 2 9/15/2029 of the following bond S&P BBB A+ BBB Answer: g interest rates on your portfolio value. Use t bond portfolio, the portfolio yield and the ming that insterest rate changes will impact e by 0.25% or 0.5%? From the additional e portfolio from interest rate risk? Yield 2.127% 1.608% 3.707% 2.870% 1.098% Date 10/6/2016 10/6/2016 10/6/2016 10/6/2016 10/6/2016 $ $ $ $ $ Price 104.12 100.06 95.52 151.58 107.05 h of the following bonds would you buy? Why? Yield Date Price 2.583% 10/6/2016 $ 102.47 3.229% 10/6/2016 $ 109.65 3.962% 10/6/2016 $ 104.58 Face Value $ 100.00 $ 100.00 $ 100.00 $ 100.00 $ 100.00 Quantity of Bonds in Portfolio 500 400 900 250 75 Total Value Portfolio Yield Portfolio Duration