Momentum Mo Problems

In which I ask myself if I'd jump off a bridge just because all my friends did.  Wait, what?  All my friends are gone and I'm stuck up here with whatever chased them off a bridge?

The Super Secret Awesomeness Strategy

Fall of 2008 was not a fun time for investors.  Even Buy and Hold believers had their faith questioned as the value of their portfolios dropped by 30-50%.  From out of the shadows came calls for the Super Secret Awesomeness Strategy... able to trade punches with Buy and Hold during good times, yet calmly stand aside when everyone else panics. Why suffer, when you could have results like these:

(As you can guess, that is a very selective date range.)  So this is today's challenger for Buy and Hold's heavyweight belt.  A strategy that had been around for decades, but which gained renewed popularity in the last few years.  In the red squares... the Super Secret Awesomeness Strategy... aka Momentum Investing.

Strength in Numbers

The wisdom of crowds is a curious thing.  In an efficient market setting, it can decide that the price of rice is exactly $500 a metric ton (coincidently the only size available at Costco).  Or, in a low-information setting, it can decide that the best way to exit a burning theater is to climb over the person next to you.  These two acts are actually very similar.  In each case, there are lots of people doing what they think is best.  The difference is information.

Whether it is farmers weighing an ox, voters choosing a politician, or bidders trying to snag something on e-bay, there is some small piece of information contributed by each member of the crowd.  That's not to say it is good information.  It may be egregiously bad information.  But the hope is that it is balanced out by opposite opinions.  You want to sell me a soda for a dollar?  I want to buy one for a nickel.  We haggle and settle on fifty cents.  If that information is public, the next buyer-seller pair can dispense with the haggling, or maybe fine tune the price to fit their specific desires.  This is all pretty obvious, but what I'm trying to get at is the idea that you can piggy-back on what everyone else is doing and get similar results.  Because the price was decided by others, you can choose to ride along and get your Costco rice at a price that is somewhat reasonable.  (Just be careful that you are actually heading to an exit when someone pulls that fire alarm.)

One would hope that the market works this way too.

Oh, and since we are talking finance, I should probably make my disclaimer here: First off, I should reiterate that I am not a financial planner, and you should make your own financial decisions based on what is best for you.  Secondly, I highly recommend pyramid schemes.  They have gotten a bad rap recently, but I can totally get you in on the ground floor.

Anyways, if you let the masses determine the price of an asset, it will save you a lot of time and effort.  Yes, maybe you have a general feeling that the asset will go up... that would be why you are buying it.  (And if that asset is, say, the whole stock market for the next 30 years, you have a strong case for your thesis.)  How much should you pay for it though?  You could talk to random people who own that asset and haggle with each of them, or you can piggy-back on the trillions of trades a day (just for the NYSE) that have already worked out their haggling.  Even if they are each contributing a tiny amount of information, the overall market has a very well-informed price (which you may or may not agree with).

Indicator, I Hardly Know Her!

This brings up the two basic types of market trading you can do.  When people trade an individual stock, they might think, "Company X is very valuable and produces earnings that I would like a piece of.  I value those earnings at $10 or over.  I believe that the price is going up.  I like to end sentences with prepositions following."  They then buy a share if the price (which is determined by the crowds) reaches $10 and hold on to it until the price meets their value of the share.  This is investing based on fundamentals.  The idea is that you found an inefficiency in the market where someone disagrees with you on the fundamental value of something.

But what if we believe the market is actually efficient?  An alternative way to invest in an individual stock is to say, "The stock for Company X has risen recently.  Their chart shows an accelerating stock price that should continue for at least the short term."  They then buy a share at the market rate and hold on to it until the technical indicators tell them to sell.  This alternative is usually a risky (and expensive) play for individual investors due to our non-instantaneous information and the high transaction costs.

Instead of using technical indicators to buy an sell individual stocks, today we are going to look at buying and selling the market.  The idea is that there is a momentum to the market that makes the swings larger than would make sense from just the fundamentals.  In late 2008 the "average company" lost about 50% of its value according to the stock market.  Was that really true?  Did General Electric have half as many factories?  Did Microsoft have half as many Windows users?  Did Lehman Brothers have half as much money as they thought they did?  (Oh, wait.)  Anyways, the quick(-ish) rebound in the broad stock market (as measured by the S&P 500) shows that it was mostly the market's momentum that was bringing down prices.  Smart investors like Warren Buffet saw that and kept plowing money back in to the market because fundamentally everything looked cheap to him (but not to whomever was selling to him).

Taking the Bear by the Horns

To buy the whole market is quite easy.  There are mutual funds that will let you do it for 0.2% of your portfolio/year and exchange traded funds that will do it for 0.05% (plus a trade fee of $5-$10).  We aren't going to concentrate so much on the "how" for this post.  Lets focus instead on the "when".  One of the easiest things to measure in the market is the 12-month simple moving average (SMA).  For our purposes, we can look at the monthly value of the S&P 500 (available since 1950) and average the last 12 months.  These fit nicely into our spreadsheet typing the [in brackets] portion:

(A25:A784) = Date
(B25:B784) = Adjusted Close of the S&P 500
(C36) = 12-month SMA [=AVERAGE(B25:B36)] -> complete the column

To round out our data set for times when we can't have a 12-month SMA, we can just set C25:C35 to = B25:B35.  Any strategy dealing with averages over time will run into end effects like this, so we won't worry about it too much.

The technical indicator in this case is when the current value of the S&P 500 passes above or below the 12-month SMA.  If it passes below, we can expect that momentum is artificially pushing stock prices down (like Fall 2009).  If it passes back above, we can expect that momentum is artificially inflating  the prices.  Because the S&P 500 has grown (at a 7-8%/yr pace), most of the time we will see the positive momentum.  At that point we should be invested in the market.  If the momentum turns negative we should sell and hold as cash (or equivalent).  Similar to the Sell in May question, this is a binary decision, using the month's information to determine whether to be in stocks or cash for that month.  (Interestingly, this limits us to 12 trades a year, making it a somewhat fiscally achievable strategy.)  To model our binary decision:

(D25) = Market rate for that month [=B26/B25]  -> complete column
(E25) = Decision for rate [=if(B25<C25,1,D25)] -> complete column
(F25) = Starting Portfolio Value [=A25]
(F26) = Portfolio Value [=F25*E25]  -> complete column

Wow!  12-Month Momentum is going toe-to-toe with the champ!

I'd have to call this one a tie.  The strategies were neck and neck from 1950 through the mid-80's, but then increased volatility sent false "sell" signals which allowed Buy and Hold to take the lead.  But next, the 12-Month Momentum strategy showed its pugalistic prowess by calmly sitting out the worst of the two bear markets we've had in 2001-2 and 2008-9.  Unfortunately that meant it sat out much of the recovery of '09, for a final lead of 6% over 63 years.  To put that in perspective, it equates to 0.1% per year.  Actually implementing the strategy would have likely cost an unknown amount, but there is a decent chance it would be more than 0.1% per year.  This strategy does leave the portfolio value more stable, though, so if that has value to you it might be worth pursuing it a bit further.

Round Two

So if the 12-Month SMA challenger is so close, maybe a little fine tuning will unseat the champ.  Going in to this analysis, I thought the main drawback to a momentum strategy would be that you spend time (which I previously showed was valuable) out of the market waiting to get back in.  Because the information always lags, and the market on average is growing, you miss some of the "average" returns when the market is turning around.  Lets modify the length of time over which we are averaging to see if we can improve.  One way to do this is go with a longer average, such as a 24-Month SMA.  Another way is to go shorter, such as with a 3-Month SMA.

Ouch!  These two never really get off the ground.  The 24-month SMA portfolio experiences about the same number of transitions to-and-from cash (a little over 1 per year) as the 12-month SMA portfolio, but they are delayed by 1-5 months, so that the portfolio misses just a bit more of the good times and (probably more importantly) feels the downturns for just slightly longer.  This is enough to lose a little over 1% per year compared to the Buy and Hold or 12-month SMA strategies.  The 3-month SMA strategy simply gets too many signals to transition (over 4 per year), which keeps it in cash for an astounding 70 months longer than the 12-month SMA.  That is nearly 6 years of sitting around waiting!  Lest you think that we just haven't refined enough, I went ahead and ran a bunch more timespans:

It looks like 12-months is the optimal timeframe to average over in creating your indicator, and it just barely yields more than the market average.  Actually, if you add up all the months the 12-month SMA tells you to sit out the market, you end up on the sidelines for over 18 years (of the 63 years I'm looking at).  It's remarkable to me that this strategy compounds at all, and it is a testament to the yield you get during the time you are invested... a whopping 10.7%!

Technical Knockout

One final thought for today's post.  It isn't really fair to compare the SMA Technical Indicator Portfolio to the Buy and Hold Portfolio.  We've played around with the length of averaging, but imagine an extremely long SMA (simple moving average).  As the length of averaging time gets longer, we'd get fewer and fewer signals to switch between stocks and cash.  Because that would expose our portfolio to more growth in the market, the dip in yield we saw reverses itself and eventually you get no signals to move to cash.

So, actually, an alternative way to think of Buy and Hold Portfolio is that it is a Technical Indicator Portfolio whose indicator has never triggered a sell signal.

-----

Update!!!

It's Log, It's Log, It's Better Than Bad, It's Good!

As noted by Jared in the comments, showing exponential growth on a linear axis makes for tough comparisons between lines.  I promise I wasn't trying to deceive (well, maybe just a little).  Here is what the Buy & Hold, 12-Month SMA, 24-Month SMA and 3-Month SMA look like in log.

-----

Didn't answer your question?  Feel free to let me know in the comments and I'll include your ideas when I post more on this in the coming weeks.   If you want to get email notifications when new posts go up, send an email to subscribe+overly-complicated-excel@googlegroups.com to subscribe to the mailing list. 

They See Me Rollin'

In which they do a cost-benefit analysis of hatin'.

The Wheels on the Bus

Temperament matters a lot.  Case in point: a few weeks ago, while riding home from work, the traffic suddenly slowed down and from behind us I hear a loud "thwunk."  My bus had just been rear ended.  Everyone was fine, except for the tiny car whose front just crumpled.  Even its driver seemed ok (or at least aware enough to discretely slide her phone away).  It was a funny story (made much better in person because I could properly pronounce "thwunk" for you), but as I was sitting there on the bus for an hour I heard two distinct conversations.  One type started, "Dude, this is crazy.  Has this ever happened to you?", while the other type stared, "Dude, I'm never riding the bus again.  It's not worth it."  I know, only in Portland would everybody on the bus start their conversation with "Dude", but what it got me thinking about was how do we know if the bus is "worth it".

When my wife and I moved to our current apartment, we didn't think about the fact that she would be driving to work and I would be taking the bus.  It was just intuitive.  That said, her work is closer.  She's also more environmentally conscious than I am and the ticket price for her bus route is less than for my route.  All of that seems like it would switch our roles.  So that is today's goal.  Lets add up real costs to see why it makes sense for me to ride the bus and not her.

A Non-Ideal Gas Law

On the surface, it seems like this should be simple.  Does the gas to get there cost more than the cost of the ticket?  For her a round trip looks like this:

And for me:

So as a first pass it looks like it wouldn't make sense for either of us.  Of course, this is the simple case.  Since we know gas isn't the only expense that goes into the car, lets overly complicate it.

Four Car-dinal Virtues

The way I see it there are fixed costs of owning a car, and there are per-mile costs.  Fixed costs include the actual purchase, the license and registration, as well as the insurance.  These don't scale with how much you drive.  (Within reason, of course.  The difference between 10k miles and 12k miles per year is zero.  There is still a difference between 10k miles and 0 miles or 30k miles.)  Instead, these costs scale with how pricey a car you have and how good of a driver you are.  On average these costs end up being ~$16 a day (almost $6000 a year!).  Maybe I'll tackle this another day, but for the current post lets say that we are keeping the car, just deciding if it makes sense to use it for a certain trip.

The flip side of this is the per-mile costs. By my count you could group these into four categories.  Lets find the {inputs} we will need for each cost.  The first one, gas, we already mentioned.  It will only depend on {car's MPG}, {cost of gas}, and {miles traveled}.  The second cost is what to do with your car once you reach your destination, as there is often an associated parking fee.  This is pretty straightforward, so we'll just spread the {parking fee} over the whole round-trip.  The third cost is wear and tear on the car.    As a back of the envelope calculation I'm guessing for every 30k miles you need about $500 of tune up, $500 of brake pads and tires, and ten $50 oil changes.  This works out to $0.05 a mile, which is a figure I've seen repeated on several auto maintenance sites (backing out gas prices).  So again we just need {miles traveled}.  Finally, the fourth cost I'll throw in is carbon offset credits.  The market has determined a price of $0.011 per mile for offsetting the carbon emissions from your car.  This isn't quite fair as they must be assuming you have an average car (23 mpg).  Instead, you can convert that price (just multiplying it by 23 mpg) to get $0.25 per gallon of gas you consume.  Again we need {miles} and {mpg}.  All of these things could be split between multiple {passengers} if you carpool.

We can do the same cost analysis for the bus.  They only have two costs, and the first one is easy: the {ticket cost}.  Once again, we'll go for round trip.  They also have a carbon offset, and this one is a bit more tricky.  Buses get lousy gas milage (about 5.5 mpg once you convert from diesel to regular), but spread it out over many {passengers}.  My commuter bus routinely fills up its 45 seats, but some routes are nearly empty.  By using the {bus mpg} and {miles traveled} then dividing by {people riding} we get gallons of gas used per passenger, which can get us to our carbon offset credit price.  What happens when we add these things up?

For My Wife's Commute:

And Mine:

 So now the bus seems to make sense for both of us.  Still not the intuitive result, though.

As an Aside: Taking a Test-Drive Around Portlandia

Before I go any further, one cool thing you can do with this model is look at when it makes sense to take the bus on smaller trips.  Other than parking, the price to make a trip by car scales linearly with the number of miles you go.  The Portland bus system is a fixed price of $2.50 for 2 hours or $5 all day.  So if you know your cost of parking, you could choose your best method of transport based on the following cost curves (assuming one person in the car and average 23 mpg).

Is your trip less than 10 miles with free parking?  Take your car.  Are you going downtown where the parking is a $5 minimum?  Take the bus.  Are you visiting someone 5 (round trip) miles away where the parking is $2?  Take a bus if you plan to stay a short time, but a car if you would need the all-day pass.  Based on my own Portland bus experience, I'm guessing the buses travel at a rate of 15 mph in the city, which means you could just as easily scale the axis by a factor of 2 and call it the "number of one-way minutes traveled," which may be easier to visualize.

This chart also explores the idea of a free-ride zone and tiered pricing, in that it doesn't really make sense to pay for a bus ride less than 20 minutes unless there is a parking fee, while an hour and a half ride might be worth $10 to someone.  Downtown parking is in the $5 range, so Portland's recent move to a single price structure has little impact on their overall downtown bus usage.  Oh, and as an aside to this aside, look at how awesome the mpg can be when you get a full bus... it's in the 300 mpg range!

Time is Money

Getting back to my earlier question, why is it that my wife's commute makes no sense by bus.  The answer really comes down to the fact that there is no direct route.  While it certainly is annoying to spend 20 minutes and $6.30 in driving expenses to get to work, this is dwarfed by the 90 minutes it would take by bus.  We need a way to put a value on this time.

One way we can do this is to think about our "real" wage.  This is the value that you put on your time.  By implicit agreement, if you work for money you have agreed that a certain amount of money is worth at least a specific amount of your time (if not more).   If you make $40k a year, your per hour wage is just under $20 an hour for a 40 h workweek.  But if you commute to work, think about work off the clock, or spend any money on clothes/computers/vuvuzelas for work, you don't really make $20/h.  Maybe it is more like $15/h. This is your value of time.  Do you really love your job?  Would you do it for less money?  Maybe your time value is more like $5/h.

If your time value is $10/h and you spend time not being productive, it costs you $10/h.  This may be fine if you are spending time playing with your kids, reading a book, or watching an episode of dancing with the stars.  You agree that the lost time is worth $10, otherwise you would work and earn yourself $10.  This is obviously simplified, and because your work/life balance is not a free market you could end up mowing the lawn for an hour (for no pay) when you would rather read a book.  Ideally you'd pay someone less than $10 to mow the lawn for you.

So, why is this important again?  Well, imagine your time value is still $10/h.  If your choices are to drive 30 min (and lose $5 of productive time) or ride the bus for 90 min (and lose $15 of productive time), that money needs to go into the car/bus equation.  This can be mediated somewhat by being productive on the bus (or in the car).   If you do some work on the bus, you might be able to be at work for less time.  If you read a book on the bus, that is "productive" time that you don't do at home and still doesn't count against you.  The way you "lose" the money is to sit around doing nothing or doing something you don't want to do.  You wouldn't pay $10 to do that.  Lets apply this to our model.  I haven't shown you how I defined everything, so lets do that now (typing the items [in brackets]).

Oh, and you can follow my work here.

You Details
Time value (per hour) = C5

Car Details
Car route (miles) = C8
Car trip time (min) = C9
Productivity in car (min) = C10
Price of gas ($) = C11  (Currently $3.77 near me)
Mpg = C12     (Average for cars is 23, but we'll allow any value)
Gas used = C13  [=C8/C12]
Parking = C14
Total people = C15   (1, unless you are carpooling)
Cost of gas per person = C16   [=C13*C12/C15]
Cost of parking per person = C17   [=C14/C15]
Cost of wear & tear = C18   [=0.05*C8/C15]
Cost of lost productivity = C19   [=(C9-C10)/(C5/60)]
Cost of carbon offset = C20   [=0.25*C13/C15]
Cost of driving = C21   [=SUM(C16:C20)]


Bus Details
Bus route (miles) = F11
Bus trip time (min) = F12
Productivity on bus (min) = F13
Mpg (equivalence of diesel) = F14 = 5.5
Gas used = F15  [=F11/F14]
Total people = F16   (10 = low use, 20 = moderate use, 45 = seat capacity, 60 can fit with standing)
Effective mpg = F17   [=F11/(F15/F16)]
Cost of (round-trip) ticket = F18
Cost of lost productivity = F19   [=(F12-F13)/(C5/60)]
Cost of carbon offset = F20   [=0.25*F15/F16]
Cost of bus = F21   [=SUM(F18-F20)]

And lets throw one more in:
Walking details
Time = F5
Productivity while walking = F6
Cost of walking = Cost of lost productivity = F8 = [(F5-F6)/(C5/60)]

Running the Model... Or Driving it... Or Taking It On The Bus, I Guess

Now, when you plug in my wife's commute, it seems much more obvious that driving is the economically favored option.

Which is different than for my commute:

Her conditions could equilibrate if she valued her time less (somewhere around $5 would work).  Alternatively, she could make use of more of the time on the bus.  But that is tough!  I already make the assumption that she could work two hours, and with two bus changes each way sleep isn't an option either.  My route is simpler, with one bus change, meaning I can work or read for 90 of my 130 minutes.

Obviously productivity can change a lot with minor input changes.  Does the bus evade traffic?  Do I enjoy driving enough to count it as productive time worth $15/h?  (In short, no.)  Some of this productivity can be conceptualized if you look at walking to work (noted top right but not added to the graph).  It would take me about 7 hours to walk the round-trip.  With a bike I could do it in 3.  I like walking and biking, but only for perhaps the first hour, so after that it is non-productive time.  Am I willing to pay the extra money to get some of that time back?  If you are using this model as a tool for your own circumstances, (and I recommend you try it), keep in mind that some things are flexible (like your love of walking... in the Portland rain) and some things are fixed (like a monthly or yearly bus pass that saves you money).

Interestingly, you can also go backwards in the analysis.  If you know that you like your bus commute at least as much as the drive, you can back-calculate from your lost productivity calculation how much you value your time.  Maybe you realize that you value time at less than $5/h.  It might make you think twice about paying someone $10 to mow your lawn for an hour.

Taking The Long View (past Longview)

One final note.  My wife and I love going to Seattle for the weekend to visit friends and family.  In the past I often thought of it as a low cost outing.  Yeah, the price of gas is a pain, but the total cost isn't much if you have low cost fun like board-games and "hanging out."  When I first started building the model, though, I started having second thoughts.  ("It really costs that much just for my commute?" etc.)  Here is what the trip looks like when compared with a greyhound bus.

It's actually not that bad.  Split between the two of us, the cost of the car is less than $33 round trip (compared to ~$41 for the bus) before lost productivity.  And with two of us in the car, I'd say I get at least two hours of conversation, reading, and other fun activities that I'd count as productive time.  That doesn't hold if it were just one of us going up though.

What can I say... at least I didn't have to walk!

Data Day

In which I update some old posts with new numbers and thoughts.

The Unemployment Line

This was my first real post, and I sorta cheated by thinking about (and modeling it offline) for a few months before starting the blog.  The central argument was that if you modeled the unemployment rate from it's peak (Oct '09) until the present (at the time, Nov '12) you created a remarkably linear fit.  At the time of the blog post, I could add four more months of data which not only fell along the line, but improved the fit.  Since that post, two more numbers have come out, and at first glance the model missed twice.

The April number was modeled to be 7.6, but actually came out better (7.5).  The May number was modeled to be 7.5, but actually came out worse (7.6).  The political pundits jumped on both numbers to show that their favorite program was working, or the opponent's obstructionism was hurting the economy.  Smarter pundits pointed to the number of jobs created or U6 unemployment number changing in the opposite direction to make counterpoint cases.  What I pulled from these numbers was that the "linear model" is now even better than before.  Adding in these data bring the R-squared up past 0.965, meaning the fit is better.

The added data (dark red) still follow our linear trend.  The change in U3 (light red) is still right around -0.6% per month, even in the months that have less than 12-month averaging (open squares).  And the predictions I made earlier are still legitimate.  (Well, as legitimate as they were ever going to be.)  The rounding to one decimal means that all the focus on one data point is silly.  That said, do not be surprised if the June number is 7.5.

Sell in May and Go Away?

Not too much data to add to this one.  The S&P started May at 1597 and ended it at 1631 (giving a yield of 2.1%).  This is larger than an average month, and didn't make those Sell in May proponents happy.  They are now (jokingly?) advocating "Sell in June".

Anyways, sorry for such a short post.  Next week we will get back to the interactive models.

What Hath Man Roth?

In which I model the certainties of death, taxes and exponential growth.

Soliciting Inputs

Seven posts in, and I still haven't managed to chase everyone away!  One of the most satisfying things about sharing these blogs with you is that I get the chance to talk to people offline about the different models.  I mean, I'd probably be making spreadsheets for many of these things anyways, but now instead of relegating them to a random folder on my desktop I get to chat with people who have different (and sometimes better) ways of looking at these things.  By all means, let me know if there is something that you think a simple overly-complicated Excel model would solve.

To that point, one of the first things my friend Ryan asked me was, "How much should I contribute to my IRA?  Can you model that?"

The answer is yes.  Yes I can.

First off, I guess I should reiterate that I am not a financial planner, and you should make your own financial decisions, keeping in mind what is best for you.  Secondly, you should totally invest in bytecoins. By definition they should be 8 times more valuable than bitcoins.

Thirdly, fill up your IRAs if it makes sense to do so.  I could (and will) go through a ton of math and modeling, but all of that pales in comparison to one piece of logic: if something has limits to check its power, it is likely to be very powerful indeed.  The limits on IRA contributions tell me that, in the absence of these limits, people would abuse the system for their own gain.  This means that there is likely no "sweet spot" where you should only partially fill up your IRA, but instead you should take it to the limit each year if you can.

To Roth or Not to Roth

I'm not going to go into the advantages of Traditional vs Roth IRAs too much, but one way to think of it is this:

• If you are going to be taxed more now than you are later, choose a Traditional IRA.  They let you evade some tax now in exchange for taxing your distribution later.

• If you are going to be taxed more later than you are now, choose a Roth IRA.  The contributions are taxed normally, but the distributions are not taxed.

So yeah, I'm a young guy.  I hope to make more money later than I do now.  I know our nation has a lot of stuff it needs to pay for and I don't predict that tax rates will go down in the future.  I choose Roth.

One funny side benefit of the Roth IRA, and the reason I will use it for today's post, is that it is much easier to model.  Unless you were going to spend your money on something tax deductible, investing in a Roth IRA basically allows you to ignore tax all together, which takes out an extremely complicated variable.  I'll just assume that if you have $5500 to contribute to an IRA, it has already been taxed, and would otherwise have been spent on something fun like a year's worth of coffee or a 3-D printer (and not, for instance, your pain medication, last month's mortgage payment, or a donation to your church).

Competing Interests, aka Saver's Remorse

Before we get to the modeling, lets first predict what we want the model output to look like.  For most people, what they want their IRA investment to pay for is, wait for it... retirement.  (I know, it seems silly because it is right in the name "Individual Retirement Account", but you can take out money for your first house or pay penalties to take it out before age 60.  I'm saying that that isn't what we're modeling.)  Basically, every year you want to add a little money to your investment and let it grow.  At or after age 60 you will make withdrawals such that hopefully the rate of money growing matches the rate you are withdrawing and you live out your golden years in financial independence.  This is what it looks like if you grew your retirement account at 7% and withdrew at 24x your contribution rate at age 65 ($5.5k contributions and $130k withdrawals):

But one menace is waiting for you... the same one that saved our hypothetical country last week. Inflation.  Inflation is basically good if you are in debt and bad you are invested. That same graph with inflation of 2.5% (which is usually considered the achievable target):

So a model of our Roth IRA has to start with a model of inflation.  Inflation stacks year over year, so lets make our first variable age.  Other important things are the age you would retire at, and the inflation rate.

Follow my work!

Current Age = B1
Age at Retirement = B2
Inflation Rate = B3

I will assume you can figure out your age.  (I'm this many!)  Age of retirement is a bit tricksy because it may be way in the future and it is hard to plan that far ahead.  There are Roth penalties if you withdraw before 60 so lets make that the lower limit and let people choose 60, 62, 65, 67, 75 or... 100?  I guess some people never retire, but for modeling purposes we'll say that our age list goes to 100.  Lets do that now. Make a column from C11:C93 with the numbers 18:100.  The inflation rate is variable, but is targeted at 2.5% and has historically averaged 3.37% in the US (including spikes where it went over 5%).  Lets make all those values possible.  We can define the inflation factor (typing the [In Brackets] portion) as:

(Cell D11) [=B$3^(C11-B$1)]

Complete the column by dragging the cell down to D93 (or double click the bottom right corner).  If you put in an age like 30, that should correspond to an inflation factor of 1.  Ages older than it should have values greater than 1, and ages less than it should have values less than 1.  As you can see graphically, a higher inflation rate can get exponentially out of control pretty fast.

Now we can use that inflation factor to make all our other numbers "real" by dividing by an inflation factor to equate to today's buying power.

Fertilizing Our Nest Egg

The next thing we need is some way to personalize the model.  You may be 20 years old and just starting out or 50 years old and several thousand dollars invested.  Lets define some more terms:

Starting Nest Egg = B4

"Real" Contributions (in 2013 dollars) = B5

"Real" Withdrawals (in 2013 dollars) = B6

Interest Rate = B7

Contributions for Roth IRAs are limited at $5500 per year per person right now, but they started out in 1998 with a limit of $2000.  That works out to an increase of ~7% for the limit raising.  Lets be cautious and say that in the future they will only raise the limit at the rate of inflation.  That means that even 30 years from now your "real" contribution would still be limited at $5500 factored at today's dollar value.  Also, you could choose $11000 for a couple, although thats really just two individuals.

The same considerations go into determining withdrawals.  Once you turn 60 you could take out as much as you like!  But really you just want to take out some to let the rest keep growing.  This variable should be how much you would take out in today's dollars.  Keep in mind that you are only replacing expenses and not adding more to your savings at this point (although technically with a Roth you could).  You might not have a mortgage any more either.  Make your own assumptions as to social security and medicare offsetting your needs, but a conservative estimate for withdrawals would be your all your current income minus investments and housing costs.

The long term interest rate for the stock market has been 7%, but is quite volatile and not guaranteed to be 7% in the future.  You could also have a Roth IRA that holds other assets like Bonds, Cash, or CDs.  (At this point would investing in Nickleback albums count as "buying low"?)

Oh, and if you haven't contributed yet, enter "0" for your starting nest egg.  Otherwise set it accordingly.

Cool!  Now, in the interest of making things more clear, I'll separate the function temporally.  There are four periods in your investing life.

• Time before now, which we don't care about.
• Right now, where you have the nest egg you have right now.
• Between now and retirement, where your nest egg grows.
• After retirement, where your nest egg may grow or shrink, depending on the size of the distribution and rate of growth.

To model this I'll use nested "if" statements.  (Note: Omitted "nested if / nest egg" joke because the CS majors out there are already cringing.)  It should look something like this:

Complete the column.  Now Make those nominal values real by dividing by the  inflation factor:

 (Cell F11) [=E11/D11]

Complete the column.  I also want to know if the money has run out yet.  I use nested if statements to check if the real value of the IRA is above 0.  If it is, we should check last year.  I want it to report the earliest year that the money runs out, thus:

(Cell G11) [="not yet"]

(Cell G12) [=if(F12>0,"not yet",if(F11>0,C12,G11))]

Complete the column and we are good to go!  Lets test it out!

Running with the Devil

I guess the simplest way to run the model is to check and see what an 18 year old could do if they routinely filled up their IRA until age 60, got the market rate of 7% during a period of low inflation, and took distributions at, lets say, twice the US minimum wage (~$30k/yr):

Thats how it is supposed to work.  A Roth IRA should allow someone of modest means to save for a modest retirement.  I'm guessing that is how they determined the contribution limits too, as the money starts to decay precipitously right at the end of life.  Some would say that if you haven't used up the last dollar then you didn't really optimize things perfectly.

But what if two 18 year old newlyweds routinely filled up their IRAs until age 60, got the market rate of 7% during a period of low inflation, and took distributions at the average income level of $51k.

Now the IRA has taken on a life of its own.  It isn't decaying, but instead it is still growing.  The government allows this and even after you die your Roth IRA can be passed down to your children who must take distributions (tax free) according to their age.

In Case of Emergency, Break IRA Glass

But what if that couple waits until they are 30 to start up their IRA.  

Technically they have only lost 12 years, but that is more than the doubling period of 7% interest.  They have less time to save, and the years they put in compile to give a nest egg only half the size (even though they invest for 71% of the time).  They run out at age 77, which isn't horrible (as the US life expectancy is about 80), but they would worry about finances.  Perhaps they keep working until 65.  Perhaps they take out only $35k a year.  (Both of these let the money last until age 95.)  Either way, what I learned from this is that time is very important.  

What else is important?

Fees.  That 1% mutual fund fee seems small, but it effectively takes you from 7% returns to 6% returns. Big deal?  Our happy newlyweds in example two with the self-sustaining portfolio?  With 6% returns they suddenly run out of money at age 90.  The closest thing to investment advice I'll give is that you should be extra-aware of fees.  There are funds available that have tons of diversity and only charge 0.06 to 0.1%.  If you are being charged more than that they had better make up the difference with performance.

Inflation.  Just going from the optimal inflation (2.5%) to historically average inflation (3.37%) makes our happy newlywed self-sustaining portfolio run out at age 93.

Contribution.  To answer the initial question, what if we only half-fill our IRA?  The happy newlywed self-sustaining portfolio runs out at age 77.  If you have them work until age 63 and only take out $30k/yr, you can get it self-sustaining again, but that is a huge decrease in standard of living.  Even filling it up only 90% takes it from being self-sustaining to a slow draw-down.  Again this comes back to time.  A dollar you didn't invest at age 18 is worth about two dollars at age 28, four at age 38, and sixteen at age 58.  The money you put in the last couple years of saving is barely going to grow at all.  And this doesn't even count the volatility of the markets!

I encourage you to definitely take the model for a test run!

Take Home Message

You basically want to invest early and often.  Don't take out your investment to buy something shiny, because replacing your time is only possible through pushing off retirement.  Also, hope that the Fed knows what it's doing with monetary policy.

I made this model much simpler than it could be.  Perhaps you stop contributing at age 60 but leave the money in for another few years.  Perhaps you shift the interest rate from risky to conservative as you age.  Feel free to suggest improvements.

I didn't mean for this post to be a downer (although you wouldn't know it from all the "things I learned"), so lets look at one rosy scenerio.  One thing to consider (and possibly post on later) is that if you look at all of human history, life-spans are increasing at a crazy rate.  What does that mean for investing?  If instead of "retire at 60, enjoy 20 years of retirement", the average human condition was "retire at 75, enjoy 50 years of retirement", we would get that precious commodity (time) that is the key to all investing.  At that point, our happy (not-so) newlyweds will have an IRA that is nearly indistinguishable from exponential growth.

-----

As always, you can subscribe to get new posts by sending an email to:
overly-complicated-excel+subscribe@googlegroups.com

Growing Pains

In which I let the masses determine their own level of stimulation.  I've honestly reworked this sentence like ten times and this is the least dirty sounding version.

Previously on OCE

If you caught my last posting and are back for more... thank you!  I'm really happy with how many returning visitors the blog is getting.  If you didn't catch my last post, this one is basically an addition to it. You are welcome to go back and check it out in full, or be happy with this short synopsis:

• An influential set of researchers (we'll call them RR) poured over historical data and claimed to observe a correlation between the amount of debt a country had (as a percent of their GDP), and what kind of growth (increase in GDP) they experienced.  As the debt got to be greater than 90% of GDP, there was a precipitous drop off in average growth, going from the 3-4% range down to negative 0.1% growth (recession). 
• A second set of researchers (that we'll call HAP) looked at the same data and found many flaws, from the actual data, to the averages, to the typos in their Excel worksheets, which made HAP's version of the growth number 2.2% under the high debt conditions.

So why is this important?  Again stealing from last week:

• In 2010, when the first set of research came out, the US was nearing that magical 90% debt mark, and had just tried to stimulate its economy.  Parts of Europe wanted to as well.  Some RR believers argued that stimulus was a bad idea as it would lead to even more debt.  Others even went the opposite direction and called for austerity in hopes of balancing the budgets and lowering debt. 

• The idea behind stimulus is that a government can borrow money (adding to debt) and spend it right away on improving the economy (adding it to GDP). Because it is affecting both the numerator and the denominator of debt/GDP it has a dampened effect on the ratio. The bonus is that all that money you paid this year to build bridges, give tax refunds, or even landscape the national mall goes to people who can spend it again, increasing GDP next year. 

The hope is that you can "grow your way out of debt" with the debt/GDP denominator gradually increasing and the debt becoming less burdensome as inflation eats away at it. But will it work? That is today's test.  We are going to model a high-debt national economy and see how different conditions affect it.

Stimulation Simulation

Right off the bat I should point out that I'm making this model as simple as possible.  We will have three columns (lets call them F, G, and H) that will be the year, GDP, and Debt, respectively.  We'll add one extra column that we can define right now as Debt as a percent of GDP (by typing the [In Brackets] portion):

(Cell I2) [=H2/G2*100]

Oh, and you can follow my work.

Great, we're done!  Except for that pesky NAME! error we get for not having put anything into G2.  Ok, lets fill it.  For the year column, make a series of numbers F2:F32 that are 0 through 30.  I'm including a year "0" so that at year "1" we can type in an equation that affects the previous year and can be dragged down to complete the column.  Keeping with the "simple" theme, lets define some terms so that we can use them to fill our Debt and GDP columns.

Initial Values:

Initial GDP (in dollars) = C2 = 1000

Initial Debt (% of GDP) = C3

I think these are easy to justify.  I'm going to use an arbitrary value of 1000 for the initial GDP because actual GDP values make for extra large cells, and honestly, we're going simple.  The "% of GDP" for Initial Debt means we will have to use "C3*0.01*C2" quite a bit (which is annoying), but it makes the presentation look prettier and is more intuitive for some folks.  I'll let people play around with the initial debt levels, keeping in mind that we were at 80ish % right before the crisis.  As a background, we averaged 60ish % over the 1990s and 2000s.

Crisis and Stimulation:

Financial Crisis Hit (% of GDP) = C6

Stimulus (% of GDP) = C7

Percent of Stimulus Respent = C8

The idea here is that the financial crisis basically made about 5% of our GDP evaporate.  I say "about" because it is tough to determine, as we did a lot of mitigation and don't actually have any real-time measurements.  You could make a good argument for any number from 2-10 (and probably some bad ones too) so lets set this as a movable target as well.  This will also let us set up scenarios in the absence of Crisis Hit.  Same thing with the Stimulus.  Technically there are still some remnants of stimulus dollars out there, but for the most part they were contained in the 5% of GDP injection of 2009.  Some people thought we needed more, some less.  Heck, lets make all the numbers from here on out movable values.

Speaking of values, when I say stimulus it means either tax breaks or spending.  They are (basically) the same.  Both are debts the government takes on so that spending in the economy will rise.  Once spent, that money goes to someone else who can choose to spend it again or save it.  The savings rate in the US is about 5%, making the Percent of Stimulus Respent theoretically about 95%.  But since the European savings rate is higher (10-20%) and Asian rates higher still we might as well let this be anything.

A Two State Solution:

Interest Rate on Debt (%)               for Low Debt = C11, for High Debt = D11
Inflation Rate (%)                           for Low Debt = C12, for High Debt = D12
Deficit (-) or Surplus (+ % GDP)      for Low Debt = C13, for High Debt = D13
Real GDP Growth Rate (%)             for Low Debt = C14, for High Debt = D14

For this model we'll set up two states (where we look at the year before).  If the previous year's debt is less than (or equal to) 90% of GDP we'll use the low debt values.  Alternatively for greater than 90% of GDP we'll use the high debt values.

The interest rate on debt is really tough to pin down.  In trying to be simple we are now confounded by the fact that you can hold a 1-year treasury bill which pays you for what you thought of the government's riskiness this year or hold a 30-year treasury bond which pays you for what you thought of the government waaaaaay in the past.  An alternative, all-encompasing, way to think about the interest on our debt is that in 2011 we spent $251 Billion in interest on somewhere around $16 Trillion which is only... like 1.5% interest?  And don't forget, that is nominal interest.  If inflation right now is at 2% then loaning the US government money for 1 year gets you a negative return.  But imagine you are an Greece, which as recently as last year had some 10-year notes as high as 30%, and now issues at around 8%.  What do you pay for your debt?  I'm going to leave this up to the reader and let them insert values for interest between 0 - 5%.

Inflation is a bit easier to track.  For one thing, we have good numbers:

Long-term, the inflation rate has been semi-volitile (averaging 3.37%), but if you had to pin down one or two numbers I'd go with periods of 2.5%, which is about what the Fed targets, and periods of >5% when people get very unhappy. Looking back up at the debt graph again, it is tough to say that high inflation really correlates with debt (and if anything our lowest recent debt was when we had highest inflation).  Lets give people the chance to make up their own minds and go with a range of 2.5%-10%.

Running a deficit or surplus is completely out of the scope of this experiment, but it happens so I guess we should model it.  Whether or not the current level of debt encourages deficits or surpluses (surpli?) is an argument that I'd encourage in the comments.  As a reference, our deficits in the 80s were about -2% GDP, and in the mid 00s neared -3%.  Our surplus in the late 90s was near +2%.

Finally, we get to the point of this: GDP growth.  Our real GDP growth has averaged about 3% since 1945, with an average of about 3.8% in the good times.  As I mentioned earlier, RR and HAP differed on their value for countries at > 90% debt (finding -0.1 and +2.2% respectively).

Nominally Keeping it Real

Because we pulled out inflation as a separate variable, we need to make sure that we change the real GDP growth to nominal growth (otherwise we'd be losing growth to inflation twice!)  The readers won't be able to change this, but it will be listed so that they can see it.

Nominal Growth for Low Debt: (C15) [=((C14*.01+1)*(C12*.01+1)-1)*100]
Nominal Growth for High Debt: (D15) [=((D14*.01+1)*(D12*.01+1)-1)*100]

Time For Sum Fun

For year zero, the GDP value will be the initial GDP minus whatever financial hit we take from the crisis, plus any additional amount from a stimulus. The debt value will be initial debt plus any additional stimulus. While I'm at it lets make a second model with no stimulus and a third with no crisis.

For year 1, the GDP will have three components. There is the GDP from last year plus the GDP growth plus respent stimulus (which is dependent on the year number so it decays exponentially). Divide by the inflation.  Set it up as an "if" statement so it uses the correct variables.

For debt, we will add last year's debt plus interest on last year's debt plus (minus) any deficits (surpluses) and divided by inflation. Again, we will set it up as an "if" statement.

Now Complete the columns.  I put a little ratio in to see which scenario has the larger GDP after 30 years, but other than that the model is pretty much made.  Lets take it for a test drive!

Check Please

The first thing we should check for is what happens to the output during a normal amount of time.  Lets take the years 1955-1970.  We had decent growth, declining debt, and if we cut off in '70 we don't have to deal with the inflation spike:

With that info, I put in these inputs and got these results:

So we have about a 75% increase in real GDP and a drop in debt (as a % of GDP) from the low 60s to the low 30s.  This matches the actual data very closely.  This also tells us that in good times (if we are actually interested in it) we can decrease the debt (without running a surplus) by about 2% a year.  This is the idea in "growing out of debt".

Crisis and Stimulus

So lets look at 2008 in the model.  These are the starting points I'll assume.

If you replace the GDP lost in crisis with debt fueled stimulus (that is not propagated by re-spending it), you end up with the same amount of GDP as if you had no crisis at all.  This is kind of cheating though because the debt never went above 90%.  Lets try it again with a little nudge to the original debt.

  

While the stimulus replaced the GDP lost in the crisis, we also lost some GDP growth when we were above 90% debt for two years.  Importantly, we are still much better off than if we had crisis but no stimulus.  Because the crisis removed some GDP from the denominator of our debt/GDP ratio, even our no-stimulus model ended up going over 90% debt.  Stimulus becomes progressively more needed as the crises get worse.  This is my best guess at what the real conditions might be:

Here the stimulus definitely helped, but we still feel the pain.  Interestingly in this scenario the stimulus model experienced 3 years above 90% debt but the no-stimulus model experienced 6 years.  One big question (and possible flaw) with these circumstances is what the "re-spend" is on stimulus dollars.  If it is set to 95%, like I'd expect from our savings rate, you end up with impossibly rosy situations where we are better off than if there was no crisis.  Granted this takes 5-10 years to notice:

So yeah, I think I'll keep the re-spend toned down.  Lets see what changing to the RR high debt growth rate and higher inflation would do to the model:

Not Pretty.  In this instance the inflation actually helps with the debt (which otherwise goes spiraling out of control), and you can see that while it doesn't get us back to no-crisis, the stimulus definitely is better than no-stimulus.  Anyways, you are probably tired looking at my tables.  

I suggest you go play around with the model on your own!!!1!1!!

Please do keep in mind that this is a communal google spreadsheet, and other people may be working on it at the same time.  Don't mess up someone else's work until they have had a chance to see their answer (unless you really really know that they are a doo doo face that you want to mess with).  Many of the cells are locked, and the variable ones only accept certain values.  If you would like to recommend a different value please submit your idea in the comments and I will add it.

Take Home Message

So what have we learned?  Well, for the most part, stimulus is a good way to squeeze out extra GDP from your economy.  It takes very specific characteristics when you have high debt (high interest rates, low inflation, low growth, little re-spending) for stimulus to be worse than no stimulus.  Can we grow out of debt (regardless of stimulus)?  Absolutely.  The big helper here is inflation.  With moderate inflation and low interest it is possible to continually run a deficit and still decrease your real debt.  That said, I'd much rather we were at a debt level where no one even questioned our credit-worthyness.  At 2% decrease a year we could even get there in my lifetime!

-----

If you want to subscribe, send an email to overly-complicated-excel+subscribe@googlegroups.com and you will be added to the list. The blog will email that list whenever it posts are published.