If there’s one single method that you can count on to calculate the value of a stock, it’s discounted cash flow (DCF). Standard measures like price-to-earnings (P/E) are useful, but they’re just shortcuts. If you really want to figure out what something’s worth, DCFs are where it’s at. That’s why I built you this template, here. (Note you’ll need to download or make a copy of it before you can fiddle around with the cells.

What’s a DCF?

In valuing a stock, a DCF takes all the cash flows a firm will ever produce and discounts them back into today’s value. Now, forecasting all those cash flows is a long and arduous task, often fraught with prediction errors. I mean, who really knows what a firm's 10-years-from-now cash flow will be? So, instead of playing clairvoyant, this template lets you consider what the market’s pricing in, and decide whether you agree. Building bells and whistles DCFs takes accounting expertise and oodles of time. My template is a rough walk-in valuation checkup performed in minutes. See here for a more detailed look at a full-blooded DCF.

What growth is Apple’s stock price baking in?

Let’s look at the example I’ve used: Apple (APPL). Once you’ve inputted Apple’s ticker (cell B1), all you need to do is complete four more cells (shaded in blue) – the sheet does the rest for you.

• Starting cash flow (cell B10). This is the most recent annual free cash flow. You can pull this from free data providers like Koyfin or from company accounts, which you’ll find on the investor relations pages of a firm’s website. The quickest way to calculate free cash flow is to take the operating cash flow (OCF) and subtract capital spending (capex) – both found in the cash flow statement.

• An appropriate discount rate (B9). For most stocks, 8% is a healthy rate to use.

• Your forecast for cash flow growth for 10 years (B11).

• A forever-after growth forecast – known as the terminal value (B12). Your terminal growth assumption should be low enough to reflect reality. No firm can expand at 4% or 5% forever, so maybe go for a rate closer to global economic growth, along the lines of 2% or 3%.

Remember, you’re trying to compute a present value that’s close to the current market value included in the sheet and taken from Google Finance. It’s a bit of trial and error, so play around with your growth assumptions until you get your present value and the current market value to roughly match.

And that’s it. In the example, I’ve taken Apple’s most recent cash flow number of $111,433 million (i.e. $111 billion) for the fiscal year 2022, an 8% discount rate, and figured out that a 3.5% cash flow growth assumption for ten years and 2.5% thereafter calculates a present value similar to Apple’s actual market value. The question then becomes whether you think 3.5% growth for ten years and 2.5% after that is overly optimistic – or on the low side.

DCFs have their critics for sure, and small changes in the assumptions – like the discount rate – can have a big impact on the computed value of a firm. But that’s not really the point here. The template is designed to be a rough valuation check-up, to give you some idea of what the market is assuming for a company’s growth. If the market value of a firm requires some unearthly growth assumptions, that’s a red flag. But sluggish growth – maybe even in our Apple example – can prick the ears. That 3.5% isn’t aggressive, after all.