At first glance, it sounds like an obvious question – surely it is better when stocks go up, right? From watching the ads on CNBC, it would seem that higher stock prices directly translate into more steak dinners and golf vacations while lower stock prices mean bringing your own peanut butter sandwiches to work (jelly is for bull markets).
However, consider a 22 year old that just started working and investing a year ago. While they might have a bit of money in their 401k, the bulk of their savings and investment is going to come over the next 43 years of working. It would be much better for the 22 year old if the stock market suddenly fell by half (assuming everything else is the same and that the stock market didn’t drop because we are about to head into a depression). In that case, the 22 year old loses half of the money they have invested, but going forward they are buying the stock market’s future earnings and dividends for half the price and double the long term cash flow return.
So that brings us to the title question, do you want the stock market to go up or down? This is a problem that spreadsheets are well-suited to solve!
Spreadsheet Inputs
- Your current age
- How much you have invested in the stock market
- How much you are investing in the stock market each year, and the expected growth rate of the amount invested annually
- The current stock market price ($2017), current P/E ratio(21.53), current dividend yield (2.18%). Source http://www.wsj.com/mdc/public/page/2_3021-peyield.html
- Earnings and dividend growth rate of the S&P 500 – let’s call it 3%
- Planned Retirement Age
Spreadsheet Output – The goal of the spreadsheet is to calculate how much money you have at age 65 when you retire, and see whether a decline today helps or hurts you given your inputs.
Spreadsheet Logic
We will use a similar approach to our Personal Financial Model where we are projecting the total amount of money we have invested, with each spreadsheet column representing numbers for one year.
First, we feed our assumptions into the column for 2015, the first year of our model. We convert the amount invested into shares of the S&P. The ending balance is the current number of shares plus the number of shares purchased from dividends and our annual amount invested.
Next we add formulas that forecast these cash flow and balance numbers for each year going forward. This starts to get a little hairy, I would suggest you download the attached excel file and click through the logic:
After pasting the formulas from column C to the right, you should have something that looks like this (we hid the years 2020-2057 below):
Now we have a spreadsheet that can take in data about age, S&P price/earnings/dividends, and annual investment, then forecasts how much money someone will have invested in the future. We can use it to answer our original question. For the example in the sheet we have now, let’s say in 2016, the S&P P/E Ratio suddenly falls by 50% to 10.77x and remains there until retirement at age 65:
The 22 year old has almost double the money when they turn 65 ($1.386 million versus $2.322 million) if the stock market fell by 50% in their first year of investing.
We can wrap up the analysis by using a data table to determine what age and amount initially invested would someone be better off if the stock market fell by half and what age or amount invested they would be worse off. We inserted an HLOOKUP to get the total value of our investments in the year that we turn 65:
Then we feed this into a data table in order to calculate how much money we have in the stock market at age 65, depending on our starting age and starting amount invested. We can paste the results in with a 50% drop in the first year and without one, and compare the balances (with conditional formatting to make it easier to see):
The results seem to make sense – the younger you are and the less you have currently invested, the more you would prefer the stock market to go down. We find that for the typical household that earns about $50k and saves $5k/year, generally if you are 35 or younger, you’d prefer the stock market to go down in order to maximize your amount invested at age 60.
To see the spreadsheet, click here: Do You Want the Stock Market to Go Up or Down?