Questions 1 through 4 require you to type in the Excel built-in formulas to calculate the answer for each problem. Examples of the Excel formulas are included in the PDF of the video lectures for TVM-Future Value and TVM-Present Value. The Excel formulas must be entered into the Excel cells highlighted in light blue.
Question 5 is primarily an Excel functionality exercise. Complete the missing items in the table that are highlighted in light blue. The purpose of this exercise is to illustrate how to use the Excel built-in TVM functions and also how to calculate the same results using the equivalent algebraic formula. The exercise can be completed in a few seconds (Yes, seconds, not minutes) by appropriately copying the formulas from the portions of the table already filled in. If you are not familiar with Excel try to learn how to copy/paste formulas from cells and what ‘absolute cell reference’ and ‘relative cell reference’ mean.
Please solve the following Time Value of Money problems by using Excel’s built-in functions.
1. What will be the ending balance for an account with an initial deposit of $10,000 earning 9% per year for 35 years?
Enter solution using Excel fomula here:
2. If you estimate you will need to have $750,000 in your retirement account when you retire in 30 years, how much must you deposit today to reach that goal assuming you can earn an annual return of 8% over that period?
Enter solution using Excel fomula here:
3. How many years will it take for $15,000 to grow into $200,000 assuming an annual return of 8.5%?
Enter solution using Excel fomula here:
4. Your firm has paid $1 million for a project that is expected to earn a lump sum payment of $1.5 million, and nothing more, in ten years. What discount rate would make the current value of the project equal to the amount the firm paid?
Enter solution using Excel fomula here:
5. Complete the table below that calculates the Future Value of $10,000 invested at 7% per year using periods from 1 to 10 years. The table also illustrates the value today of $10,000 to be received in the future from 1 to 10 years later using a discount rate of 5%. (Note: table is to be completed by appropriately copying the Excel formulas. Using Excel copy/paste functionality it should take just a few seconds to do it).
Number of Future Value Future Value Present Value Present Value
Periods (Excel Formula) (Algebra Formula) (Excel Formula) (Algebra Formula)
1 $10,700.00 $10,700.00 $9,523.81 $9,523.81
2 $11,449.00 $11,449.00 $9,070.29
3
4
5
6
7
8
9
10 $6,139.13 $6,139.13
Future Value Initial Present Value Future
rate Amount Disc. Rate Amount
7% $10,000.00 5% $10,000.00