Problem:
Tom Kendrick invests $1,000 in a CD at 10% a year for five years. He withdraws the interest at the end of each year.
What amount does he have at the end of five years assuming that he does not spend or invest the interest?
The formula to calculate the future value of an investment with compound interest is given by the formula:
![\[ FV = PV \times (1 + r)^t \]](https://excelcalculations.refrigeratordiagrams.com/wp-content/ql-cache/quicklatex.com-f4c18d3e2d116405fe836038639b69d8_l3.svg "Rendered by QuickLaTeX.com")
Where:
- FV is the future value of the investment,
- PV is the present value or initial investment,
- r is the annual interest rate (as a decimal),
- t is the number of years the money is invested or borrowed for.
In this case, Tom Kendrick invests $1,000 at an annual interest rate of 10% for five years, and he withdraws the interest at the end of each year. The formula for calculating the future value in this scenario is:
Let’s create an Excel table to calculate this. You can use the following steps:
- Create an Excel Table:
| Year | Initial Investment | Interest Rate | Withdrawn Interest | Future Value |
|---|---|---|---|---|
| 1 | $1,000.00 | 10% | =B2*C2 | =B2+D2 |
| 2 | =E2 | 10% | =C2*(B3+E2) | =E3+D3 |
| 3 | =E3 | 10% | =C2*(B4+E3) | =E4+D4 |
| 4 | =E4 | 10% | =C2*(B5+E4) | =E5+D5 |
| 5 | =E5 | 10% | =C2*(B6+E5) | =E6+D6 |
- Fill Down the Formulas:
Click on the small square at the bottom right corner of cell B2 (the square is called the fill handle). Drag it down to fill the formulas for all five years.
- Understand the Results:
The “Withdrawn Interest” column calculates the interest withdrawn at the end of each year. The “Future Value” column calculates the total amount at the end of each year.
- Final Result:
The final amount at the end of the fifth year is in cell E6.
Now, let’s calculate it:
- Initial Investment: $1,000
- Annual Interest Rate: 10% (0.10 as a decimal)
- Number of Years: 5
![\[ FV = $1,000 \times (1 + 0.10)^5 \]](https://excelcalculations.refrigeratordiagrams.com/wp-content/ql-cache/quicklatex.com-937ebc565be841839bb800d4bd65b19a_l3.svg "Rendered by QuickLaTeX.com")
![\[ FV \approx $1,610.51 \]](https://excelcalculations.refrigeratordiagrams.com/wp-content/ql-cache/quicklatex.com-8d3bc12b6b4c670cbf2ac955bdc8a22a_l3.svg "Rendered by QuickLaTeX.com")
So, Tom Kendrick would have approximately $1,610.51 at the end of five years if he doesn’t spend or invest the interest.
Other Approaches:
If using Excel functions directly:
In cell E2, you can enter the following formula:
=PV(0.10, 5, 0, -1000)
This formula will give you the future value directly.
