Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Logout MarketXLS
Guru functionsValuation Models

Altman’S Z Score In Excel Calculator (Includes Marketxls Template)

Written by admin
Wed Jun 21 2017
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
 

What is Altman’s Z score and what it is used for?

In this article, I will provide you with a quick introduction to Altman Z score for public companies and how to calculate Altman z score in Excel using MarketXLS functions. Altman’s Z score is probably one of the more famous credit scoring models have survived 30 years of application more than that. Sometimes when a company enters bankruptcy it’s kind of thing that people have seen it months or even years coming. But sometimes it’s just a complete surprise. We all know examples of some companies that just imploded and people did not see coming, and investors in people end up losing millions and billions of dollars.

So, what if there was a way to predict the bankruptcy. There was a way and by looking at some financial numbers we could get an idea if there is a likelihood of a company going bankrupt, even if there may be other people were not quite seeing that coming. So that’s really was what Altman’s Z score was developed for, it’s basically a formula that outputs the z score and then that score can be an indicator to suggest if the company is at a high risk of going bankruptcy or not.

This model was created by combining five different financial ratios, calculated by using the accounting data of those companies that had already gone bankrupt in the past. The model itself was developed in 1968 but it is still one of the most widely used models.

Download the Altmans Z Score Calculator in MarketXLS

It is a long formula but it is not very difficult to calculate once you have all the numbers. Altman’s Z score is a linear discriminant model, that means it divides potential borrowers into two classes, either high risk or low risk. This score does not directly produce a probability of going bankrupt however you can take this score map it to a credit rating and translate that credit rating to a probable default. This score is essentially a credit classification model. The idea is, that we take a look at some fundamental variables and essentially plug these into a linear function to produce the Altman Z score in Excel.

How is the score Calculated?

Following equation is used to calculate the score.

1)

Working Capital is Current Assets minus the current liabilities. BBRY is at cell B2 and Year is at cell C2.

Using MarketXLS functions you can calculate this factor as highlighted below in Yellow.

2)

Part of that theory here is that the younger companies would have less of the tenure to accumulate retained earnings so there a little bit more likely to default compared to the older companies.

3)

Altman’s Z score in Excel

4)

Altman’s Z score in ExcelThis factor major has much in common with the structural approach to the credit risk. As the market value of equity reduces company has less of a cushion to pay back its liabilities so its much more likely to default.

5)

Altman Z score in Excel

How do we now interpret the result?

If the score is higher than 2.99 it means that the possibility of company’s bankruptcy is very low. This interval is known as safe zone. The value between 1.8 and 2.99 represent a Grey zone, suggesting that company is facing some kind of financial stress.

If the value is below 1.8, the possibility of the company’s bankruptcy is high. This group is known as the distress zone.In our example for the period ending 2016 BBRY was under some kind of financial stress. Once I had this model I could simply replicate this for other companies. So, I compared the BBRY with GOOGL for Altsman Z score for the period of 2011 to 2016 and plot the scores.

Altman’s Z score in Excel

Please however note, that this model does not calculate the exact probability of company’s bankruptcy. It is more of a statistics-based model. So, you cannot rely on it alone. But looked along with credit rating of the company it could provide good indication of financial health of a company.

Google is consistently getting higher and higher and that of BBRY is going the other way down. I based the model on Yearly numbers, however with MarketXLS you can calculate the same by  quarter. All you have to do is add one more argument in functions for quarter. More details on how to use MarketXLS hf functions, please see our knowledge-base here.

With the Altman Z score in Excel model in place, I went on to find a company that filed for Bankruptcy in recent time so I could check if this score can validate itself if we put into the model the numbers from that company.

So, i found the article below…https://seekingalpha.com/article/4058519-ocean-rig-files-bankruptcy about Ocean Rig with the symbol of ORIG.

So, I ran the same model with ORIG and found the results below. The results seem to indicate that the Altman’s Z score is a valid indicator of possible bankruptcy of a company.

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates
Stock screens
Public saved screens

Top 100 Gainers Today

Top 100 losers Today

Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Implement “your own” investment strategies in Excel with thousands of MarketXLS functions and starter sheets.
Get started today
Search for a stock

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

Top MarketXLS Rank stocks

Oppenheimer Holdings Inc. Class A (DE) logo

Oppenheimer Holdings Inc. Class A (DE)

41.03
 
USD
 
0.28
 
(0.69%)
79
Rank
Optionable: Yes
Market Cap: 417 M
Industry: Capital Markets
52 week range    
28.41   
   52.20
Dick's Sporting Goods Inc logo

Dick's Sporting Goods Inc

121.69
 
USD
 
2.71
 
(2.28%)
79
Rank
Optionable: Yes
Market Cap: 8,484 M
Industry: Specialty Retail
52 week range    
63.45   
   130.67
Buckle Inc. (The) logo

Buckle Inc. (The)

44.04
 
USD
 
0.87
 
(2.02%)
79
Rank
Optionable: Yes
Market Cap: 2,025 M
Industry: Apparel Stores
52 week range    
26.50   
   51.55
Best Buy Co. Inc. logo

Best Buy Co. Inc.

81.23
 
USD
 
-1.16
 
(-1.41%)
79
Rank
Optionable: Yes
Market Cap: 17,020 M
Industry: Specialty Retail
52 week range    
60.79   
   116.00
Group 1 Automotive Inc. logo

Group 1 Automotive Inc.

190.65
 
USD
 
-0.17
 
(-0.09%)
78
Rank
Optionable: Yes
Market Cap: 2,777 M
Industry: Auto & Truck Dealerships
52 week range    
136.16   
   212.23
Big 5 Sporting Goods Corporation logo

Big 5 Sporting Goods Corporation

12.84
 
USD
 
0.17
 
(1.34%)
78
Rank
Optionable: Yes
Market Cap: 301 M
Industry: Specialty Retail
52 week range    
10.23   
   26.39
Community Trust Bancorp Inc. logo

Community Trust Bancorp Inc.

47.78
 
USD
 
0.58
 
(1.23%)
77
Rank
Optionable: Yes
Market Cap: 836 M
Industry: Banks - Regional - US
52 week range    
39.10   
   47.91
Vail Resorts Inc. logo

Vail Resorts Inc.

265.72
 
USD
 
9.84
 
(3.85%)
76
Rank
Optionable: Yes
Market Cap: 9,758 M
Industry: Resorts & Casinos
52 week range    
201.91   
   348.42
Hibbett Sports Inc. logo

Hibbett Sports Inc.

68.19
 
USD
 
-1.51
 
(-2.17%)
76
Rank
Optionable: Yes
Market Cap: 831 M
Industry: Specialty Retail
52 week range    
39.58   
   90.50
Guess? Inc. logo

Guess? Inc.

20.18
 
USD
 
0.20
 
(1.00%)
76
Rank
Optionable: Yes
Market Cap: 1,057 M
Industry: Apparel Stores
52 week range    
14.27   
   25.27

More Features