PLEASE SOLVE QUESTION 3 USING EXCEL ONLY!!! SHOW ALL FOMRULAS USED IN EXCEL
2. The following table shows information about an Amazon.com bond as of July 19, 2017: Amazon.com Bond Information Settlement 7/21/2017 Maturity 12/5/2044 Coupon 4.950% Face Value 100 Given Price 118.691 Given Yield 3.840% Frequency 2 a) Determine the value of the bond and its current yield. Also find whether the bond is undervalued, overvalued, or fairly valued compared to the given price. b) Calculate the yield to maturity using the YIELD function and find the duration, modified duration, and convexity of the bond. c) Determine the percentage change in price if market rates decline by 1%. Determine the actual relative change in bond prices using the intrinsic values for each rate. Compare this result with those obtained using the formulas with the modified duration only and with the modified duration and convexity together. d) Create a chart that shows the relationship of this bonds convexity to the coupon rate. Use a rate of 1% to 15% in calculating the convexity. 3. Suppose the firm in the previous problem can raise new funds in the capital markets under the following conditions: Source Range After-tax Cost Retained Earnings Up to 500,000 Solution Problem #3 Common Equity Up to 2,000,000 Solution Problem #3 2,000,001 to 5,000,000 17.00% More than 5,000,000 19.00% Preferred Equity Up to 500,000 Solution Problem #3 More than 500,000 8.50% Debt Up to 2,000,000 Solution Problem #3 2,000,001 to 5,000,000 7% More than 5,000,000 9% a) Enter the above information using custom number formats to format the Range column. Note that some of the answers should be linked to your solution to the previous question. b) Using this information, calculate each of the break-points. c) Create a chart of the firm's marginal WACC curve using the market value weights. Make sure that it is a perfect step function. 2. The following table shows information about an Amazon.com bond as of July 19, 2017: Amazon.com Bond Information Settlement 7/21/2017 Maturity 12/5/2044 Coupon 4.950% Face Value 100 Given Price 118.691 Given Yield 3.840% Frequency 2 a) Determine the value of the bond and its current yield. Also find whether the bond is undervalued, overvalued, or fairly valued compared to the given price. b) Calculate the yield to maturity using the YIELD function and find the duration, modified duration, and convexity of the bond. c) Determine the percentage change in price if market rates decline by 1%. Determine the actual relative change in bond prices using the intrinsic values for each rate. Compare this result with those obtained using the formulas with the modified duration only and with the modified duration and convexity together. d) Create a chart that shows the relationship of this bonds convexity to the coupon rate. Use a rate of 1% to 15% in calculating the convexity. 3. Suppose the firm in the previous problem can raise new funds in the capital markets under the following conditions: Source Range After-tax Cost Retained Earnings Up to 500,000 Solution Problem #3 Common Equity Up to 2,000,000 Solution Problem #3 2,000,001 to 5,000,000 17.00% More than 5,000,000 19.00% Preferred Equity Up to 500,000 Solution Problem #3 More than 500,000 8.50% Debt Up to 2,000,000 Solution Problem #3 2,000,001 to 5,000,000 7% More than 5,000,000 9% a) Enter the above information using custom number formats to format the Range column. Note that some of the answers should be linked to your solution to the previous question. b) Using this information, calculate each of the break-points. c) Create a chart of the firm's marginal WACC curve using the market value weights. Make sure that it is a perfect step function
