Problem 5-29 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. 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, linearly 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.) c. Calculate the present value of receiving $2,300 per year, with certainty, for the next 20 years. Infer rates for the missing years using linear interpolation. Suppose the term structure of risk-free interest rates is as shown below: 8 9 11 12 13 14 15 16 17 18 19 Term (years) Rate 1 1.99% 2 2.41% 3 2.74% 4 3.03% 5 3.32% 6 3.54% 7 3.76% 10 4.13% 20 4.93% a. Calculate the present 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 5 Present Value $1,000 $2,000 $2,940.27 $953.49 $1,906.98 $993.39 $1,986.79 $1,986.79 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, linearly 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.) $500 Cash Flows Present Value c. Calculate the present value of receiving $2,300 per year, with certainty, for the next 20 years. Infer rates for the missing years using linear interpolation. $2,300 Cash Flows Present Value 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, linearly 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.) c. Calculate the present value of receiving $2,300 per year, with certainty, for the next 20 years. Infer rates for the missing years using linear interpolation. Suppose the term structure of risk-free interest rates is as shown below: 3 4 5 6 8 10 11 12 13 14 15 16 17 18 19 20 Term (years) Rate 1 1.99% 2 2.41% 7 3.76% 2.74% 3.03% 3.32% 3.54% 4.13% 4.93% a. Calculate the present 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 5 Present Value $1,000 $2,000 $2,940.27 $953.49 $1,906.98 $993.39 $1,986.79 $1,986.79 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, linearly 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.) $500 Cash Flows Present Value c. Calculate the present value of receiving $2,300 per year, with certainty, for the next 20 years. In fer rates for the missing years using linear interpolation. $2,300 Cash Flows Present Value Requirements 1. Start Excel - completed. 2. In cell F16, by using cell references and the function PV, calculate the present value of receiving $1,000 in two years (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 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. 4. In cell D16, by using cell references, calculate the present value of the investment (1 pt.). Note: Refer to the values from Steps 2 and 3 in your calculations. 5. In cell H11, by using cell references, calculate the risk-free interest rate for Year 4 (1 pt.). 6. In cell J11, by using cell references, calculate the risk-free interest rate for Year 6 (1 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.). 9. In cell E20, by using cell references and the function PV, calculate the present value of $500 received for Year 1 (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 pt.). 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 Step 5 in your calculations. 11. In cell D20, by using cell references and the function SUM, calculate the present value of receiving $500 per year (1 pt.). 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 $2,300 received for Year 1 (1 pt.). Note: The output of the expression or 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 (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. Use cell references to the risk-free interest rates from Steps 5,6,7, and 8 in your calculations. 14. In cell D24, by using cell references and the function SUM, calculate the present value of receiving $2,300 per year (1 pt.). Note: Refer 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. Problem 5-29 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. 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, linearly 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.) c. Calculate the present value of receiving $2,300 per year, with certainty, for the next 20 years. Infer rates for the missing years using linear interpolation. Suppose the term structure of risk-free interest rates is as shown below: 8 9 11 12 13 14 15 16 17 18 19 Term (years) Rate 1 1.99% 2 2.41% 3 2.74% 4 3.03% 5 3.32% 6 3.54% 7 3.76% 10 4.13% 20 4.93% a. Calculate the present 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 5 Present Value $1,000 $2,000 $2,940.27 $953.49 $1,906.98 $993.39 $1,986.79 $1,986.79 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, linearly 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.) $500 Cash Flows Present Value c. Calculate the present value of receiving $2,300 per year, with certainty, for the next 20 years. Infer rates for the missing years using linear interpolation. $2,300 Cash Flows Present Value 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, linearly 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.) c. Calculate the present value of receiving $2,300 per year, with certainty, for the next 20 years. Infer rates for the missing years using linear interpolation. Suppose the term structure of risk-free interest rates is as shown below: 3 4 5 6 8 10 11 12 13 14 15 16 17 18 19 20 Term (years) Rate 1 1.99% 2 2.41% 7 3.76% 2.74% 3.03% 3.32% 3.54% 4.13% 4.93% a. Calculate the present 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 5 Present Value $1,000 $2,000 $2,940.27 $953.49 $1,906.98 $993.39 $1,986.79 $1,986.79 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, linearly 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.) $500 Cash Flows Present Value c. Calculate the present value of receiving $2,300 per year, with certainty, for the next 20 years. In fer rates for the missing years using linear interpolation. $2,300 Cash Flows Present Value Requirements 1. Start Excel - completed. 2. In cell F16, by using cell references and the function PV, calculate the present value of receiving $1,000 in two years (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 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. 4. In cell D16, by using cell references, calculate the present value of the investment (1 pt.). Note: Refer to the values from Steps 2 and 3 in your calculations. 5. In cell H11, by using cell references, calculate the risk-free interest rate for Year 4 (1 pt.). 6. In cell J11, by using cell references, calculate the risk-free interest rate for Year 6 (1 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.). 9. In cell E20, by using cell references and the function PV, calculate the present value of $500 received for Year 1 (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 pt.). 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 Step 5 in your calculations. 11. In cell D20, by using cell references and the function SUM, calculate the present value of receiving $500 per year (1 pt.). 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 $2,300 received for Year 1 (1 pt.). Note: The output of the expression or 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 (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. Use cell references to the risk-free interest rates from Steps 5,6,7, and 8 in your calculations. 14. In cell D24, by using cell references and the function SUM, calculate the present value of receiving $2,300 per year (1 pt.). Note: Refer 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