S&P 500 Calculator: Warren Buffett Valuation Spreadsheet

This Tool Can Bring Calm to Chaos

Reverse inquiries looking for our Buffett 10-Year Returns Estimation Tool suggested another post was in order. We’ll explain where the tool came from, how to use it and why it is so important.

Get our insights direct to your inbox: SUBSCRIBE

Where the Tool Came From:

In Economic Cycles and Mean Reversion we walked through an article published in 1999 by Fortune Magazine. The report explained why Buffett believed growth rates in the stock market’s annual returns would collapse.

In December of 1999, one month after publication, Barron’s quoted a comment describing Berkshire as:

“…a middlebrow insurance company studded with a bizarre mélange of assets, including candy stores, hamburger stands, jewelry shops, a shoemaker and a third-rate encyclopedia company.”^[1]

The market plunged nearly 50% not long after that beauty came out. Over his investing career Mr. Buffett has run into bouts of doubters and skeptics. He has proven them wrong again and again.

The 1999 article in Fortune laid out Buffett’s thinking on how stock market capitalization, economic growth and long term rates of return could be estimated. Fortune did a solid job explaining what Buffett uses to estimate long-term stock market returns. You need a bunch of data and math to copy his logic. In our experience, data and math are not everyone’s idea of fun.

How to Use the Tool:

The tool we built saves you the headache. The exhibit below is what the tool looks like. All you do is plug in your estimates in the blue cells.

1. How fast do you think the economy will grew, per year, over the next decade
2. What will profit margins be
3. What will price to earnings multiples will be
4. What will interest rates be

Warren Buffett Stock Market Valuation: Why You Need the Tool

Our research over the last 12 years has made the following items clear:

1. Forecasting is a fool’s game, particularly in the short term
2. Better to be roughly right than precisely wrong
3. To understand the market, you need to know where things are in historical context

Buffett thinks about markets in a way that benefits from both common-sense and a long history of being right. No grand forecasts required. Stick some numbers in the blue boxes.

The tool always teaches us something by showing how different assumptions impact outcomes. And that is the beauty of the tool: everyone can have their own opinions, see what it means, and go from there.

Please click to download in the Bespoke Tools research folder KCR’s Warren Buffett Valuation Spreadsheet.

We have a version of the tool annotated with a much more detailed walk-through of what each cell means. If you would like a copy of that please let us know and we will provide it. That version can be helpful but can also look overwhelming so often-times a call works best.

Evidence:

If you have questions about the math underpinning the spreadsheet call us. We provide some notes below:

In The Buffett Valuation Metric, we explained the empirical evidence that Market Cap to GDP was the best single measure of where valuations stand. That single factor alone does a terrific job of giving you a simple estimate of where markets are going over the long-haul. Again and again market cap to GDP has been a guiding light for asset allocation.

The Buffett valuation tool contemplates a broader array of the inputs he uses when approximating the market’s long run potential. Buffett solves for 10-year future estimated returns with the following knowns: starting valuations, starting interest rates, starting profit margins, and starting GDP. After that, he contemplates future margins, interest rates, and valuations based on history.

Our team does the same thing. Not because we make investment decisions based on 10 year estimates of returns. We just find it helpful to know how expensive things are and what investors’ implied expectations are.

The tool allows you to use your own assumptions within Buffett’s framework to see how different scenarios would play out. The one complexity we readily acknowledge is around interest rates.

In 1999 Buffett observed that interest rates:

“…act on financial valuations the way gravity acts on matter. The higher the rate, the greater the downward pull. Conversely, if government interest rates fall, the move pushes prices of all other investments upward. … the effect – like the invisible pull of gravity – is constantly there. … If government interest rates, now at a level of about 6%, were to fall to 3%, that factor alone would come close to doubling the value of common stocks.”

Makes sense right? Interest rates fall from 6% to 3% you get a double in valuations. Intuitive. Reasonable.

The problems begin as you approach the zero bound. When Buffett said this he, like most of the world, probably wasn’t thinking that a time would come when borrowers would pay near-insolvent governments to lend them money (negative yielding bonds). As we all know, that is exactly what happened.

That same linear approximation does not hold true when rates fall from 0.50% to 0.25%. The simple linear approximation he gave of 6% to 3% nearly doubling valuations becomes ludicrous when rates hit the floor. We can’t be sure but believe that Buffett would agree with us.

For this part of the model we stuck with “simple” and used a linear regression. That regression is used to help rescale the subsequent market cap to GDP ratio that informs the final annual price return calculation. We’ll be the first folks with our hands up acknowledging that this is far from perfect and more complex methods are available.