Please help with the attached spreadsheet. I need pages 1, 2, & 4 completed. Only the blue and pink highlighted boxes.

BOND PRICING Annual Payments Inputs Number of Years to Maturity Number of Payments per Year Face Value 8 1 $1,000 YTM Coupon Payment 3.25% $35.00 Bond Price using a Timeline Year Cash Flows 1 $35.00 2 $35.00 3 $35.00 4 $35.00 5 $35.00 Present Value of Cash Flows Bond Price Bond Price using the PV Function Bond Price $0.00 Enter the Excel function PV. Hint: The results here should equal cell B16. 6 $35.00 7 8 $35.00 $1,035.00 Calculate the PV of the individual cash flows using the algebraic method. The value in the yellow cell will calculate on its own. BOND PRICING Semi-Annual YTM and Price Sensitivity Inputs Annual Coupon Rate Yield to Maturity (Annualized) Number of Payments / Year Number of Years to Maturity Face Value Outputs YTM / Payment Semiannual Coupon Payment 4% 1.75% 2 8 $1,000 Bond Price as Time Approaches Maturity Time Until Maturity Bond Price 0 1 2 3 4 5 6 7 8 Bond Price as YTM Changes YTM Bond Price 1% 2% 3% 4% 5% 6% 7% 8% 9% 10% proaches Maturity For this problem consider the bond price if the time until it matures equals the values in column E. Bond Pricing by Calendar Time $12.00 $10.00 $8.00 Bond Price $6.00 $4.00 $2.00 $0.00 0 1 2 3 4 5 6 7 8 Calendar Time (Years) Bond Price By Yield To Maturity $12.00 For this problem consider the bond price when the YTM changes. $10.00 $8.00 Bond Price $6.00 $4.00 $2.00 $0.00 1% 2% 3% 4% 5% 6% Yield To Maturity 7% 8% 9% 10% 6 8% 7 9% 8 10% What three things must be done if payments are semi-annual rather than annual? 1) Divide the annual coupon interest payments by two 2) Multiply the Maturity by two 3) Divide the Required Rate of Return by two. STOCK VALUATION Dividend Growth Model - Constant Growth Inputs Dividend Growth Rate Required rate of return Dividend Discount Model Year Dividend Growth Rate Dividend Stock Value 3% 11% Finite Horizon 0 1 3.0% 2 3.0% 3 3.0% Infin Horiz 4 3.0% 5 3.0% $6.64 Calculate the stock value using the Dividend Growth Model and the data provided in the yellow cells. Two hints: 1) notice that the dividend growth does not change, and 2) What year is the dividend in cell B11 received? 3.0% nt Growth