326 Chapter 7 Retirement Plans
Projections Assuming Payments Change
at a Different Rate than InflationWe assumed above that your earnings, and with them your ability to fund your retirement
account, will rise at a rate that keeps pace with inflation. Hopefully that assumption isn’t
too optimistic; you would certainly hope that your earnings will at least rise fast enough
so that you don’t lose ground to rising prices. However, you probably hope for more, that
your earnings will rise at a faster rate than inflation, and that your buying power actu-
ally increases as your career advances. If that happens, hopefully you will be able to also
increase your deposits at a similar rate. That would be helpful, because it would allow
smaller deposits now to be made up for by increases later on.
How do we handle this? Whether we work in actual dollars or in today’s dollars, the
payments will be changing, so the annuity formulas don’t work. The only realistic way
to handle this is to work things out on a spreadsheet. We can do this by using either
actual or today’s dollars; since using today’s dollars will not help us here the way it
did in Example 7.3.3 it is probably more straightforward to go back to working in actual
dollars.Example 7.3.4 Rework the question from Examples 7.3.2 and 7.3.3, this time
assuming that your payments increase at a 5% annual rate.In Example 7.3.2 we found that the future value you will actually need is $3,959,260. We set
up a spreadsheet just as we did in Chapter 5. Since we don’t know what the payment needs
to be, we will just enter an educated guess, such as $100.00. Since the payments are weekly,
we’ll also need to change the header from Year to Week, and the interest earned column
formula needs to be adjusted to divide by 52, since things are weekly. This new formula for
the interest earned cell needs to be copied on down. For the moment, let’s copy that cell
down as far as row 106 (the end of the second year).In row 55, the fi rst row of the second year, the payment cell should be changed to
Round(1.05*D54,2). This will increase the payment in that month and every subsequent
month to $105.00.Since the 52 rows of the second year are set up the way we want all the remaining years to
be, we will highlight the cells from A55 through E106, and copy those. Then highlight A107
through A2082, and click on Paste, which will duplicate the second year’s cells into the cells
for the remaining 38 years.We then check to see the balance shown at the end of year 40. To be able to see this easily
without having to scroll through a couple of thousand rows, you may want to hide the rows
between the fi rst and last.Rows OmittedRows Omitted1 Rate: 9.10%
2
3A B C D
Week Interest Earned Payment Ending Balance
1 $0.00 $100.00 $100.00Starting Balance
$0.00
4 2 $100.00 $0.18 $100.00 $200.18
5 3 $200.18 $0.35 $100.00 $300.53