The Daily Dow
Thursday, October 9, 1997
by Robert Sheard
LEXINGTON, KY (Oct. 9, 1997) -- It's been pointed out to me that I write frequently about "what-if" scenarios -- what if Johnny saved $2,000 a year for 40 years at 17%, what if he invested in stocks instead of index funds, what if Suzy is wearing red socks on Tuesday and sits next to Jack in biology class while he's wearing a blue jacket -- you know, the kind of questions that brought you to tears when you took the SAT the morning after the prom (actually happened to me).
What I don't usually include in such pieces are the nuts and bolts behind the calculations. So for those readers who would like to experiment with their own "what-if" scenarios, I'd like to cover the basics of these calculations today.
All of the calculations I do can be performed by hand if you like, but it's a whole lot faster (and sometimes more accurate if you're full of caffeine) if you have a spreadsheet program. Let's walk through a couple of simple scenarios.
Scenario One:
Tracy is 22 years old and plans to start a new tax-free Roth IRA in January with $2,000, then add $2,000 a year each January. If she earns 18% a year, what will she have at age 60?
If you're using a spreadsheet, you'll need to set up three columns. In the first column will be the value at the beginning of the year. The second column will be for the annual contributions Tracy makes. And the final column is for the balance at the end of the year.
In year one, then, Tracy's opening balance is zero (column one). She adds $2,000 (column two). And the third column is where the returns are calculated. To get the result in column three you need to add columns one and two, and then multiply the sum by 118% -- (opening balance + annual contribution) * 118%. Why 118%? If you multiply something by 100% you get the original value, so to increase the total by 18%, you need to multiply it by 118%.
The spreadsheet numbers for year one, then, will look like this:
Original Annual Ending Value Contribution Value $0 $2,000 $2,360
For the next year, the same process tales place. The Original Value for year two is the ending value from year one -- $2,360. The annual contribution is another $2,000. So column three takes the sum of those two numbers ($4,360) and multiplies it by 118% to account for the portfolio return of 18%. The ending value after year two is $5,145.
Original Annual Ending Value Contribution Value $0 $2,000 $2,360 $2,360 $2,000 $5,145
And so on each year. In this scenario, Tracy will keep investing the same way for 39 years in all (from her 22nd year to her 60th). If you simply copy and paste the three columns from the second year of your spreadsheet into the next 37 lines, you'll have the growth of this model over the full 39 years. At the end of the 39 years, Tracy's grand total would be $8,324,426. Voila.
If you want to alter this scenario for different rates of return, all you have to do is change the equation in your third column (in each year, again copy and paste to do this quickly) to reflect the return you want to test. For example, if you want to check a 12% return, you would add the original value and annual contribution and then multiply that sum by 112% instead of 118%. (The result after 39 years at 12% growth would be $1,532,183).
Scenario Two:
Let's complicate the first scenario to take inflation into account. We all know that prices will keep rising and that a million dollars today won't buy what it did twenty years ago (like we really know what having a million dollars is like anyway). So we need to reduce our final value each year by the inflation rate to give us a picture in today's dollars of what our growth will be worth.
Using the same contribution ($2,000 a year), the same return (18% a year), but adding inflation at 3% a year, you would need to add a fourth column to your spreadsheet, or revise your calculation in column three. In essence you would perform the same calculations as in the first scenario, but then take your final total each year and reduce it by 3%. Let's look at a sample year.
Column one in year one is your starting amount ($0). Column two is your annual contribution ($2,000). Column three is your return -- the sum of columns one and two ($2,000), multiplied by 118% (equaling $2,360). The fourth column takes the total of $2,360 and multiplies it by 97% in order to reduce the total by the 3% inflation rate. Your year-end total (in today's dollars) after one year is $2,289.
This doesn't mean you would actually lose the difference between $2,360 and $2,289. It means that the $2,360 you have is actually the equivalent of $2,289 in today's dollars. It's a way of giving us a sense of what the astronomical numbers we're looking at after 30 or 40 years might really mean to our standard of living.
Year two, then, starts in column one with the final total from year one of $2,289. Add the $2,000 annual contribution (column two), multiply the sum by 118% (column three), and then multiply that product ($5,061) by 97% (column four).
Original Annual Return After Value Contribution Value Inflation $0 $2,000 $2,360 $2,289 $2,289 $2,000 $5,061 $4,909
After the full 39 years, Tracy would still have the $8.3 million we calculated in scenario one, but we now know that in today's dollars, that total would be worth the equivalent of $3,053,271 if inflation were to stay constant at 3% a year -- still very impressive.
In tomorrow's lesson, I'll review compound annual growth rates and internal rate of return. Class dismissed.
(c) Copyright 1997, The Motley Fool. All rights reserved. This material is for personal use only. Republication and redissemination, including posting to news groups, is expressly prohibited without the prior written consent of The Motley Fool. ________________________________
Stock Change Last -------------------- T - 5/16 46.19 GM +1 1/16 69.38 CHV - 5/8 86.94 MMM -1 3/4 96.50
Day Month Year
Day Month Year
FOOL-4 -0.44% 4.32% 21.18%
DJIA -0.42% 1.46% 25.02%
S&P 500 -0.33% 2.46% 31.03%
NASDAQ +0.23% 3.57% 35.23%
Rec'd # Security In At Now Change
1/2/97 153 Chevron 65.00 86.94 33.75%
1/2/97 179 Gen. Motor 55.75 69.38 24.44%
1/2/97 120 3M 83.00 96.50 16.27%
1/2/97 479 AT&T 41.75 46.19 10.63%
Rec'd # Security In At Value Change
1/2/97 153 Chevron 9945.00 13301.44 $3356.44
1/2/97 179 Gen. Motor 9979.25 12418.13 $2438.88
1/2/97 479 AT&T 19998.25 22123.81 $2125.56
1/2/97 120 3M 9960.00 11580.00 $1620.00
CASH $1167.51
TOTAL $60590.89