Stock Valuation Calculator Excel: Compare P/E, Dividends, and Fair Value for 10 Stocks

Stock valuation calculator Excel — if you're trying to determine whether a stock is cheap, fairly priced, or expensive, you need a structured analytical framework, not guesswork. Wall Street analysts use proprietary models costing thousands per seat, but the same fundamental analysis is completely achievable in a well-designed Excel spreadsheet. The key metrics — P/E ratio, dividend yield, market capitalization, revenue, and technical indicators like RSI — are all available through MarketXLS formulas that pull live data directly into Excel. This guide walks you through a complete six-sheet stock valuation workbook that takes you from raw data to actionable analysis.

10-Stock Valuation Dashboard at a Glance

Data as of 2026-03-15. P/E color coding: 🟢 Green (<20) = potentially undervalued | 🟡 Yellow (20-35) = fair range | 🔴 Red (>35) = premium pricing. In the MarketXLS template version, all values update live via =QM_Last("AAPL"), =PERatio("AAPL"), etc.

Why You Need a Stock Valuation Calculator in Excel

Figuring out whether a stock is cheap, fairly priced, or expensive is the central question of investing. Every investment decision — whether you're a financial advisor building client portfolios, a self-directed investor evaluating new positions, or a portfolio manager rebalancing sector allocations — ultimately comes down to valuation.

The challenge isn't accessing data — it's organizing it. You can find P/E ratios, dividend yields, and revenue figures across dozens of websites, but comparing 10 stocks across eight metrics simultaneously requires a structured framework. An Excel-based valuation calculator solves this by:

• Centralizing data — All metrics for all stocks in one place, updated live via MarketXLS formulas
• Enabling comparison — Side-by-side analysis across sectors (tech, financials, healthcare, energy, consumer)
• Supporting scenario modeling — What if the market re-rates a stock to a different P/E multiple? The scenario analysis sheet answers this instantly
• Automating classification — Stocks are automatically labeled as Value, Blend, or Growth based on P/E thresholds you control
• Facilitating position sizing — Allocation percentages and projected dividend income calculate automatically from your portfolio size input
• Providing technical context — RSI and SMA indicators supplement fundamental analysis with trend and momentum data

The 10-stock default lineup intentionally spans the growth-value spectrum: mega-cap tech (AAPL, MSFT, GOOGL, NVDA), financials (JPM, V), healthcare (JNJ, UNH), energy (XOM), and consumer staples (PG). This cross-sector coverage lets you see the full valuation landscape in a single dashboard.

Current Market Context: March 2026

As of mid-March 2026, markets are navigating a mixed environment that makes valuation analysis particularly important. Technology mega-caps have pulled back from late-2025 highs, creating potential entry points for long-term investors at more reasonable multiples. Meanwhile, traditional value sectors — financials, energy, healthcare — are trading at historically reasonable multiples with strong dividend support.

Several observations from the current data worth analyzing in your valuation calculator:

• JNJ (P/E 15.4) and JPM (P/E 12.8) are trading at multiples well below the S&P 500 average, solidly in "value" territory by historical standards. Both offer dividend yields above 2%, providing income while you wait for potential multiple expansion.

• NVDA (P/E 62.4) reflects the AI premium, but its RSI near 42 suggests recent selling pressure. The market is pricing in massive future earnings growth — the scenario analysis sheet helps you evaluate whether the current price is justified under different growth assumptions.

• XOM (P/E 14.2, Div Yield 3.4%) is a classic income play, trading below average market multiples with one of the highest dividend yields in the watchlist. Energy sector valuations remain compressed despite strong earnings.

• GOOGL, JNJ, and NVDA are all trading below their 50-day SMAs, which some technical analysts interpret as short-term weakness. This can represent either a buying opportunity (if fundamentals are intact) or a warning signal (if the decline reflects deteriorating business conditions).

• UNH (P/E 20.1) sits right at the boundary between "value" and "blend" — a quality compounder at a reasonable multiple with a 1.5% dividend yield.

The Six-Sheet Workbook: Complete Walkthrough

Sheet 1: How To Use

A comprehensive quick-start guide explaining every sheet, input cell, and formula in the workbook. Yellow cells are input cells throughout — change these to customize the entire analysis. Includes links to MarketXLS and the MarketXLS demo page, plus a complete formula reference for all MarketXLS functions used across all six sheets.

Sheet 2: Main Dashboard — The Valuation Command Center

The Main Dashboard is a matrix-style layout with your 10 tickers across the top and eight fundamental/technical metrics down the left side. Three yellow input cells at the top control the entire workbook:

• Portfolio Size ($) — Flows through to the Portfolio Allocation sheet for position sizing
• Max Position % — Prevents overconcentration in any single stock (default 15%)
• Risk Tolerance (1-5) — Qualitative input for portfolio construction guidance

The ticker row (Row 3) contains 10 yellow input cells — swap any ticker and the entire column of metrics updates instantly in the MarketXLS version. The eight metrics for each stock:

The matrix layout means you can scan across any row to compare a single metric across all 10 stocks, or scan down any column to see all metrics for a single stock. This dual-axis comparison is what makes the spreadsheet format so powerful for valuation analysis.

Sheet 3: Scenario Analysis — P/E Re-Rating Models

This is the sheet that transforms your stock valuation calculator from a data dashboard into a decision-making tool. For each stock, it calculates fair value estimates across six P/E scenarios (P/E 12, 15, 20, 25, 30, and 35).

The logic:

1. Derive implied EPS from current price and P/E ratio: EPS = Price ÷ P/E
2. Apply target multiples to implied EPS: Fair Value = EPS × Target P/E
3. Color-code results: Green (>10% above current price = potentially undervalued), Red (<10% below current price = potentially overvalued)

Two adjustable input cells control additional analysis:

• Growth Rate % (yellow, default 5%) — Expected annual EPS growth
• Discount Rate % (yellow, default 10%) — Required rate of return

Example from the template: JPM trades at $198.70 with a P/E of 12.8, implying EPS of approximately $15.52. If the market re-rates JPM to a P/E of 20 (the S&P 500 long-term average), the implied fair value is $310.40 — a significant premium to today's price. The scenario analysis sheet shows this calculation instantly, color-coded green to highlight the potential upside.

Conversely, if NVDA's P/E compresses from 62.4 to 35 (still a meaningful growth premium), the implied fair value is approximately $491 — well below the current $875.60. This scenario is color-coded red, highlighting the risk of multiple compression.

In the MarketXLS template version, these scenarios update live as prices and P/E ratios change:

=QM_Last("AAPL")  → Current price (updates live)
=PERatio("AAPL")   → Current P/E (updates with each earnings report)
=B4/C4             → Implied EPS (derived formula)
=D4*20             → Fair value at P/E 20 (derived formula)

Sheet 4: Strategy Classification — Value, Blend, or Growth

The Strategy Classification sheet automatically labels each stock based on P/E thresholds you control via two yellow input cells:

• Value threshold (P/E <) — Default: 20
• Growth threshold (P/E >) — Default: 30

Stocks are classified as:

• 🟢 Value (P/E < 20): JPM (12.8), XOM (14.2), JNJ (15.4) — trading at discounts to historical averages
• 🟡 Blend (P/E 20-30): GOOGL (22.4), UNH (20.1), PG (25.6), AAPL (28.3) — reasonably priced for their quality
• 🔴 Growth (P/E > 30): MSFT (35.1), V (30.5), NVDA (62.4), AMZN (58.2) — premium pricing reflecting growth expectations

The sheet also flags RSI extremes:

• RSI < 30: Oversold — potential buying opportunity (none currently in the watchlist)
• RSI > 70: Overbought — momentum may be stretched (none currently)
• RSI 30-70: Neutral — all 10 stocks are in neutral RSI territory as of March 2026

In the MarketXLS version, classification formulas update live:

=IF(PERatio("AAPL")<$B$2, "Value", IF(PERatio("AAPL")>$D$2, "Growth", "Blend"))
=IF(RSI("AAPL")<30, "Oversold", IF(RSI("AAPL")>70, "Overbought", "Neutral"))

Sheet 5: Portfolio Allocation — Position Sizing and Dividend Income

Enter your portfolio size and maximum position percentage, and this sheet calculates:

• Dollar allocation per stock based on your target percentage weights
• Share count at current prices (using =QM_Last("AAPL") in the live version)
• Annual dividend income per position using =DividendPerShare("AAPL") multiplied by share count

The default allocation spreads across all 10 stocks with weights from 8% to 12%, maintaining diversification while allowing slight overweights for higher-conviction positions. A $100,000 portfolio generates approximately $1,200 in projected annual dividend income from this cross-sector allocation — dominated by the higher-yield names (XOM, JNJ, PG, JPM).

The Max Position % input (yellow, default 15%) prevents any single stock from dominating your portfolio. This is critical during periods of conviction — it's easy to overweight your favorite idea and underestimate the risk of being wrong.

In the MarketXLS version, share counts update as prices change:

=INT($B$2*C5/QM_Last("AAPL"))  → Shares to buy at current price
=E5*DividendPerShare("AAPL")    → Annual dividend income for position

Sheet 6: Correlation & Comparison — Color-Coded Valuation Matrix

The comparison matrix provides an instant visual summary across all 10 stocks and seven metrics. P/E ratios are color-coded (green/yellow/red) so you can identify value and growth extremes at a glance.

The Above SMA? column adds technical context to the fundamental picture:

• Stocks above their 50-day SMA have positive short-term momentum — AAPL, MSFT, JPM, XOM, PG, V, and UNH are all trading above their SMAs
• Stocks below their 50-day SMA face technical headwinds — GOOGL, JNJ, and NVDA are below their SMAs

This combination of fundamental valuation and technical positioning creates a more complete picture than either approach alone. A stock that's fundamentally cheap AND has positive momentum is a stronger candidate than one that's cheap but in a downtrend (which may indicate deteriorating fundamentals the market hasn't fully priced).

Hypothesis: Value Opportunities in a Growth-Led Market

Here's an analytical framework to explore (not a recommendation — do your own research): In a market where mega-cap tech has dominated returns for years, traditional value metrics may be underweighting quality companies in financials, healthcare, and consumer staples. If economic conditions normalize and interest rates stabilize in the back half of 2026, stocks with P/E ratios under 20 and dividend yields above 2% could offer attractive risk-adjusted characteristics compared to high-multiple growth names.

The Scenario Analysis sheet lets you test this directly:

• If JPM re-rates from P/E 12.8 to a market-average P/E of 20, the implied fair value is approximately $310 — roughly 56% above current levels
• If XOM re-rates from P/E 14.2 to 18 (still below market average), fair value is approximately $143 — roughly 27% above current levels
• Conversely, if NVDA's P/E compresses from 62.4 to 35 (still a significant growth premium), implied fair value drops to approximately $491 — roughly 44% below current price

This is exactly the kind of analysis that a structured stock valuation calculator enables — not to predict the future, but to map out the range of outcomes and understand the risk/reward embedded in current prices.

MarketXLS Formula Reference

All formulas require the MarketXLS Excel add-in. The static sample version works without any add-in and includes formula references throughout.

Download the Templates

Download the templates:

• — Pre-filled with current data for 10 stocks as of 2026-03-15
• — Live-updating formulas that refresh every time you open Excel

The static version works in any spreadsheet application (Excel, Google Sheets, LibreOffice). The formula version requires the MarketXLS add-in for live data — book a demo to see it in action.

How to Use the Template: Step-by-Step

1. Open the Main Dashboard and edit the yellow ticker cells in Row 3 — replace any of the 10 default tickers with stocks you want to analyze. In the MarketXLS version, all metrics update instantly.

2. Check the Scenario Analysis sheet to see fair value ranges under different P/E assumptions. Adjust the growth rate and discount rate inputs (yellow cells) to match your expectations for each sector.

3. Review the Strategy Classification for automatic Value/Blend/Growth labels and RSI signals. Adjust the P/E thresholds if your definition of "value" differs from the defaults.

4. Go to Portfolio Allocation and enter your portfolio size, max position %, and risk tolerance in the yellow cells. The sheet calculates position sizes and projected dividend income automatically.

5. Use the Comparison Matrix for a quick visual comparison across all metrics. The color-coded P/E column instantly highlights which stocks are cheap vs. expensive relative to earnings.

6. Extend and customize — Add more stocks by extending the data rows. Explore additional MarketXLS formulas like =QM_GetHistory("AAPL") for historical price trends or =DividendYield("AAPL") for income analysis. Check the MarketXLS function library for 1,100+ available formulas.

Frequently Asked Questions

How do I change the stocks in the valuation calculator?

In the MarketXLS formula version, edit the yellow ticker cells in Row 3 of the Main Dashboard. Every formula in the workbook references these cells, so changing a ticker updates all metrics across all six sheets automatically. In the static version, you'll need to manually enter new data values, but the formula reference shows which MarketXLS function to use for each metric.

What P/E ratio indicates a stock is undervalued?

There's no universal "undervalued" P/E threshold — it depends on the sector, growth rate, and market conditions. Generally, P/E ratios below 15 are considered deep value territory, 15-20 is moderate value, 20-30 is fair for quality growth names, and above 30 requires strong growth to justify. The Strategy Classification sheet lets you set your own thresholds via yellow input cells. Always compare a stock's P/E to its sector average and historical range, not just the broad market.

Can I add more than 10 stocks?

Yes. Extend the data rows in each sheet and add your new tickers. In the MarketXLS version, copy a formula row and change the ticker. Update the Portfolio Allocation percentages to sum to 100%, and extend the Comparison Matrix to include the new stocks.

How does the Scenario Analysis differ from a DCF model?

The Scenario Analysis uses P/E re-rating methodology — it asks "what if the market assigns a different multiple to this stock's current earnings?" This is simpler and more intuitive than a full discounted cash flow (DCF) model, which requires assumptions about future free cash flow, terminal growth rates, and weighted average cost of capital. For quick relative valuation across multiple stocks, P/E re-rating is more practical. For deep-dive single-stock analysis, a DCF may be more appropriate.

What does it mean if a stock is below its 50-day SMA?

A stock trading below its 50-day simple moving average indicates that recent price action is weaker than the medium-term trend. This can signal short-term weakness, a pullback within an uptrend (potential buying opportunity), or the beginning of a more extended decline. Context matters — if the fundamental picture is unchanged, a dip below the SMA may be a technical entry point. If fundamentals are deteriorating (earnings misses, guidance cuts), the price weakness may be warranted.

How often should I refresh the data?

The MarketXLS formula version refreshes automatically every time you open the workbook — no manual updating needed. For the static version, refresh data weekly for ongoing monitoring, or at key decision points: before adding new positions, during earnings season, and when significant market events occur.

The Bottom Line

A stock valuation calculator in Excel provides the structured framework that separates analytical investing from guesswork. By combining fundamental metrics (P/E, dividend yield, revenue, market cap) with technical indicators (RSI, 50-day SMA) in a single dashboard, you can evaluate stocks on multiple dimensions simultaneously.

The scenario analysis is particularly powerful — modeling fair value across six P/E targets reveals the range of outcomes embedded in current prices. A stock trading at P/E 62 has very different risk characteristics than one at P/E 13, even if both are "good companies." The spreadsheet makes this concrete.

Combined with automatic value/growth classification, disciplined position sizing, and a color-coded comparison matrix, this six-sheet workbook is a complete stock analysis platform that scales from quick screening to deep fundamental research.

Ready to build your valuation dashboard with live data? Visit MarketXLS to access 1,100+ financial formulas directly in Excel, or book a demo to see how live market data transforms your analysis workflow.

Disclaimer

This template and analysis are for educational purposes only. Nothing in this post constitutes financial advice, a recommendation to buy or sell any security, or an endorsement of any investment strategy. All ticker examples are used to demonstrate formula functionality and analytical frameworks, not to suggest investment merit. Past performance does not guarantee future results. Always do your own research and consult a qualified financial advisor before making investment decisions.