5.4 Solving Annuity Problems with Spreadsheets
In the previous sections we have seen how spreadsheets can illustrate what we calculate
with annuity formulas while also allowing us to “crunch the numbers” for situations that
we could not reasonably handle with our formulas. In this section, we will further explore
the use of spreadsheets to solve problems that we would not be able to otherwise.Example 5.4.1 Suppose that I deposit $3,000 each year into an investment account
that earns 9%. How long will it take before my account balance reaches $1,000,000?Our annuity formulas for future value don’t give us any reasonable way to fi nd the term any
more than we could for present value. We can, though, adapt the approach of Section 5.3
to a future value table to answer this question. Using the spreadsheet template saved from
Section 5.2, we can adjust the rate and payments to fi t this question, and then look for the
year when the balance goes over $1,000,000.Rows Omitted42 40 $927,199.65 $83,447.97 $3,000.00 $1,013,647.62
41 30 $847,989.59 $76,310.06 $3,000.00 $927,199.65
2
3
4
A B C D
Year Interest Earned Payment Ending Balance
1 $0.00 $3,000.00 $3,000.00Starting Balance
$0.005
2 $3,000.00 $270.00 $3,000.00 $6,270.00
3 $6 270 00 $564 30 $3 000 00 $9 034 30
E
From this table, we see that in year 40 my account balance will reach $1,000,000.Similarly, we can deal with more complicated situations by combining this idea with the
work we did in Section 5.2.Example 5.4.2 Miyako has $47,593 in her retirement account. She plans to deposit
$2,000 this year, and will increase her payment by 3% each year. If her account earns
9%, how long will it be before her account balance reaches $500,000?E. Additional Exercises- All of the examples we have been considering have had interest rates that do not change. Many loans do have fi xed
rates, which don’t change, but other loans carry adjustable rates, which may change.
Suppose that you take out a 30-year adjustable rate mortgage with a balance of $208,900. The interest rate is 3.99%
for the fi rst year, and then increases by 1% (to 4.99%) in the second year, and in each additional year until the rate
reaches 10.99%, where it remains for the rest of the mortgage’s term. If you pay $1,000 a month, how long will it take
to pay off this loan? If you pay $2,000 a month, how long will it take? - As mentioned in the text of this section, some loans do not allow negative amortization. If a payment is made that is not
enough to cover the interest due in a month, the entire payment is applied toward the interest owed. While interest continues
to accumulate on the balance, the unpaid interest is not included in that balance. Instead, the unpaid interest is considered
“on hold”; future payments are not applied to any later interest or to reduce the loan’s principle until all on-hold interest is paid.
Suppose that Ed and Carol owe $96,575.18 on their mortgage. The interest rate is 6.75%. For the next 3 months, they pay
only $250. After that, they increase their monthly payments to $1,000 each month and keep this up until the loan is paid off.
Their loan does not allow negative amortization; too-small payments are handled as described at the start of this
exercise. Also, any payment that is less than the scheduled payment is assessed a fee of $35.
How long will it take for Ed and Carol to pay off their mortgage?
Copyright © 2008, The McGraw-Hill Companies, Inc.
5.4 Solving Annuity Problems with Spreadsheets 235