Question
Fred Jones plans to deposit $1000 at the end of each quarter into an account that pays 8% annually, compounded quarterly. What will be the
Fred Jones plans to deposit $1000 at the end of each quarter into an account that pays 8% annually, compounded quarterly. What will be the value of the account at the end of the 4th quarter (4th payment)?
The answer to the question above is given by the future value of an ordinary annuity. A template to compute this value is constructed in steps (a)-(c). Be sure to distinguish between labels, input data, and the formulas that do the calculations.
a. Start with a clean worksheet and enter the following data. Steps (b) and (c) complete the template. Note that each of a series of deposits is called a "payment" (denoted 'R' in cell A4).
| A | B | C | D | E |
1 | Problem #1 |
|
|
|
|
2 | Annuity Problem |
|
|
|
|
3 |
|
|
|
|
|
4 | PMT | $1,000 |
| Period | Ending Balance |
5 | r | 0.08 |
| 1 | =B4 |
6 | m | 4 |
| 2 | =E5+E5*$B$10+$B$4 |
7 | t | 1 |
| 3 |
|
8 | mt | =B6*B7 |
| 4 |
|
9 |
|
|
|
|
|
10 | r/m | =B5/B6 |
|
|
|
11 | FV |
|
|
|
|
b. The formula in cell E5 copies the deposit at the end of Quarter #1 from cell B4; the formula in E6 adds to the balance (E5) the interest on the balance together with another payment. Copy the formula in E6 to cells E7..E8 in order to determine the balance at the end of the remaining quarters.
c. In cell B11 we want to compute the total amount in cell E8 directly. Enter the formula =FV(B10, B8, -B4) in cell B11 and check that it gives the same value found in cell E8. NOTE the negative sign in the third argument. This is because Excel treats payments (out) as a negative cash flow. Try the formula without the negative sign and observe the difference. Algebra practice: In cell, F8 enter the formula for the Future Value of an Ordinary Annuity
FV = PMT [(1+r/m)mt-1]/[r/m] (using appropriate cells). The result should be the same as found in cell E8. Why do you think the built-in formula FV is provided?
Discussion: In a textbox explain what information is given by the different approaches used to compute the values in cells B11, E8, and F8. SAVE the worksheet.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started