i need help with this assignment, the first few pictures are examples n last picture will be the main assignment. i need it with excel sheets

RHIM 4322 * Demo Exercise 5 * Characteristics and Valuation of Bonds Information Sheet Bond Valuation 1. Consider a bond issued by Wal-Mart with a maturity date of December 2022 and a stated annual coupon rate of 4.55%. In December 2018, with 4 years left to maturity, investors owing the bonds were only requiring a 2.48% rate of return. What would be the value of the bonds to these investors? 2. Suppose our firm decides to issue 20-year bonds with annual coupon payments. We decide to offer a 12% coupon rate. The required return on this kind of corporate bonds is currently 14%. What would be the value of these bonds to investors? 3. Suppose our firm decides to issue 20-year bonds. We decide to offer a 12% coupon rate. The required return on bonds of similar risk is 10%. What is the bond's value to investors? 4. You own a bond that pays 10% in annual coupon rate. It matures in 15 years. Calculate the value of the bond to you as the investor if your required rate of return on the bonds is (a) 8%, (b) 10%, and (c) 12%, respectively. Expected Rate of Return of Bond (Yield to Maturity) 5. The market price is $900 for a 10-year bond that pays 8% coupon rate. What is the bond's expected rate of return (Yield to Maturity)? 6. Exxon Mobil 20-year bonds pay 9% coupon rate annually. If bonds sell at $945, what is the bonds expected rate of return (Yield to Maturity)? 7. Suppose a bond sells at $898.90 and 10% coupon rate with 8 years to maturity and annual coupon interest payments. What is the bond's expected rate of return (Yield to Maturity)? Investment Decisions 8. Cranston Industries just issued 30-year bonds that pay 8.5% coupon rate annually. a. The bonds sold for $1,107 20. Calculate the bond's expected rate of return (Yield to Maturity). b. Investors require a return rate of 7.75% on the bonds. Determine the value of the bond to you as an investor c. Should you purchase the bond? Why? 9. Monster Zinc just issued 20-year bonds that pay 7.65% coupon rate annually. a. The bonds sold for $990.25. Calculate the bond's expected rate of return (Yield to Maturity). b. Investors require a rate of 6.45% on the bonds. Determine the value of the bond to you as an investor c. Should you purchase the bond? Why? 1 B D G K M 1. Consider a bond issued by Wal-Mart with a maturity date of December 2022 and a stated annual coupon rate of 4.55%. In December 2018, with 4 years left to maturity, N investors owing the bonds were only requiring a 2.48% rate of return. What would be the value of the bonds to these investors? FV 1,000 FV is always 1000 for any bonds rate 0.0248 2. 3 4 nper 4 5 pet 45.50 - FV Coupon Rate - 1000 0.0455 45.50 6 O type PV- 7 ($1,077.91) 8 PV- 9 10 11 12 13 OR ($1,077.91) Enter each number by moving your mouse to the correponding cell and click (e.g.cell C2 for fv) Hit Enter Key when all numbers are entered B D E 13 F G H 1 J K L M N 2. Suppose our firm decides to issue 20-year bonds with annual coupon payments. We decide to offer a 12% coupon rate. The required return on this kind of corporate bonds is currently 14%. What would be the value of these bonds to investors? 15 FV 1,000 16 rate 0.14 17 nper 20 18 pmt 120.00 19 type 0 20 PV- (5867.54) 21 22 3. Suppose our firm decides to issue 20-year bonds. We decide to offer a 12% coupon rate. The required return on bonds of similar risk is 10%. What is the bond's value to le 23 FV 1,000 24 rate 0.10 25 nper 20 25 pmt 120.00 27 type 0 28 PV- ($1,170.27) 29 Calibri 11 BIUSA 1 2 3 4 B D E F H 1 J K 5. The market price is $900 for a 10-year bond that pays 8% coupon rate. What is the bond's expected rate of return (Yield to Maturity)? FV 1,000 FV is always 1000 for any bonds PV -900 PV is negative since this is cash paid out nper 10 pmt 80.00 - FV* Coupon Rate - 1000 * 0.08 - 80.00 type O Rate 9.60% -RATE(nper,pmt.pw.[v].[type).(guess] 5 6 7 8 9 PV 10 11 12 13 6. Exxon Mobil 20-year bonds pay 9% coupon rate annually. If bonds sell at $945, what is the bonds' expected rate of return (Yield to Maturity)? FV 1,000 -945 nper 20 pmt 90.00 type O Rate - 9.63% 15 16 17 18 19 20 7. Suppose a bond sells at $898.90 and 10% coupon rate with 8 years to maturity and annual coupon interest payments. What is the bond's expected rat to Maturity)? FV 1,000 -898.9 8 1 PV nper Bond Vibor Yield to Maturity Investment Decisions Insert Format Help Calibri 11 BIUSA B NE D E 8 F G H 1 K 9 M N 10 11 12 13 14 15 16 6. Exxon Mobil 20-year bonds pay 9% coupon rate annually. If bonds sell at $945, what is the bonds' expected rate of return (Yield to Maturity)? FV 1,000 PV -945 nper 20 pmt 90.00 type Rate - 9.63% 17 18 19 20 21 22 23 24 25 26 27 28 29 7. Suppose a bond sells at $898.90 and 10% coupon rate with 8 years to maturity and annual coupon interest payments. What is the bond's expected rate of return (Yield to Maturity)? FV 1,000 PV -898.9 nper 8 pmt 100.00 type 0 Rate - 12.04% Bond Valuation Yield to Maturity Investment Decisions he 2 * 8. Cranston Industries just issued 30-year bonds that pay 8.5% coupon rate annually A B D E F 1 8. Cranston Industries just issued 30-year bonds that pay 8.5% coupon rate annually. G H 3 a. The bonds sold for $1,107.20. Calculate the bond's expected rate of return (Yield to Maturity). 4 FV 1,000 5 PV -1107.2 6 nper 30 7 pmt 85.00 8 type 0 9 Rate = 7.58% 10 11 b. Investors require a return rate of 7.75% on the bonds. Determine the value of the bond to you as an investor. 12 FV 1,000 13 rate 0.0775 14 nper 30 15 pmt 85.00 16 type 0 17 PV- ($1,086.46) 18 19 c. Should you purchase the bond? Why? 20 No. Because the market price ($1,107.20) is higher than the budget ($1,086.46) (i.e. the price that the invester is willing to pay). 21 22 9. Monster Zinc just issued 20-year bonds that pay 7.65% coupon rate annually. 24 a. The bonds sold for $990.25. Calculate the bond's expected rate of return (Yield to Maturity). 25 FV 1.000 PV -990.25 Band Valuation Yield to Maturity Investment Decisions 2 o Calibri 11 BI USA B D E F TV. Decause the market price >1,107. OFISI Wanne Doug (1,000. O. price investeris wmung wu pays H K M 20 21 22 N 9. Monster Zinc just issued 20-year bonds that pay 7.65% coupon rate annually. 24 25 26 27 28 29 30 31 32 33 34 35 a. The bonds sold for $990.25. Calculate the bond's expected rate of return (Yield to Maturity). FV 1,000 PV -990.25 nper 20 pmt 76.50 type 0 Rate - 7.75% b. Investors require a rate of 6.45% on the bonds. Determine the value of the bond to you as an investor. FV 1,0001 rate 0.0645 Inper 20 pmt 76.50 type 0 PV- ($1,132.75) 36 37 38 39 40 41 42 43 44 c. Should you purchase the bond? Why? Yes. Because the market price (5990.25) is lower than the budget ($1,132.75) (i.e. the price that the invester is willing to pay). AF Bond Valuation Yield to Maturity Investment Decisions RHIM 4322 * Homework 5 * Characteristics and Valuation of Bonds Please complete and show work in an excel spreadsheet following the Demo Exercise. Do transfer the financial data to the spreadsheet. No need to show the explanatory notes like what is being provided in the Demo Exercise. Submit the Homework spreadsheet using this filename "RHIM4322- Last_Name-Homework 5" 1. Calculate the value of a bond that will mature in 20 years. The annual coupon interest rate is 7.75%, and the investor's required rate of return is 8.25%. 2. You own a bond that pays 12% in annual coupon interest. It matures in 15 years. Calculate the value of the bond if your required rate of return on the bonds is (a) 8%, (b) 12%, and (c) 15%, respectively. 3. Assume the market price of a 5-year bond for Margaret Inc. is $900. The bond has an annual coupon interest rate of 6%. What is the bond's expected rate of return (Yield to Maturity)? 4. National Steel 15-year bonds pay 8% coupon interest annually. a. The market price of the bonds is $1,085. Compute the bond's expected rate of return (Yield to Maturity). b. Your required rate of return is 10%. Determine the value of the bond to you. Should you purchase the bond? Why? c