Excel Activity: Bond Valuation Start with the partial model in the file Cho4 p24 Build a Model.xlsx. A 15-year 9% semiannual coupon bond with a per value of $1,000 may be called in 4 years at a call price of $1,050. The bond sells for $1,080. (Assume that the bond has just been issued.) The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round Intermediate calculations. X Download spreadsheet Ch04 224 Build a Model-0935c7.xlsx a. What is the bond's yield to maturity? Round your answer tatwo decimal places. % b. What is the bond's current yield? Round your answer to two decimal places Search this course aluation X U. WITOLD ME WHU LIT yetu! HUIU yuuchavet LU veure Pro % c. What is the bond's capital gain or loss yield? Round your answer to two decimal places. Use a minus sign to enter a negative value, if any. % d. What is the bond's yield to call? Round your answer to two decimal places. % e. How would the price of the bond be affected by a change in the going market interest rate? (Hint: Conduct a sensitivity analysis of price to changes in the going market interest rate for the bond. Assume that the bond will be called it and only if the going rate of interest falls below the coupon rate. This is an oversimplification, but assume it for purposes of this problem.) Round your answers to the nearest cent. e. How would the price of the bond be affected by a change in the going market interest rate? (Hint: Conduct a sensitivity analysis of price to changes in the going market interest rate for the bond. Assume that the bond will be called if and only if the going rate of interest falls below the coupon rate. This is an oversimplification, but assume it for purposes of this problem.) Round your answers to the nearest cent. Nominal market rate 096 Actual bond price $ 2% $ 4% $ 6% $ 8% $ 10% 12% 14% $ $ 16% $ f. Now assume the date is October 25, 2020. Assume further that a 13%, 10-year bond was issued on July 1, 2020, pays 8% $ 10% $ 12% $ $ 14% 16% $ f. Now assume the date is October 25, 2020. Assume further that a 13%, 10-year bond was issued on July 1, 2020, pays Interest semiannually (on January 1 and July 1), and sells for $1,080. Again, it may be called in 4 years from the date of issue at a call price of $1,050. Use your spreadsheet to find the bond's yield. Round your answers to two decimal places. % Yield to maturity: % Yield to call: Check My Work Reset Problem 1 F G fx B D E 1 Bond Valuation 2 3 Years to maturity 15 4 Number of coupon payment per year 2 5 Coupon rate 9% 6 Par value $1,000 7 Current price $1,080 8 Call price $1,050 9 Years until bond is cailable 4 10 11 a. Calculating the bond's yield to maturity Formulas 12 Penodic YTM #N/A 13 Annualized nominal YTM NA 14 15 b. Calculating the band's current yield 16 Current yield UNA 17 18 c. Calculating the bond's capital gain or loss yield 19 Capital gain/loss yield #N/A 20 21 d. Calculating the bond's yield to call + 22 Periodic YTC NA 23 Annualized nominal YTC WNIA 24 25 e. Assume that the bond will be called if and only if the going rate of interest fails below the Count 20 Conducting a sensitivity analysis of 25 e. Assume that the bond will be called if and only if the going rate of interest falls below the coupon rate. 26 Conducting a sensitivity analysis of price to changes in the going market interest rate for the bond 27 Nominal market rate 9% 28 Bond price if it's not called #N/A 29 Bond price if it's called #N/A 30 31 Nominal Bond price if 32 Actual market rate Not called Called 33 bond price (C27) $0.00 $0.00 34 0% 35 2% 36 4% 37 6% 38 8% 39 10% 40 12% 41 1496 42 16% 43 44 f. Calculating the bond's yield 45 Settlement date (today) 10/25/2020 46 Matunity date 7/1/2030 47 Call date 7/1/2024 48 Coupon rate 13% 49 Par value (% of par value) 100 5. Current prie 196 of nar value Formulas Nominal market rate Note (C27) 096 NA 2% NA 4% N/A 6% WN/A 8% NA 10% #N/A 1296 VA 14% N/A 16% DNA G File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be carefules from the Internet con contains. Unless you need to edit ater.to.in Protected C12 x fx B D E 14 f. Calculating the bond's yield 5 Settlement date (today) 10/25/2020 6 Maturity date 7/1/2030 7 Call date 7/1/2024 B Coupon rate 13% 9 Par value (% of par value) 100 0 Current price (% of par value) 0 1 Call price (% of par value) 0 2 2 Frequency (semiannual bonds) 1 3 Basis (360- or 365-day year) 4 Yield to maturity WNIA 5 Yield to call #N/A 7 8 9 0 1 Actual bond price Formulas Nominal market rate (C27) 0% 2% 4% 6% 8% 10% 12% 14% 16% Bond price if Not called Called $0.00 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A $0.00 #N/A #N/A #NA #N/A #N/A #N/A #N/A #N/A #N/A Excel Activity: Bond Valuation Start with the partial model in the file Cho4 p24 Build a Model.xlsx. A 15-year 9% semiannual coupon bond with a per value of $1,000 may be called in 4 years at a call price of $1,050. The bond sells for $1,080. (Assume that the bond has just been issued.) The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round Intermediate calculations. X Download spreadsheet Ch04 224 Build a Model-0935c7.xlsx a. What is the bond's yield to maturity? Round your answer tatwo decimal places. % b. What is the bond's current yield? Round your answer to two decimal places Search this course aluation X U. WITOLD ME WHU LIT yetu! HUIU yuuchavet LU veure Pro % c. What is the bond's capital gain or loss yield? Round your answer to two decimal places. Use a minus sign to enter a negative value, if any. % d. What is the bond's yield to call? Round your answer to two decimal places. % e. How would the price of the bond be affected by a change in the going market interest rate? (Hint: Conduct a sensitivity analysis of price to changes in the going market interest rate for the bond. Assume that the bond will be called it and only if the going rate of interest falls below the coupon rate. This is an oversimplification, but assume it for purposes of this problem.) Round your answers to the nearest cent. e. How would the price of the bond be affected by a change in the going market interest rate? (Hint: Conduct a sensitivity analysis of price to changes in the going market interest rate for the bond. Assume that the bond will be called if and only if the going rate of interest falls below the coupon rate. This is an oversimplification, but assume it for purposes of this problem.) Round your answers to the nearest cent. Nominal market rate 096 Actual bond price $ 2% $ 4% $ 6% $ 8% $ 10% 12% 14% $ $ 16% $ f. Now assume the date is October 25, 2020. Assume further that a 13%, 10-year bond was issued on July 1, 2020, pays 8% $ 10% $ 12% $ $ 14% 16% $ f. Now assume the date is October 25, 2020. Assume further that a 13%, 10-year bond was issued on July 1, 2020, pays Interest semiannually (on January 1 and July 1), and sells for $1,080. Again, it may be called in 4 years from the date of issue at a call price of $1,050. Use your spreadsheet to find the bond's yield. Round your answers to two decimal places. % Yield to maturity: % Yield to call: Check My Work Reset Problem 1 F G fx B D E 1 Bond Valuation 2 3 Years to maturity 15 4 Number of coupon payment per year 2 5 Coupon rate 9% 6 Par value $1,000 7 Current price $1,080 8 Call price $1,050 9 Years until bond is cailable 4 10 11 a. Calculating the bond's yield to maturity Formulas 12 Penodic YTM #N/A 13 Annualized nominal YTM NA 14 15 b. Calculating the band's current yield 16 Current yield UNA 17 18 c. Calculating the bond's capital gain or loss yield 19 Capital gain/loss yield #N/A 20 21 d. Calculating the bond's yield to call + 22 Periodic YTC NA 23 Annualized nominal YTC WNIA 24 25 e. Assume that the bond will be called if and only if the going rate of interest fails below the Count 20 Conducting a sensitivity analysis of 25 e. Assume that the bond will be called if and only if the going rate of interest falls below the coupon rate. 26 Conducting a sensitivity analysis of price to changes in the going market interest rate for the bond 27 Nominal market rate 9% 28 Bond price if it's not called #N/A 29 Bond price if it's called #N/A 30 31 Nominal Bond price if 32 Actual market rate Not called Called 33 bond price (C27) $0.00 $0.00 34 0% 35 2% 36 4% 37 6% 38 8% 39 10% 40 12% 41 1496 42 16% 43 44 f. Calculating the bond's yield 45 Settlement date (today) 10/25/2020 46 Matunity date 7/1/2030 47 Call date 7/1/2024 48 Coupon rate 13% 49 Par value (% of par value) 100 5. Current prie 196 of nar value Formulas Nominal market rate Note (C27) 096 NA 2% NA 4% N/A 6% WN/A 8% NA 10% #N/A 1296 VA 14% N/A 16% DNA G File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be carefules from the Internet con contains. Unless you need to edit ater.to.in Protected C12 x fx B D E 14 f. Calculating the bond's yield 5 Settlement date (today) 10/25/2020 6 Maturity date 7/1/2030 7 Call date 7/1/2024 B Coupon rate 13% 9 Par value (% of par value) 100 0 Current price (% of par value) 0 1 Call price (% of par value) 0 2 2 Frequency (semiannual bonds) 1 3 Basis (360- or 365-day year) 4 Yield to maturity WNIA 5 Yield to call #N/A 7 8 9 0 1 Actual bond price Formulas Nominal market rate (C27) 0% 2% 4% 6% 8% 10% 12% 14% 16% Bond price if Not called Called $0.00 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A $0.00 #N/A #N/A #NA #N/A #N/A #N/A #N/A #N/A #N/A
