- a. Suppose I deposit $1,500 per year for 10 years into an account earning 6%. For the next 15 years I increase my deposits
to $1,800 per year, then for the next 5 years after that I decrease my deposits to $960 per year. Find my total future value.
b. Suppose I deposit $125 per month for 10 years into an account earning 6%. For the next 15 years I increase my deposits to
$150 per month, then for the next 5 years after that I decrease my deposits to $80 per month. Find my total future value.
E. Additional Exercises
- Suppose that I deposit $2,000 at the start of each year for 25 years. My account earns 7.5%. Create a spreadsheet to
calculate my account’s future value. - Suppose that you set up an investment account today, and the following occurs over the next 40 years:
- You deposit $750 a year for 10 years, then $1,250 a year for the next 10 years. In the 21st year you deposit $2,000,
and increase your deposits by 5% every year thereafter. - For the fi rst 30 years you invest aggressively and earn a rate of 11% on your investments. In the last 10 years, you
move your money to lower risk investments that earn a lower rate of 7%.
- You deposit $750 a year for 10 years, then $1,250 a year for the next 10 years. In the 21st year you deposit $2,000,
Find the future value of this account.
- Alexander has set up a 401(k) account at work. He is paid every other week, and this year he will deposit $30 per
paycheck into his 401(k). Next year, he plans to increase his biweekly deposit by 4% (to $31.20 per week), and every
year thereafter he will increase his deposit by 4%. If his account earns 9.05%, fi nd his future value.
228 Chapter 5 Spreadsheets
5.3 Amortization Tables with Spreadsheets
In Section 4.5 we talked about amortization tables, which illustrate how the payments
made on a loan kill off the loan balance, step by step. While the calculations done to com-
plete an amortization table aren’t all that complicated, there are a lot of them, which makes
building an entire amortization table with a pencil, paper, and calculator an unrealistic
project. It should be apparent, though, that this is exactly the sort of task that a spreadsheet
can handle beautifully.Example 5.3.1 Construct an amortization table spreadsheet for a $5,000, 2-year
loan at 8.59% interest. (Assume payments are monthly.)As a fi rst step, we’ll set up columns, using the same format that we used in Section 4.5. Also,
even though this problem doesn’t ask us to consider different interest rates, we’ll store the
interest rate in a cell at the top as we did in Example 5.2.5 so that if we want to change the
interest rate later, we can. We also may fi nd it useful to store the initial loan amount in a cell,
so we’ll do that at the top as well.1 Rate: 8.59% Initial Balance: $5,000.00
2A B C D
Month Payment To Interest To Principal Ending BalanceE
Next, we can calculate the amount of each payment, using the annuity formulas from
Chapter 4. That works out to be $227.48. In A3 we enter “1”, in B3 we enter “227.48”,
in C3 we enter “ROUND(D1*B1/12,2)”, in D3 “B3-C3”, and in E3 “D1-D3”. In the
next row we enter in A4 “A31”, in B4 “B3”, in C4 “ROUND(E3*$B$1/12,2)”, incb