How to Find the Z Score Using Excel: Answers to the Most Common Questions

How to Find the Z Score Using Excel: Answers to the Most Common Questions

Knowing how to find the z score using Excel matters because it gives you a repeatable, auditable bankruptcy-risk calculator that works on any public company with a 10-K. The Altman Z Score formula has five variables, each built from a ratio you can calculate with balance sheet and income statement data. The final number tells you whether a stock belongs in the safe zone (above 2.99), the grey zone (1.81 to 2.99), or the distress zone (below 1.81). This post answers the questions investors ask most often about building and using the model in Excel.

Key Takeaways

• The Altman Z Score Excel formula is: =1.2*((CA-CL)/TA)+1.4*(RE/TA)+3.3*(EBIT/TA)+0.6*(MCAP/TL)+1.0*(REV/TA), where each abbreviation is a cell or named range holding the corresponding financial input.
• You need seven raw inputs: current assets, current liabilities, total assets, retained earnings, EBIT, market cap, total liabilities, and revenue.
• The formula works for public manufacturing companies. Non-manufacturers should use the Z'' variant, which drops the revenue-to-assets term.
• Negative retained earnings (accumulated deficit) are valid inputs and will reduce X2 below zero, dragging the Z score down correctly.
• For batch analysis, structure companies in rows and inputs in columns, then copy the formula down to run across an entire watchlist.
• Track the Z score quarterly rather than annually to catch deterioration trends before they become crises.

The Exact Excel Formula You Need

Enter this formula in any cell after filling in your inputs:

=1.2((B2-B3)/B4)+1.4(B5/B4)+3.3*(B6/B4)+0.6*(B7/B8)+1.0*(B9/B4)**

With this layout:

The formula cell gives you the Z score. Add an interpretation cell next to it:

=IF(C10>2.99,"Safe Zone",IF(C10>1.81,"Grey Zone","Distress Zone"))

That two-cell setup is all you need for a single-company analysis.

Why the Formula Uses These Specific Weights

Altman derived the weights (1.2, 1.4, 3.3, 0.6, 1.0) using multiple discriminant analysis on a dataset of 33 bankrupt and 33 non-bankrupt U.S. manufacturers in the 1960s. The weights reflect each variable's statistical power in separating the two groups.

The 3.3 weight on X3 (EBIT/Total Assets) is the highest because operating profitability was the strongest single predictor of bankruptcy. Companies with negative EBIT were far more likely to fail, and the high weight ensures this signal dominates the score.

The 0.6 weight on X4 (Market Cap/Total Liabilities) is the lowest among the five because market data is noisy and forward-looking. It still matters; a very low X4 means the market has already priced in distress risk. But it carries less weight than the accounting-based variables to avoid letting short-term sentiment overwhelm the fundamental signal.

How to Handle Common Data Problems

Several situations can create errors or misleading results in your Excel model.

Negative retained earnings: A company with an accumulated deficit (more losses than profits over its history) will show a negative number in B5. This is correct. Leave it as-is; it reduces X2 below zero and appropriately lowers the Z score. Do not replace it with zero.

Negative EBIT: An operating loss produces a negative EBIT. Enter it as a negative number. X3 will be negative, the 3.3 weight amplifies that drag, and the Z score will fall significantly. This is the formula working correctly: companies losing money operationally score lower on Z, as they should.

Very high market cap relative to liabilities: Technology companies with minimal debt and large market caps (like Apple at a P/E of 28.3 and ROIC of 45.1%) will show high X4 values. AAPL's market cap exceeds $3.4 trillion against relatively modest total liabilities, so X4 is elevated. The Z score will sit comfortably above 2.99. That is accurate: the model correctly identifies AAPL as financially safe.

Zero or near-zero total assets: Avoid running the formula on holding companies or financial firms where the balance sheet structure differs fundamentally from manufacturing companies. Division by total assets forms the denominator for X1, X2, X3, and X5; if total assets are artificially small relative to enterprise value (as they can be in asset-light firms), the ratios will look inflated.

Batch Analysis: Running the Z Score Across a Watchlist

For multiple companies, restructure your layout:

Row 1: Headers (Company, CA, CL, TA, RE, EBIT, MCAP, TL, REV, X1, X2, X3, X4, X5, Z Score, Zone)

Rows 2 onwards: One company per row, with all inputs in columns B through I.

Columns J through N hold the five ratio calculations:

• J2: =(B2-C2)/D2 (X1)
• K2: =E2/D2 (X2)
• L2: =F2/D2 (X3)
• M2: =G2/H2 (X4)
• N2: =I2/D2 (X5)

Column O holds the weighted Z score: =1.2J2+1.4K2+3.3L2+0.6M2+1.0*N2

Column P holds the zone: =IF(O2>2.99,"Safe",IF(O2>1.81,"Grey","Distress"))

Copy rows 2 down for each additional company. Sort by column O to rank your watchlist by Z score, lowest to highest.

What the Z Score Reveals That P/E Does Not

The P/E ratio tells you what the market is paying per dollar of earnings. It says nothing about the quality of the balance sheet, the sustainability of the earnings, or the distance from financial distress. A company can have a P/E of 10 and a Z score of 1.3, which means the earnings are cheap because the company is in financial trouble, not because the market mispriced a healthy business.

Johnson & Johnson trades at a P/E near 15.4 with a dividend yield of 3.1%, which looks attractive. But the Z score context matters: JNJ has retained earnings accumulated over decades, strong EBIT-to-assets, and a market cap far exceeding its total liabilities. Its Z score sits well into the safe zone. The low P/E in this case reflects a genuine value opportunity in a financially sound company.

Contrast that with a grey-zone industrial company with a P/E of 9. The Z score reveals negative working capital and an accumulated deficit. The cheap P/E is a symptom of distress, not a discount.

How Often to Recalculate

Annual recalculation (using full-year 10-K data) gives you the cleanest inputs because annual retained earnings and EBIT figures are audited. Quarterly recalculation (using trailing twelve-month data) catches deterioration faster.

The recommended cadence:

• Annual: full recalculation from audited 10-K
• Quarterly: update EBIT, current assets, and current liabilities from the 10-Q; update market cap from the current share price

Note that market cap changes every trading day. If you update market cap weekly or monthly while keeping the accounting inputs from the last filing, you are partly mixing current-market and lagged-accounting data. That is acceptable and standard practice; it actually improves the model's responsiveness because X4 will reflect current market sentiment.

Further reading: Investopedia · CFA Institute

Why altman z score excel Matters

This section anchors the discussion on altman z score excel. The detailed treatment, formula, and worked examples appear in the body of this article above. The points below summarize the most important takeaways for value investors who want to apply altman z score excel in real portfolio decisions. ValueMarkers exposes the underlying data on every covered ticker via the screener and stock profile pages, so the concepts in this article translate directly into actionable filters.

Key inputs for altman z score excel

See the main discussion of altman z score excel in the sections above for the full treatment, including the inputs, the calculation methodology, the typical sector benchmarks, and the most common pitfalls to avoid. The ValueMarkers screener lets value investors filter the full universe of 100,000+ stocks across 73 exchanges using altman z score excel alongside the rest of the 120-indicator composite, with sector percentiles and historical trends shown on every stock profile.

Sector benchmarks for altman z score excel

See the main discussion of altman z score excel in the sections above for the full treatment, including the inputs, the calculation methodology, the typical sector benchmarks, and the most common pitfalls to avoid. The ValueMarkers screener lets value investors filter the full universe of 100,000+ stocks across 73 exchanges using altman z score excel alongside the rest of the 120-indicator composite, with sector percentiles and historical trends shown on every stock profile.

Related ValueMarkers Resources

• Earnings Quality — Glossary entry for Earnings Quality
• ROIC Consistency — ROIC Consistency measures how efficiently a company converts capital into earnings
• Debt To Equity — Glossary entry for Debt To Equity
• Z Score Formula — related ValueMarkers analysis
• Z Score Formula Excel — related ValueMarkers analysis
• Dcf Model — related ValueMarkers analysis

Frequently Asked Questions

what happens if the stock market crashes

A stock market crash compresses market capitalization across the board, which directly reduces the X4 variable (Market Cap / Total Liabilities) in the Altman Z Score. Companies that were in the safe zone with healthy Z scores of 3.5 or higher can fall toward the grey zone during a crash not because their operations deteriorated but because market-based solvency appears weaker. The accounting-based variables (X1, X2, X3, X5) remain anchored to the most recent filing and change more slowly. For long-term value investors, a crash-induced drop in Z scores can signal screening opportunities: companies whose Z scores fell due to market compression rather than operational weakness.

what time does the stock market open

The New York Stock Exchange and Nasdaq both open at 9:30 a.m. Eastern Time on weekdays, excluding U.S. federal holidays. Pre-market trading runs from approximately 4:00 a.m. to 9:30 a.m. Eastern, and after-hours trading continues from 4:00 p.m. to 8:00 p.m. Eastern. For Z score analysis purposes, the opening or closing price can be used to update the market capitalization input (X4); the end-of-day closing price is the most commonly used because it represents the last consensus price before settlement.

what time does the stock market close

U.S. stock exchanges close at 4:00 p.m. Eastern Time on regular trading days. Some stocks continue trading in the after-hours session until 8:00 p.m. Eastern. For updating the market cap cell in your Z score Excel model, use the 4:00 p.m. closing price multiplied by shares outstanding. This gives you the most stable and widely reported market cap figure, which is also what data providers like Bloomberg and FactSet use when they calculate X4 for published Z score data.

when does the stock market open

The stock market opens at 9:30 a.m. Eastern Time Monday through Friday, except on federal holidays including New Year's Day, Martin Luther King Jr. Day, Presidents' Day, Good Friday, Memorial Day, Juneteenth, Independence Day, Labor Day, Thanksgiving, and Christmas. The NYSE also closes early (1:00 p.m. Eastern) on the day after Thanksgiving and the day before Christmas. If you are updating market cap data for your Z score model, use closing prices from full trading days rather than half-day sessions to avoid distortion.

why is the stock market down today

Stock markets fall for many reasons: macroeconomic data surprises (inflation, employment, GDP), central bank policy shifts, earnings misses from large-cap companies, geopolitical events, or broad sector rotations. For Z score purposes, what matters is whether the decline is market-wide (which compresses X4 for all companies simultaneously) or company-specific (which may reflect deteriorating fundamentals deserving a Z score recalculation). A company whose stock drops 20% in a broad market selloff will show a lower X4 but likely unchanged X1, X2, X3, and X5. A company whose stock drops 20% on an earnings miss needs a full Z score update using the new figures.

is coca cola a good stock to buy

Coca-Cola (KO) trades at a P/E near 23.7 with a dividend yield of 3.0% and more than 60 consecutive years of dividend growth as of 2026. Its Z score sits comfortably in the safe zone, supported by strong retained earnings, consistent EBIT-to-assets, and a market cap that far exceeds total liabilities. The question of whether KO is a good buy depends on your required return: at current prices, the implied long-term return from dividends plus earnings growth is approximately 7% to 8% annually, which is competitive for a low-risk, defensive holding. Run KO through the ValueMarkers screener to see its full VMCI Score breakdown and compare it against other consumer staples.

Apply what you learned here by running the Z score formula on any stock in your watchlist. The ValueMarkers screener calculates the Altman Z-Score automatically across 73 global exchanges, so you can cross-reference your Excel output against our data or skip the spreadsheet entirely for faster screening.

Written by Javier Sanz, Founder of ValueMarkers. Last updated April 2026.

Ready to find your next value investment?

ValueMarkers tracks 120+ fundamental indicators across 100,000+ stocks on 73 global exchanges. Run the methodology above in seconds with our stock screener, or see today's top-ranked names on the leaderboard.

Related tools: DCF Calculator · Methodology · Compare ValueMarkers

Disclaimer: This content is for informational and educational purposes only and does not constitute investment advice, a recommendation, or an offer to buy or sell any security. Past performance does not guarantee future results. Consult a licensed financial advisor before making investment decisions.