112 N Problem 5.29 Complete the steps below using cell references to ghen data or previous cakulations. 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, a. Calculate the present value of an investment that pays $1.000 in two years and $2,000 in five years for certain b. Calculate the present value of receiving $500 per year, with certainty, at the end of the next five years. To find the rates for the missing years in the table, lincarly interpolate between the years for which you do know the rates. (For example, the rate in year 4 would be the average of the rate in year 3 and year 5.) Calculate the present value of receiving 52,300 per year, with certainty, for the next 20 years. Infer rates for the missing years using lincar interpolation Suppose the term structure of risk-free interest rates is as shown below 6 TO Term Cycars) Rate 2 2.41% 3.0 3.7696 2. Calculate the pret value of an investment that pays $1,000 in two years and $2,000 in five years for certain Cash Flow in Year 2 Cash Flow in Year Present Vale 52.000 50.00 S0.00 1. Calculate the present value of receiving $500 per year, with certainty, at the end of the next five years. To find the rates for the missing years in the tale Incarly interpolate between the years for which you do know the rates. (For example, the rate in year 4 would be the 5.29 U w 17 18 19 20 16 14 15 13 11 12 5% N 1. Calculate the present value of receiving 5500 per year, with certainty, at the end of the five years. To find the rates for the missing years in the table, incarly incrpolate between the years for which you do know the rates. (For comple, the rate in year 4 would be the average of the rate in year and year 5.) Cash Flows S500 Present Value 13 Calculate the present value of neching 52 300 per year, with certainty, for the next 20 years. Infor rates for the missing years using linear interpelatie Cash Flows 32.300 Regelmess 2. In dl 16, by wing cell references and the function PV. calculate the percent val of receiving 51,000 in two years (p) Note: The output of the expression or function you typed in this cell is expected some 3. In cell 116, by wine cell references and the function IV, calculate the present value of receiving 52,000 in the years (pl.) Note The put of the express or function you typed in this cell is expected as ative number 4. In cell 16, by wing cell reference calculate the present value of the investment) Note Refer to the values from Steps 2 and 3 in your calculations 5. In cell by using od references, calculate the risk-free interest rate for Year 4 p.) 6. In cell J11 by wing references, calculate the ristrerest rate for Year 6 (1 p.) 7. In cells LIMIT ly ting ell references, calate the risk-free interest rates for years and 9 ) R. In cells OWWII, by wing cell references, calculate the risk-free interest rates for Years 11 through 19 pt.) 9. In cell 20. by using cell references and the function PV. calculate the present value of $500 received for Year 1 (1 p.) Note: The output of the expression or function you typed in this collis espected as a positive number 28 29 32 12 40 27 Requirements 1. Start Excel - completed 2. In cell F16, by using cell references and the function PV, calculate the present value ofreceiving $1,000 in two years (pt.) 30 Note: The output of the expression or function you typed in this cell is expected as a positive number 31 3. In cell 116, by using cell references and the function PV, calculate the present value of receiving $2,000 in five years (1 pt.) Note: The output of the expression or function you typed in this cell is expected as a positive number. 32 4. In cell D16, by using cell references, calculate the present value of the investment (1 p.). Note: Refer to the values from Steps 2 and 3 in your calculations. 15 5. In cell 1111, by using cell references, calculate the risk-free interest rate for Year 4 (pt) 6. In cell J11, by using cell references, calculate the risk-free interest rate for Year 6( pt.) 7. In cells L11:M11, by using cell references, calculate the risk-free interest rates for Years 8 and 9 (2 pt.) 8. In cells 011:W11, by using cell references, calculate the risk free interest rates for Years 11 through 19 (9 pt.) 19 9. In cell E20, by using cell references and the function PV, calculate the present value of $500 received for Year 1 (pt) Note: The output of the expression or function you typed in this cell is expected as a positive number 10 To calculate the present values of $500 received at the end of Years 2 through 5, copy cell E20 and paste it onto cells F20:120 (1 pr.). Note: The output of the expression or function you typed in this cell is expected as a positive number. Use a cell reference to the risk-free interest rate for Year 4 from Stop 5 in your calculations 11. In cell D20, by wing cell references and the function SUM, calculate the present value of receiving 5500 per year (pl.) Note: Refer to the values from Steps 9 and 10 in your calculations 12. In cell E24, by using cell references and the function PV, calculate the present Value of 52,300 received for Year 1 (1 p.) Note: The output of the expression of function you typed in this cell is expected as a positive number 13. To calculate the present values of $2,300 received at the end of Years 2 through 20, copy cell E24 and paste it onto cells F24:X24 pl.) Note: The output of the expression or function you typed in this cell is expected as a positive number. Use cell references to the rok free interest rates from Steps 5.6.7 and 8 in your calculations 14. In cell 024 by using cell references and the function SUM. calculate the present value ofreceiving 52,300 per year (pt). Note: Relor to the values from Steps 12 and 13 in your calculations 15. Save the workbook. Close the workbook and then exit Excel Submit the workbook as directed 5-29 15 4 50 112 N Problem 5.29 Complete the steps below using cell references to ghen data or previous cakulations. 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, a. Calculate the present value of an investment that pays $1.000 in two years and $2,000 in five years for certain b. Calculate the present value of receiving $500 per year, with certainty, at the end of the next five years. To find the rates for the missing years in the table, lincarly interpolate between the years for which you do know the rates. (For example, the rate in year 4 would be the average of the rate in year 3 and year 5.) Calculate the present value of receiving 52,300 per year, with certainty, for the next 20 years. Infer rates for the missing years using lincar interpolation Suppose the term structure of risk-free interest rates is as shown below 6 TO Term Cycars) Rate 2 2.41% 3.0 3.7696 2. Calculate the pret value of an investment that pays $1,000 in two years and $2,000 in five years for certain Cash Flow in Year 2 Cash Flow in Year Present Vale 52.000 50.00 S0.00 1. Calculate the present value of receiving $500 per year, with certainty, at the end of the next five years. To find the rates for the missing years in the tale Incarly interpolate between the years for which you do know the rates. (For example, the rate in year 4 would be the 5.29 U w 17 18 19 20 16 14 15 13 11 12 5% N 1. Calculate the present value of receiving 5500 per year, with certainty, at the end of the five years. To find the rates for the missing years in the table, incarly incrpolate between the years for which you do know the rates. (For comple, the rate in year 4 would be the average of the rate in year and year 5.) Cash Flows S500 Present Value 13 Calculate the present value of neching 52 300 per year, with certainty, for the next 20 years. Infor rates for the missing years using linear interpelatie Cash Flows 32.300 Regelmess 2. In dl 16, by wing cell references and the function PV. calculate the percent val of receiving 51,000 in two years (p) Note: The output of the expression or function you typed in this cell is expected some 3. In cell 116, by wine cell references and the function IV, calculate the present value of receiving 52,000 in the years (pl.) Note The put of the express or function you typed in this cell is expected as ative number 4. In cell 16, by wing cell reference calculate the present value of the investment) Note Refer to the values from Steps 2 and 3 in your calculations 5. In cell by using od references, calculate the risk-free interest rate for Year 4 p.) 6. In cell J11 by wing references, calculate the ristrerest rate for Year 6 (1 p.) 7. In cells LIMIT ly ting ell references, calate the risk-free interest rates for years and 9 ) R. In cells OWWII, by wing cell references, calculate the risk-free interest rates for Years 11 through 19 pt.) 9. In cell 20. by using cell references and the function PV. calculate the present value of $500 received for Year 1 (1 p.) Note: The output of the expression or function you typed in this collis espected as a positive number 28 29 32 12 40 27 Requirements 1. Start Excel - completed 2. In cell F16, by using cell references and the function PV, calculate the present value ofreceiving $1,000 in two years (pt.) 30 Note: The output of the expression or function you typed in this cell is expected as a positive number 31 3. In cell 116, by using cell references and the function PV, calculate the present value of receiving $2,000 in five years (1 pt.) Note: The output of the expression or function you typed in this cell is expected as a positive number. 32 4. In cell D16, by using cell references, calculate the present value of the investment (1 p.). Note: Refer to the values from Steps 2 and 3 in your calculations. 15 5. In cell 1111, by using cell references, calculate the risk-free interest rate for Year 4 (pt) 6. In cell J11, by using cell references, calculate the risk-free interest rate for Year 6( pt.) 7. In cells L11:M11, by using cell references, calculate the risk-free interest rates for Years 8 and 9 (2 pt.) 8. In cells 011:W11, by using cell references, calculate the risk free interest rates for Years 11 through 19 (9 pt.) 19 9. In cell E20, by using cell references and the function PV, calculate the present value of $500 received for Year 1 (pt) Note: The output of the expression or function you typed in this cell is expected as a positive number 10 To calculate the present values of $500 received at the end of Years 2 through 5, copy cell E20 and paste it onto cells F20:120 (1 pr.). Note: The output of the expression or function you typed in this cell is expected as a positive number. Use a cell reference to the risk-free interest rate for Year 4 from Stop 5 in your calculations 11. In cell D20, by wing cell references and the function SUM, calculate the present value of receiving 5500 per year (pl.) Note: Refer to the values from Steps 9 and 10 in your calculations 12. In cell E24, by using cell references and the function PV, calculate the present Value of 52,300 received for Year 1 (1 p.) Note: The output of the expression of function you typed in this cell is expected as a positive number 13. To calculate the present values of $2,300 received at the end of Years 2 through 20, copy cell E24 and paste it onto cells F24:X24 pl.) Note: The output of the expression or function you typed in this cell is expected as a positive number. Use cell references to the rok free interest rates from Steps 5.6.7 and 8 in your calculations 14. In cell 024 by using cell references and the function SUM. calculate the present value ofreceiving 52,300 per year (pt). Note: Relor to the values from Steps 12 and 13 in your calculations 15. Save the workbook. Close the workbook and then exit Excel Submit the workbook as directed 5-29 15 4 50