| BUS3062 Fundamentals of Finance |
| Time Value of Money (TVM) - Analyzing Single Cash Flows |
| Practice Problem Worksheet Using Excel Formulas |
|
|
|
|
|
|
|
|
|
|
| Be advised that annual compounding is used in each of these practice problems, unless more frequent compounding is otherwise specifically disclosed. |
| Future Value |
| Future Value Problem 1) Here is a simple future value problem: |
| How much would you have in your savings account after 5 years if you deposited $1,000 today and it earned 6% interest per year? The correct answer is $1,338.23 determined below. |
| Solution: First, identify this is a future value problem (this is determined because we are asked how much money we will have (in the future.) That is our clue that this is a FV problem. |
| Next identify the known variables: |
| The interest rate: I = 6% (or 0.06 expressed as a decimal) |
| The number of time periods: N = 5 |
| The present value: PV = 1,000 (note that you will enter PV as a negative number below) |
| Next using the Excel, Formulas, Financial, FV formula that reads as follows: |
| FV = (Int rate, Number of periods, next enter 0 because there are no recurring payments for this type of problem, enter the Present Value as a negative number) |
| inputting the numbers into the Excel FV formula in the exact order prescribed by the formula (click on Cell B32 to see the contents of the cell as inputted): |
| FV = |
$1,338.23 |
| Future Value Problem 2 |
| How much would you have after 10 years if you deposited $1,500 today and it earned 5% interest per year? The correct answer is $2,443.34 determined below. |
| FV = |
$2,443.34 |
| Present Value |
| Present Value Problem 1 |
| How much would you need to deposit today if you wanted to end up with $10,000 in your savings account in 8 years and the deposit would earned 7% interest per year? The correct answer is $5,820.09 determined below. |
| Solution: First, identify this is a present value problem (this is determined because we are asked how much money we need to deposit (today.) That is our clue that this is a PV problem. |
| Next identify the known variables: |
| The interest rate: I = 7% (or 0.07 expressed as a decimal) |
| The number of time periods in years: N =8 |
| The future value: FV = 10,000 |
| Next using the Excel, Formulas, Financial, PV formula that reads as follows: |
| PV = (Int rate, Number of periods, next enter 0 because there are no recurring payments for this type of problem, enter the Future Value) |
| Inputting the numbers into the Excel PV formula in the exact order prescribed by the formula (click on Cell B68 to see the contents of the cell as inputted): |
| PV = |
($5,820.09) |
(Note that PV is solved showing it as a negative number. This is because of the formula that is used to solve the equation and represents that $5,820.09 must be paid out (an outflow) in order to receive the 10,000 (inflow) amount in the future. Even though the Excel mathematical equation solves the PV as a negative number, one should simply refer to the PV as $5,820.09 without reference to it as a negative number.) |
| Present Value Problem 2 |
| What is the present value of $15,000 payment planned to be made in 12 years when the discount rate is 7%? The correct answer is $6, 660.18 determined below. |
| PV = |
($6,660.18) |
| Time Period |
| Solving for Time: |
| Problem 1: Here is a simple time value of money problem solving for number of periods: |
| How long would it take a deposit of $2,500 to grow to $3,500 assuming it earned 4.5% annual interest? The correct answer is 7.64 years determined below. |
| Solution: First, identify this is a time value of money problem attempting to solve for time. (Such time period problems are usually easy to identify and often state "how long will it take.") |
| Next identify the known variables: |
| The interest rate: I = 4.5% (or 0.045 expressed as a decimal) |
| The present value: PV = 2,500 (note you will enter PV as a negative number below) |
| The future value: FV = 3,500 |
| Next using the Excel, Formulas, Financial, NPER formula that reads as follows: |
| NPER = (Int rate, next enter 0 because there are no recurring payments for this type of problem, enter the Present Value amount as a negative number, enter the future value amount) |
| inputting the numbers into the Excel NPER formula in the exact order prescribed by the formula (click on Cell B109 to see the contents of the cell as inputted): |
| NPER = |
7.64 |
years |
| Problem 2 |
| How long would it take a deposit of $150 to grow to $300 (another way of saying this would be "how long would it take any deposit to double") assuming it earned 7.5% annual interest? The correct answer is 9.58 years determined below. |
| NPER = |
9.58 |
years |
| Annual Rate of Return |
| Solving for Interest Rate |
| Problem 1: Here is a simple problem solving for interest rate (annual rate of return): |
| What interest rate must you earn to turn your $3,000 deposit into $4,000 within 6 years? The correct answer is 4.91% determined below. |
| Solution: First, identify this is a time value of money problem attempting to solve for a requested interest rate. Such interest rate problems often state "what rate of interest must be earned..." or similar wording. |
| Next identify the known variables: |
| The number of time periods in years: N = 6 |
| The present value: PV = 3,000 (note that you will enter PV as a negative number below) |
| The future value: FV = 4,000 |
| Next using the Excel, Formulas, Financial, RATE formula that reads as follows: |
| RATE = (Number of time periods, next enter 0 since there are no recurring payments for this problem, enter the Present Value amount as a negative number, enter the Future Value amt.) |
| inputting the numbers into the Excel RATE formula in the exact order prescribed by the formula (click on Cell B148 to see the contents of the cell as inputted): |
| RATE = |
4.91% |
(Make sure you change the number of decimals in the formula cell so you show at least 4 decimal places as is shown to the left, otherwise, your response would show 5% and that is not sufficiently exact and would be marked as incorrect.) |
| RATE 2) Here is a simple time value of money solving for interest rate practice problem: |
| If you had $10,000 and needed it to grow to $12,000 within 3 years, what annual rate of return would you need to receive? The correct answer is 6.27% determined below. |
| NPER = |
6.27% |
(Make sure you change the number of decimals in the formula cell so you show at least 4 decimal places as is shown to the left, otherwise, your response would show 6% and that is not sufficiently exact and would be marked as incorrect.) |
| Differing Interest Rates |
| Solving for Future Value with differing rates of interest: |
| Problem 1: Here is a simple FV problem solving with differing interest rates: |
| A $1,500 deposit you make today is expected to earn 3% the first year, 4% the second year, 4.5% the third year, and 5% the fourth year. How much would you have at the end of the fourth year? The correct answer is 1,763.06 determined below. |
| Solution: First, identify this is a future value problem with differing rates of interest. |
| Next, the answer is calculated by taking the original deposit and multiplying it by (1+ Interest Rate) for each annual period's interest rate, algebraically shown as follows: ($1,500 original deposit x 1.03 x 1.04 x 1.045 x 1.05 = $1,763.06 (also click on Cell B181 to see the contents of the cell as solved algebraically without the use of an Excel Formula function): |
| Answer = |
$ 1,763.06 |
| Problem 2 |
| A deposit of $850 earns the following interest rates: 5% the first year, 5.5% the second year, and 6% the third year. What would be third year future value? The correct answer is $998.08 determined below. |
| Answer = |
$ 998.08 |
| Compounding Frequency |
| Compounding Frequency: |
| Each of the above problems assumes interest is compounded on an annual basis. However, in actuality, many financial obligations are compounded more frequently, such as bond interest (normally semi-annual frequency), many bank CDs (quarterly, or more frequent compounding), home mortgage and car financing (monthly frequency.) |
Sweet17 
W2.xlsx
