Yield on cost dashboard excel - if you have ever wanted to see, in one screen, exactly how much income each dollar you invested years ago is producing today, this is the template for that. A traditional spreadsheet shows current yield. A yield on cost (YOC) dashboard tells you what your past self bought, what the dividend has done since, and what the compounding looks like if you keep going. This guide ships a premium, dashboard-style Excel template that does all of that in one place, with KPI tiles, embedded charts, a sector heatmap, scenario projections, and live MarketXLS dividend formulas wired through every sheet.
The template is built to look like a professional-grade dashboard product, not a plain spreadsheet. Ten sheets, branded cover page, frozen panes, conditional formatting, dropdown form controls, and an allocation sizer that scores each holding from yield on cost, payout safety, and dividend streak length. Download both the static sample (with formula references shown as cell comments) and the live MarketXLS formula version at the end of this post.
Yield on cost vs current yield - the quick reference
Before going further, the single most useful table in the entire dividend-investor toolkit:
| Holding | Bought | Cost Basis | Current Price | Current DPS | Current Yield | Yield on Cost |
|---|---|---|---|---|---|---|
| KO | 2012 | $28.50 | $66.20 | $2.04 | 3.10% | 7.16% |
| LOW | 2015 | $72.10 | $243.50 | $4.96 | 2.05% | 6.88% |
| MCD | 2014 | $95.50 | $276.20 | $6.76 | 2.45% | 7.08% |
| AAPL | 2014 | $28.50 | $212.50 | $1.00 | 0.47% | 3.51% |
| MSFT | 2016 | $56.80 | $441.80 | $3.32 | 0.75% | 5.85% |
| NEE | 2017 | $41.80 | $72.40 | $2.06 | 2.95% | 4.93% |
Two things jump out. First, every YOC number is higher than the current yield - that is what compounding dividend growth looks like over a decade. Second, the spread is not uniform. Some holdings (KO, LOW, MCD) have produced YOC numbers more than double their current yield because of long, steady dividend growth. Others (AAPL) carry a low headline yield but have already lifted YOC several times above the cost-basis starting point.
The yield on cost dashboard tracks all of this in a single workbook so you do not have to recompute it every quarter.
What is yield on cost, and why a dashboard
Yield on cost is the annual dividend per share divided by the original cost basis per share. If you bought a stock at $50 a share and it pays $3 today, your YOC is 6 percent regardless of what the stock currently trades at. It is a personalized measure of how productive each dollar you have already invested is, right now.
Three reasons dividend investors care about YOC:
- It rewards patience. A stock with a 2 percent starting yield that grows the dividend 10 percent a year will, in roughly 11 years, have a YOC above 5 percent (rule of 72 with dividend growth). Current yield never tells that story because it always uses the latest market price as the denominator.
- It anchors the income decision separately from the price decision. The market price can drop 30 percent in a drawdown; YOC does not move. As long as the dividend is paid, YOC stays where it was - which is one reason long-term dividend investors lean on it as a sanity check during volatility.
- It surfaces the slow compounders. A boring utility or staples name that grows the dividend 5 percent a year for 25 years can have a YOC nearer 15 percent than 3 percent. That kind of compounding is invisible if all you ever look at is current yield.
The catch is that YOC is hard to track without a spreadsheet because it depends on a value (your cost basis) that no broker dashboard surfaces alongside live dividend per share figures. A yield on cost dashboard fixes that. Plug your shares, cost basis, and purchase year into the Holdings sheet; the rest of the workbook recomputes YOC per holding, weighted YOC across the portfolio, projected income at 1, 5, 10, and 20 years, sector exposure, and a suggested allocation tilt.
What is inside the template
Ten sheets, each tuned for one job. The whole workbook is designed to be opened, glanced at, and understood in under a minute.
Sheet 1 - Cover
Branded cover page with a large title, subtitle (May 2026 Edition, Dividend Portfolio Income Tracker), version line, data-as-of date, a table of contents, and a MarketXLS credit. Gridlines are hidden, the tab is colored navy. This is what someone sees first when they open the workbook - the spec is that it should not look like a spreadsheet, it should look like the cover of a product.
Sheet 2 - How To Use
Step-by-step tutorial: enter your holdings, set global inputs, read the Dashboard, run a projection, read the sector view, size new positions. Each step lives in its own section with body copy plus a footer listing every MarketXLS function referenced on the sheet.
Sheet 3 - Dashboard
The headline sheet. The top rows hold a KPI tile row of six tiles:
- Annual Income - total annual dividend income across the portfolio
- Weighted YOC - portfolio-weighted yield on cost (income divided by cost basis)
- Total Cost Basis - what you originally paid, summed
- Portfolio Value - what the holdings are worth today
- Current Yield - annual income divided by current portfolio value
- Years to Double - rule-of-72 estimate using the assumed dividend growth rate
Below the tiles, a conditionally formatted holdings table shows every position with YOC (red-amber-green color scale), annual income (data bars), payout ratio (green-safe to red-stretched), dividend streak length (data bars), and gain percent (three-arrow icon set). Two embedded bar charts compare YOC by holding and annual income contribution by holding.
The Dashboard tab has gridlines hidden, frozen panes set on row 11, print area set, and tab color set to MarketXLS blue.
Sheet 4 - Holdings
The only sheet a user ever needs to edit. Yellow input cells with gold borders hold:
- Four global inputs: target YOC, assumed dividend growth, monthly contribution, reinvest yes-no toggle (with dropdown)
- Three scenario toggles: income goal tier, sector tilt, payout safety filter (each a dropdown)
- 24 portfolio rows: ticker, shares, cost basis per share, purchase year (all yellow input cells)
Every other sheet pulls from this one. Change a ticker and the Dashboard, Income Projection, Strategy Playbook, Allocation Sizer, and Sector Comparison all update.
Sheet 5 - Income Projection
Six scenarios across columns: no-reinvest at 4, 6, and 8 percent dividend growth, plus full-reinvest at the same three growth rates. Output columns project annual dividend income at year 1, 5, 10, and 20. A bar chart visualizes the year-20 outcome. A notes block explains the assumption and caveats.
Sheet 6 - Strategy Playbook
A sector tilt table that aggregates median YOC, median payout, and median streak length by sector, then assigns a tilt (Overweight, Modest Overweight, Neutral, Underweight) with a one-sentence rationale. Below it sits an income goal tier playbook that maps Conservative, Balanced, and Aggressive settings to a different filter recipe each. Conditional formatting colors the YOC and payout columns; data bars score the streak.
Sheet 7 - Allocation Sizer
A composite score per holding: 50 percent YOC, 30 percent payout safety, 20 percent dividend streak length (capped at 50 years and normalized). Target weight equals score divided by sum of scores; dollar allocation equals target weight times total cost basis. A pie chart visualizes the suggested split. Edit the score weights to swap in your own factor recipe.
Sheet 8 - Sector Comparison
Sector aggregates: median YOC, median current yield, median payout, median streak, and share of total annual income. Conditional formatting heatmaps every numeric column. A pie chart shows the income share by sector so you can spot single-sector concentration immediately.
Sheet 9 - Methodology
A long-form explainer: how YOC is defined, how the weighted version is calculated, how the years-to-double figure works, what data the formulas pull, what the projection formulas assume, and where the model breaks down (taxes, price moves, dividend cuts). Lifts perceived value and helps a user audit the workbook themselves.
Sheet 10 - Glossary and Disclaimer
Plain-English definitions of every term used in the workbook plus the educational-only disclaimer. Yield on cost, dividend per share, forward dividend rate, current yield, payout ratio, dividend streak, dividend growth rate, cost basis, reinvestment, beta, sector tilt, drip compounding, income goal tier.
How the live MarketXLS formulas work
The template version replaces every static value with a live MarketXLS function so the workbook keeps re-pricing every time you open it. The core formulas, all verified against the MarketXLS function documentation:
=QM_Last("KO") - Live last price for portfolio value calculations
=DividendPerShare("KO") - Trailing twelve-month dividend per share, the YOC numerator
=DividendYield("KO") - Current dividend yield (live, against today's price)
=ForwardAnnualDividendRate("KO") - Forward annual dividend rate (most recent quarter x 4)
=DividendPayoutRatio("KO") - Trailing payout ratio
=ConsecutivePeriodOfIncreasingDividendPayout("KO","Yearly") - Streak length in years
=FiveYearAverageDividendYield("KO") - Five year average current yield
=Sector("KO") - GICS sector classification
=Beta("KO") - Beta versus market
=PERatio("KO") - Trailing PE ratio
=MarketCapitalization("KO") - Market cap in dollars
The YOC formula itself is one of the simplest in the workbook:
YOC = DividendPerShare(ticker) / cost_basis_per_share
In the live template this becomes, for the KO row of the Holdings sheet:
=DividendPerShare("KO") / Holdings!G10
where G10 is the user-entered cost basis. Update the cost basis cell and the YOC recomputes instantly. Update the ticker and every downstream formula reroutes.
For the weighted portfolio YOC tile on the Dashboard:
=SUM(J11:J34) / SUM(N11:N34)
J is annual income (shares times DPS), N is cost basis (shares times cost). The result is the dollar-weighted yield on cost across all 24 holdings.
For the years-to-double tile, the workbook uses the dividend-growth version of the rule of 72:
=LN(2) / LN(1 + Holdings!$C$6/100)
At 6 percent assumed dividend growth, that resolves to roughly 11.9 years before the dividend income doubles even if you never add a dime to the portfolio - the pure compounding case.
The current setup - what May 2026 looks like for a dividend grower portfolio
A few snapshots from the sample workbook, captured 2026-05-29, that help frame the moment:
- Sample portfolio cost basis: $107,995 across 24 long-running dividend growers
- Current market value: $289,655 (a substantial mark-to-market gain after a decade of buy-and-hold)
- Annual dividend income: $5,817 (or roughly $485 per month)
- Weighted YOC: 5.39 percent
- Current yield (against today's market value): 2.01 percent
The gap between the 5.39 percent YOC and the 2.01 percent current yield is the entire dividend-growth thesis in one number. The portfolio is earning the equivalent of a 5 percent bond against original capital, but anyone buying today at the same prices would have to settle for the 2 percent current yield. That is the embedded value of having held aristocrats and high-quality dividend growers through a decade of dividend increases.
In late May 2026, with the 10-year Treasury near 4.3 percent and corporate bond yields offering a similar range, this matters because the static-yield comparison ("why hold stocks at 2 percent when bonds pay 4 percent") completely ignores YOC growth on existing holdings. A bond pays the coupon you bought; a quality dividend grower lifts the dividend roughly in line with earnings growth, year after year, for decades. The dashboard makes that distinction visible.
How to build a yield on cost dashboard from scratch in Excel
If you want to understand what is happening inside the template before you trust it, here is the minimum-viable construction in five steps.
Step 1 - Set up the Holdings sheet
A single table with columns: Ticker, Shares, Cost Basis Per Share, Purchase Year. Make the input cells yellow with a bold border. Add data validation dropdowns for the reinvestment toggle (Yes / No).
Step 2 - Pull live values
For each ticker in the holdings table, add columns for live current price (=QM_Last(ticker)), live dividend per share (=DividendPerShare(ticker)), live current yield (=DividendYield(ticker)), payout ratio (=DividendPayoutRatio(ticker)), and streak (=ConsecutivePeriodOfIncreasingDividendPayout(ticker,"Yearly")).
Step 3 - Compute YOC and annual income
YOC equals the live DPS divided by the cost basis. Annual income equals shares times live DPS. Both are one-cell formulas referencing earlier columns.
Step 4 - Build the KPI tiles
Sum the annual income column for the income tile. Divide total income by total cost (sum of shares times cost) for weighted YOC. Sum shares times current price for portfolio value. Divide total income by portfolio value for current portfolio yield. Use =LN(2)/LN(1+DGR) for years-to-double.
Step 5 - Layer on conditional formatting
Color-scale the YOC column red to green. Data-bar the annual income column. Color-scale the payout column green (safe) to red (stretched). Add a three-arrow icon set to the gain percent column. None of this is decoration - it is what makes the dashboard skimmable at a glance.
That is the minimum viable build. The premium template ships with a cover page, scenario sheet, sector comparison, allocation sizer, methodology, and glossary on top of this core - all polish that pushes it from a working spreadsheet to a presentation-ready dashboard.
Reinvestment - the YOC accelerator
Yield on cost grows on its own as the dividend grows. Reinvested dividends accelerate it. The mechanism is simple: every reinvested dividend buys more shares at the prevailing price. Those new shares immediately start paying dividends. Income grows from both the rising dividend per share and the rising share count.
The Income Projection sheet compares the two paths side by side. A representative no-reinvest scenario at 6 percent dividend growth starting from a 5 percent YOC projects roughly:
- Year 1: starting income
- Year 5: ~26 percent more income (dividend growth alone)
- Year 10: ~60 percent more income
- Year 20: ~160 percent more income (income has roughly tripled)
A full-reinvest scenario at the same starting yield and 6 percent dividend growth assumes every dividend re-enters at the same yield. That compounds aggressively - year 20 income lands several multiples higher than the no-reinvest case because the share count has been growing along with the dividend per share.
The catch with the full-reinvest math: it implicitly assumes the yield available for reinvestment stays at the starting yield. In practice, you reinvest at the prevailing market yield, which moves around. The model trades simplicity for honesty by labeling these as projections, not forecasts, and reporting both paths so the bracket is visible.
Portfolio construction - using the dashboard
The Allocation Sizer sheet is where the dashboard becomes opinionated. It scores each holding on a composite of YOC (50 percent weight), payout safety (30 percent weight, calculated as 1 minus payout ratio), and streak length (20 percent weight, capped at 50 years and normalized). The score determines a suggested target weight; the target weight times total cost basis gives a suggested allocation in dollars.
This is a starter framework, not a prescription. Three reasons to tune it:
- Your goal might not be the default goal. The default weights are tuned toward an income-first portfolio. A growth-first investor should down-weight YOC and up-weight five-year DGR.
- The score does not capture qualitative views. A stock with a great YOC and safe payout might be in a sector you simply do not want to overweight. Manual overrides matter.
- The composite is one composite among many. Other valid factor recipes include yield + DGR, free cash flow yield + payout, or a quality factor based on ROIC. All are valid; the workbook just makes the math transparent.
The Strategy Playbook sheet layers a sector tilt on top. Sectors with high median YOC, safe payouts, and long streaks (Consumer Staples, Utilities, parts of Healthcare) get overweighted in the default view. Sectors with stretched payouts or thin YOC get underweighted. The income goal tier playbook then translates Conservative, Balanced, and Aggressive into a different filter set on yield, payout, beta, and streak length.
Risk - the things YOC does not see
Yield on cost is a powerful framing tool, but it has blind spots.
YOC is fixed at your cost basis. It does not change when the stock drops 50 percent. That is the upside (calm during drawdowns) and the downside (a dividend cut from a falling stock will look identical on the YOC line right up until the cut). Payout ratio, free cash flow coverage, and balance sheet health are the second-order checks the dashboard surfaces alongside YOC for this reason.
Dividends are not guaranteed. A 50-year streak does not guarantee a 51st. The 2020 and 2009 periods both produced cuts among names previously considered untouchable. The Strategy Playbook overweights long-streak, safe-payout names because the base rate is encouraging, not because the outcome is certain.
Tax wrapper matters. YOC in a taxable account is partially eroded by qualified-dividend tax (currently 15 to 20 percent for most US investors). The same YOC in a Roth IRA is not. The dashboard does not model the tax wrapper - that is an exercise for your tax accountant.
Reinvestment risk. The full-reinvest projection assumes you can reinvest at the same yield. If the market re-rates a name higher, you reinvest at a lower yield; if it re-rates lower, you reinvest at higher yield but face the implied risk that caused the re-rating.
Concentration risk. A portfolio of 24 dividend growers can still be heavily exposed to one sector (the sample portfolio shown above leans toward Consumer Staples and Industrials). The Sector Comparison sheet exists to make that exposure obvious so it cannot hide.
Building a watchlist around the dashboard
The 24-stock example portfolio in the template is intentionally diverse: 5 Consumer Staples (KO, PG, PEP, WMT, plus add your own), 2 Consumer Discretionary (MCD, LOW), 3 Healthcare (JNJ, ABT, MDT), 2 Energy (XOM, CVX), 4 Industrials (MMM, CAT, HON, GD), 3 Information Technology (AAPL, MSFT, TXN), 2 Financials (AFL, BLK), 1 Materials (APD), 2 Utilities (NEE, DUK), and 1 Real Estate (O).
That mix is not a recommendation - it is a sandbox to test the dashboard with names that have long, well-documented dividend histories. To make the dashboard your own, replace each ticker with one you actually hold, and enter the share count, cost basis, and purchase year that match your brokerage records.
The MarketXLS formulas accept any ticker symbol the underlying QuoteMedia data feed supports, which covers all major US and many international listings. For deep historical research on a particular dividend grower, the Dividend Aristocrats Dashboard template ships a complementary 28-stock universe of names with at least 25 years of consecutive increases.
Download the templates
Both files are free to download.
Download the templates:
- - Pre-filled with the May 29, 2026 sample portfolio; every data cell carries a comment showing the MarketXLS formula that would produce it.
- - Live formulas, zero static data. Open with MarketXLS installed and every cell re-prices on workbook open.
Open either file in Excel for the cover page, KPI dashboard, sector heatmap, scenario projection, and allocation sizer all in one workbook.
FAQ - Yield on cost dashboard excel
What is the difference between yield on cost and current yield? Current yield uses the current market price as the denominator. Yield on cost uses the original cost basis. They start at the same number on the day you buy a stock and diverge as the stock price moves and the dividend grows. A stock that has doubled in price and doubled the dividend will have a YOC twice its current yield.
Does yield on cost change over time? Yes, but only when the dividend per share changes. If a company raises the dividend, your YOC goes up. If they cut, it falls. The stock price has zero direct effect on YOC because the denominator (cost basis) is fixed.
Is yield on cost the same as dividend yield? No. Dividend yield is the live measure (DPS divided by current price). Yield on cost is the personalized measure (DPS divided by your original cost basis). They are calculated differently and serve different purposes.
Can I use this template for international stocks? Yes, as long as the MarketXLS data feed covers the ticker. Many international names listed on US exchanges (ADRs and dual listings) work directly. For local-market tickers, check the MarketXLS function documentation or contact support to verify coverage.
How often should I refresh the dashboard? Live MarketXLS formulas refresh on workbook open. For portfolio review purposes, monthly is usually frequent enough to capture meaningful dividend changes. The sector heatmap and allocation tilt only shift when company-level data shifts, which moves quarterly at most.
How is "years to double" calculated? Using the dividend-growth version of the rule of 72: years = LN(2) divided by LN(1 + DGR). At a 6 percent assumed dividend growth rate, that is approximately 11.9 years. The formula assumes no dividend cuts and a constant growth rate, neither of which is guaranteed.
Why does the template use a 50/30/20 score in the Allocation Sizer? The weights front-load yield on cost (the income metric an investor most directly cares about) while preserving safety (via payout ratio) and discipline (via streak length) as secondary filters. These are defaults, not prescriptions - you can edit the formula in column F of the Allocation Sizer sheet to swap in any composite you prefer.
Does the template show capital gains? The Dashboard holdings table includes a Gain Percent column comparing current market value to cost basis, but the workbook is built around income, not total return. For a price-and-total-return view of the same portfolio, pair this with a separate total-return tracker.
The bottom line
Yield on cost is the single most useful framing for a long-term dividend investor, and a dashboard is the natural format because YOC depends on personal data (your cost basis) that no broker dashboard surfaces alongside live company data. This template combines both: yellow input cells for your portfolio, live MarketXLS formulas for every dividend metric, and ten polished sheets that turn the raw inputs into KPI tiles, charts, a sector heatmap, scenario projections, and an opinionated allocation tilt.
The whole workbook is built around three premises: that dividend growth compounds quietly and is invisible without YOC, that payout safety and streak length are the right second-order filters, and that an investor who can see all of this on one screen makes better decisions than one who only sees a static current yield.
To explore the underlying MarketXLS dividend functions in more depth, visit marketxls.com or book a demo to see how the formula library powers premium portfolio templates like this one.
