Need assistance with these practice problems. Trying to understand which formulas to use for what.
B D E H 1 2 Problem 6-25 3 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Suppose you are given the information about the default-free, coupon-paying yield curve below. a. Use arbitrage to determine the yield to maturity of a two-year, zero-coupon bond. b. What is the zero-coupon yield curve for years 1 through 4? Par value of coupon bond $1,000 Maturity (years) 2 3 Coupon rate (annual payments) 0.00% 10.00% 6.00% 12.00% YTM 2.000% 3.908% 5.840% 5.783% Coupon amount $0.00 Price of coupon bond S926.19 S843.43 S798.61 Present value of interim coupon Implied price of zero-coupon bond Zero-coupon yield 1. Start Excel - completed. 2. In cell D18, by using a cell reference, input the zero-coupon yield for Year 1 (1 pt.). 3. In cell E14, by using cell references, calculate the coupon amount of the two-year coupon bond (1 pt.). 4. To calculate the coupon amounts of the three- and four-year coupon bonds, copy cell E14 and paste it onto cells F14:G14 (1 pt.). 5. In cell E15, by using cell references and the function PV, calculate the price of the two-year coupon bond (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 6. To calculate the price of the three- and four-year coupon bonds, copy cell E15 and paste it onto cells F15:G15 (1 pt.). 7. In cell E16, by using cell references and the function PV, calculate the present value of the two-year coupon bond in Year 1 (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 8. In cell E17, by using cell references, calculate the implied price of the two-year zero-coupon bond (1 pt.). Note: Refer to the values from Steps 5 and 7 in your calculations. 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Requirements 39 40 41 42 43 44 45 46 47 48 49 1 4 B D E H 1 2 Problem 6-25 3 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Suppose you are given the information about the default-free, coupon-paying yield curve below. a. Use arbitrage to determine the yield to maturity of a two-year, zero-coupon bond. b. What is the zero-coupon yield curve for years 1 through 4? Par value of coupon bond $1,000 Maturity (years) 2 3 Coupon rate (annual payments) 0.00% 10.00% 6.00% 12.00% YTM 2.000% 3.908% 5.840% 5.783% Coupon amount $0.00 Price of coupon bond S926.19 S843.43 S798.61 Present value of interim coupon Implied price of zero-coupon bond Zero-coupon yield 1. Start Excel - completed. 2. In cell D18, by using a cell reference, input the zero-coupon yield for Year 1 (1 pt.). 3. In cell E14, by using cell references, calculate the coupon amount of the two-year coupon bond (1 pt.). 4. To calculate the coupon amounts of the three- and four-year coupon bonds, copy cell E14 and paste it onto cells F14:G14 (1 pt.). 5. In cell E15, by using cell references and the function PV, calculate the price of the two-year coupon bond (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 6. To calculate the price of the three- and four-year coupon bonds, copy cell E15 and paste it onto cells F15:G15 (1 pt.). 7. In cell E16, by using cell references and the function PV, calculate the present value of the two-year coupon bond in Year 1 (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 8. In cell E17, by using cell references, calculate the implied price of the two-year zero-coupon bond (1 pt.). Note: Refer to the values from Steps 5 and 7 in your calculations. 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Requirements 39 40 41 42 43 44 45 46 47 48 49 1 4
