excel formula for Portfolio Yield? Frequency as annual or what is it in the formula? Based on the data given

Excel File Edit View Insert Format Tools Data Window Help 2 Tue Oct 19 2:42 PM AutoSave OFF AvG... Excel Project go Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v 11 v AA = 29. Wrap Text Number SIX HH AY | 6 Y 22 D Conditional Format Cell Formatting as Table Styles Or v Paste B TU U 11 OA Merge Center Insert Delete 3 Format 3 Sort & Filter Find & Select Analyze Data K4 X fx A B D E F G H 1 2 L M N Finraum 1 Excel Project Due: October 20, 2021 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 2 portfolio from interest rate risk? Total Value Portfolio Duration 58P AA A- BBB AAA Yield 4.139% 4.504% 6.326% 4.036% 2.625% Date 10/13/21 10/13/21 10/13/21 10/13/21 10/13/21 $ $ $ $ $ $ Price Face Value 102.49 $ 100.00 95.11 $ 100.00 132.48 $ 100.00 125.10 $ 100.00 97.01 $ 100.00 Quantity of Bonds in Portfolio 350 600 710 100 Portfolio Yield 0.041263531 0.042299045 0.056980536 0.038088611 0.051710445 Duration 14.67029403 6.858208597 6.959823388 11.12404814 0.966666667 A 800 3 Issuer Name Coupon Coupons/Year Maturity 4 Walmart 4.300% 2 4/22/44 5 Wells Fargo 3.500% 2 10/26/29 6 Ford Motors 9.950% 2 2/15/32 7 Johnson & Johnson 5.950% 2 8/15/37 8 Caterpillar 1.931% 2 2 10/1/22 9 10 11 What is the value of the bond portfolio? 12 Answer: 13 14 What is the portfolio yield? ? 15 Answer: 16 17 What is the duration of the bond portfolio? 18 Answer: 19 20 If interest rates increase by 0.25% what is the value of bond portfolio? 21 Issuer Name Yield Price Total Value 22 Walmart 23 Wells Fargo 24 Ford Motors 25 Johnson & Johnson 26 Caterpillar 27 Answer: Sheet1 + !!! Ge- + Ready KL 125% TIK ZUIWERP PPY 201... Copy 17. IDAG Tik 20 RAG UNE Excel File Edit View Insert Format Tools Data Window Help 2 Tue Oct 19 2:42 PM AutoSave OFF AvG... Excel Project go Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v 11 v AA = 29. Wrap Text Number SIX HH AY | 6 Y 22 D Conditional Format Cell Formatting as Table Styles Or v Paste B TU U 11 OA Merge Center Insert Delete 3 Format 3 Sort & Filter Find & Select Analyze Data K4 X fx A B D E F G H 1 2 L M N Finraum 1 Excel Project Due: October 20, 2021 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 2 portfolio from interest rate risk? Total Value Portfolio Duration 58P AA A- BBB AAA Yield 4.139% 4.504% 6.326% 4.036% 2.625% Date 10/13/21 10/13/21 10/13/21 10/13/21 10/13/21 $ $ $ $ $ $ Price Face Value 102.49 $ 100.00 95.11 $ 100.00 132.48 $ 100.00 125.10 $ 100.00 97.01 $ 100.00 Quantity of Bonds in Portfolio 350 600 710 100 Portfolio Yield 0.041263531 0.042299045 0.056980536 0.038088611 0.051710445 Duration 14.67029403 6.858208597 6.959823388 11.12404814 0.966666667 A 800 3 Issuer Name Coupon Coupons/Year Maturity 4 Walmart 4.300% 2 4/22/44 5 Wells Fargo 3.500% 2 10/26/29 6 Ford Motors 9.950% 2 2/15/32 7 Johnson & Johnson 5.950% 2 8/15/37 8 Caterpillar 1.931% 2 2 10/1/22 9 10 11 What is the value of the bond portfolio? 12 Answer: 13 14 What is the portfolio yield? ? 15 Answer: 16 17 What is the duration of the bond portfolio? 18 Answer: 19 20 If interest rates increase by 0.25% what is the value of bond portfolio? 21 Issuer Name Yield Price Total Value 22 Walmart 23 Wells Fargo 24 Ford Motors 25 Johnson & Johnson 26 Caterpillar 27 Answer: Sheet1 + !!! Ge- + Ready KL 125% TIK ZUIWERP PPY 201... Copy 17. IDAG Tik 20 RAG UNE