Tutorial: How to Use the Valuations Tab in the DGI Spreadsheet
Introduction
Hey, fellow dividend growth investors!
If you’re like me, building a retirement portfolio through individual stocks means digging into the numbers to find those hidden gems that can compound your passive income over time. My free “Valuations” tab in the DGI stock valuation spreadsheet is one of my favorite tools for exactly that.
Packed with key metrics on market stats, dividends, and growth projections, this tab helps you quickly screen and compare stocks for long-term holding.
Whether you’re a beginner screening for your first dividend aristocrat or a seasoned investor refining your watch list, this guide will walk you through it step by step. Let’s turn those rows of data into actionable insights for your retirement snowball!
Download the free DGI Portfolio Spreadsheet here → [Resources Page Link]
What The Stock Valuation Spreadsheet Is Good For
This tab lists both current and potential candidates for my personal dividend growth portfolio. It pulls in real-time-ish data (updated weekly) on:
- Market stats and valuation metrics
- Dividend yields and growth projections
- 3-year return forecasts
Use it to compare stocks across sectors, filter for high-yield opportunities, project future income, or spot undervalued companies paying you to wait while their payouts grow.
Pro tip: Make a copy in your own Google Drive and tweak the formulas to match your criteria!
Step-by-Step Breakdown of the Columns
Here’s a snapshot of the stock valuation spreadsheet:
The spreadsheet starts with headers in row 1: Ticker, Name, GICS Sector, Market Cap ($B), Price, Price % Change, 52-Week High/Low, % Below/Above 52-Wk, Dividend, Forward Yield, EST. EPS Growth, EST. DIV. Growth, Current FY EPS Estimate, PE, Normal F.V. PE, Delta PE, Payout Ratio, Est. 3YR Tot. Return, Est. 3YR CAGR, Est 3YR YOC, Payout Target.
Basic Identification Columns
Ticker and Name: The stock ticker and full company name. For example, ADP for Automatic Data Processing Inc. Start here to search for familiar names or discover new ones.
Market Cap ($B): Company size in billions (e.g., 107.6 for ADP). Why it matters: Larger caps like this tend to be more stable for retirement portfolios, but smaller ones might offer faster growth.
GICS Sector: Refers to the classification of companies into 11 major economic sectors using the Global Industry Classification Standard (GICS), a standardized system jointly developed and maintained by MSCI and S&P Dow Jones Indices since 1999. Knowing and tracking the 11 GICS sectors gives you an instant framework to build a balanced portfolio.
Price and Valuation Metrics
Price and Price % Change: Current share price (e.g., $266.10 for ADP) and daily change (e.g., 0.95%). Use this to gauge short-term momentum—positive changes might signal a good entry point.
The color-coding for this column shows stocks with negative daily price changes in red and positive returns in green.
52-Week High/Low and % Below/Above: Shows the price range over the past year (e.g., ADP high $329.85, low $247.18). % Below High (e.g., -19.33%) highlights discounts—aim for stocks 10-20% below highs for value buys. % Above Low (e.g., 7.65%) indicates recovery strength.
The color coding for this column shows stocks within 5% of 52-week highs in green, within 5-10% of highs in yellow, and more than 10% off highs in red.
Dividend and Growth Metrics
Dividend and Forward Yield: The annual dividend being paid by the company. Forward Yield is your expected annual return from dividends alone. For retirement, target yields of 3-5% with growth to combat inflation.
The color coding for this column is dark red for dividend yields between 0-1%, light red for 1-2%, yellow for 2-3%, light green for 3-4%, and dark green for 4%+.
EST. EPS Growth and EST. DIV. Growth: Projected earnings per share growth (e.g., 9% for ADP) and dividend growth (e.g., 9%). High double-digit growth here screams compounding potential—use the Chowder Rule (yield + div growth > 12%) for quick screens.
Current FY EPS Estimate and PE: EPS forecast for the fiscal year (e.g., $10.92 for ADP) and price-to-earnings ratio (e.g., 24.4). A lower PE (under 20) often indicates an undervalued stock; compare it to the Normal F.V. PE (fair value PE, e.g., 28.0) for potential bargains.
The color coding for this column shows EPS estimates in white, AFFO estimates in cyan, and operating cash flow per share (OCF) in pink. The AFFO estimates are typically used for real estate investment trusts, while OCF is used for Amazon, which historically trades off this metric rather than EPS.
Delta PE and Payout Ratio: Difference between current and fair PE (e.g., -12.97%)—positive means overvalued, negative undervalued.
Payout Ratio (e.g., 62.3%) shows % of earnings paid as dividends. This varies depending on the sector and business type. Slow, steady growers in the utility, real estate, and consumer staples sectors have higher payout ratios, while fast growers like consumer discretionary and tech stocks are typically lower.
Return Projections
Est. 3YR Tot. Return, Est. 3YR CAGR, Est 3YR YOC: Projected total return over 3 years (e.g., 57.18%), compound annual growth rate (e.g., 16.27%), and yield on cost (e.g., 3.3%). These are gold for retirement planning—aim for 10%+ CAGR to beat the market. Use yield on cost to compare different companies’ future expected dividends.
Color Coding Explained
The color variations on different tabs are meant as a visual tool or heat map to give a quick view for comparison.
Yellow = moderate
- Light green = trading near 52-week highs or strong yields
- Light red/dark red = trading well below highs or low yields
- Yellow = moderate
This coloring scheme carries through on the valuation, return, and yield projection tabs as well for fast scanning.
How to Use The Stock Valuation Spreadsheet: Practical Tips for Your Portfolio
Now that you understand the columns, here’s how to actually use the sheet in your investing process.
- Sorting and Filtering: In Google Sheets, click the filter icon (or Data > Create a filter) on row 1. Sort by Forward Yield descending for high-payers, or EST. DIV. Growth for fast growers. Filter % Below 52-Wk High > -0.10 for “on sale” stocks.
- Screening for DGI Winners: Look for stocks with Yield > 2%, Div Growth > 7%, Payout Ratio < 65%, and Delta PE negative. For instance, ADP (Yield 2.56%, Div Growth 9%, Payout 62.3%) has solid metrics for a recurring income business.
- Projecting Retirement Income: Use Est 3YR YOC to forecast: If you invest $10K in a stock at 3% yield growing 10%/year, YOC could hit 4-5% in 3 years. Plug your shares into a copy of the sheet.
- Comparing to Your Holdings: Cross-reference with my “Port” tab. For example, see how ABBV’s metrics stack up against XOM for diversification.
- Common Pitfalls to Avoid: One common mistake that investors make is to chase yield alone—check growth to ensure payouts rise with inflation. Update data manually if formulas break, and always DYOR (do your own research) before buying.
Download the Portfolio Dividend Tracker Spreadsheet and experiment! Share your screens with me on social media—I’d love to see how you’re using it for your retirement goals. This tool helped me grow my annual dividends from $2K to over $4K in 7 years; it can do the same for you.
How I Use It: Stock Valuation Watch List
I track over 200 companies with my personal watch list and post regular updates of the top companies on my social media accounts. That live watch list is available at the link below for my readers. This is available at any time to view and find investment ideas for your portfolio. All I ask in return is that you view it from my site directly, and that if you find it useful, please share it with others.
Happy investing!
Eric – DGI for the DIY