## What Is the Altman Z Score?

Using the Altman **Z score**, you can predict if a company will be bankrupt within two years. Developed by Edward Altman, it measures the likelihood of a company going bankrupt. By combining five financial ratios, it measures the probability.

## Significance of Altman Z Score

- The Altman Z score of <1.8 indicates financial distress and a high risk of bankruptcy for the company.
- The company’s Altman Z score is 1.8 to 3, which indicates it is in the gray area and will likely file for bankruptcy.
- The company’s Altman Z score is greater than 3, which indicates it is in the safe area and will unlikely file for bankruptcy.

## How to Calculate the Altman Z Score in Excel: 3 Suitable Examples

### Example 1 – Altman Z Score for a Public Manufacturing Company

We are going to use the following dataset where values of each particular are given for a public manufacturing company. We will use** the IF function** to calculate the zone condition of the Altman Z score.

The Altman Z score formula for a public manufacturing company is shown below:

**Z = 1.2 X1+1.4 X2+3.3 X3+0.6 X4+1.0 X5**

** Steps:**

- To calculate the Altman Z score, we have to find out some coefficients.
- To calculate the coefficient
**X1**, we will use the following formula in cell**F5**.

`=(C5-C6)/C14`

Cell **C5** is the total current assets and cell** C6** is the total current liabilities. We calculate the working capital by subtracting total current assets from total current liabilities. By dividing the working capital by total assets, we will get the coefficient **X1**.

- Press
**Enter**.

- To calculate the coefficient
**X2**, we will use the following formula in cell**F6**.

`=C10/C14`

We obtain coefficient **X2** by dividing the retained earnings by total assets.

- To calculate the coefficient
**X3**, we will use the following formula in cell**F7**.

`=C9/C14`

We obtain coefficient **X3** by dividing the operating income by total assets.

- To calculate the coefficient
**X4**, we will use the following formula in cell**F8**.

`=C11/C14`

We obtain coefficient **X4** by dividing the market value of equity by total assets.

- To calculate the coefficient
**X5**, we will use the following formula in cell**F9**.

`=C12/C14`

We obtain coefficient **X5** by dividing the sales by total assets.

- Enter the constant values in the range of cell
**G5:G9**from the Altman Z score formula as shown below.

- To calculate the Altman Z Score, use the following formula in cell
**I5**.

`=G5*F5+G6*F6+G7*F7+G8*F8+G9*F9`

- The Altman Z score value for this company is
**2.797429**. - To calculate the zone condition, use the following formula in cell
**J5**.

`=IF(I5>3,"Safe",(IF(I5<1.8, "Distress", "Grey")))`

** **

We obtain a Z-score of 2.797429. It indicates that the company is not in a safe condition and will likely file for bankruptcy.

** How Does the Formula Work? **

- The
**IF(I5<1.8, “Distress”, “Grey”)**function determines whether the condition cell**I5**(means Z score value) is met, and will return**“Distress”**if it is true and return**“Grey”**if it is false. - The
**IF(I5>3,”Safe”,(IF(I5<1.8, “Distress”, “Grey”)))**formula determines whether the condition cell**I5**(means Z score value) is met, and will return**“Safe”**if it is true and return**“Distress”**or**“Grey”**if it is false.

### Example 2 – Altman Z Score for a Private Manufacturing Company

We are going to use the following dataset where values of each particular are given for a private manufacturing company.

The Altman Z score formula for a private manufacturing company is shown below:

**Z = 0.717X1+0.847X2+3.107X3+0.42X4A+ 0.998 X5**

**Steps:**

- To calculate the Altman Z score, we have to determine the coeficients.
- To calculate the coefficient
**X1**, we will use the following formula in cell**F5**.

`=(C5-C6)/C15`

Cell **C5** is the total current assets and cell** C6** is the total current liabilities. We calculate the working capital by subtracting total current assets from total current liabilities. By dividing the working capital with total assets, we will get the coefficient **X1**.

** **

- To calculate the coefficient
**X2**, we will use the following formula in cell**F6**.

`=C10/C15`

We obtain coefficient **X2** by dividing the retained earnings by total assets.

- To calculate the coefficient
**X3**, we will use the following formula in cell**F7**.

`=C9/C15`

We obtain coefficient **X3** by dividing the operating income by total assets.

- To calculate the coefficient
**X4A**, we will use the following formula in cell**F8**.

`=C13/C14`

We obtain coefficient **X4A** by dividing the net worth by total liabilities.

- To calculate the coefficient
**X5**, we will use the following formula in cell**F9**.

`=C12/C15`

We obtain coefficient **X5** by dividing sales by total assets.

- Enter the constant value in the range of cell
**G5:G9**from the Altman Z score formula as shown below.

- To calculate the Altman Z Score, we will use the following formula in cell
**I5**.

`=G5*F5+G6*F6+G7*F7+G8*F8+G9*F9`

- We will get the Altman Z score value for this company to be
**1.80**. - To calculate the zone condition, we will use the following formula in cell
**J5**.

`=IF(I5>3,"Safe",(IF(I5<1.8, "Distress", "Grey")))`

- From the above calculation, we obtain a Z-score for this public manufacturing company is 1.80. It indicates that the company is not in a safe condition and will likely file for bankruptcy.

** How Does the Formula Work? **

- Here, the
**IF(I5<1.8, “Distress”, “Grey”)**function determines whether the condition cell**I5**(means Z score value) is met, and will return**“Distress”**if it is true and return**“Grey”**if it is false. - The
**IF(I5>3,”Safe”,(IF(I5<1.8, “Distress”, “Grey”)))**formula determines whether the condition cell**I5**(means Z score value) is met, and will return**“Safe”**if it is true and return**“Distress”**or**“Grey”**if it is false.

### Example 3 – Altman Z Score for a General Firm

We will demonstrate another model which is Model B.

The Altman Z score formula for a general firm is shown below:

**Z = 6.56X1+3.26X2+6.72X3+1.05X4A**

**Steps:**

- To calculate the coefficient
**X1**, we will use the following formula in cell**F5**.

`=(C5-C6)/C15`

Cell **C5** is the total current assets and cell** C6** is the total current liabilities. We calculate the working capital by subtracting total current assets from total current liabilities. By dividing the working capital by total assets, we will get the coefficient **X1**.

- To calculate the coefficient
**X2**, we will use the following formula in cell**F6**.

`=C10/C15`

We obtain coefficient **X2** by dividing retained earnings by total assets.

- To calculate the coefficient
**X3**, we will use the following formula in cell**F7**.

`=C9/C15`

We obtain coefficient **X3** by dividing the operating income by total assets.

- To calculate the coefficient
**X4A**, we will use the following formula in cell**F8**.

`=C13/C14`

We obtain coefficient **X4A** by dividing the net worth by total liabilities.

- Enter the constant values in the range of cell
**G5:G8**from the Altman Z score formula as shown below.

- To calculate the Altman Z Score, use the following formula in cell
**I5**.

`=G5*F5+G6*F6+G7*F7+G8*F8+G9*F9`

- We will get the Altman Z score value for this company as
**3.006248**. - To calculate the zone condition, we will use the following formula in cell
**J5**.

`=IF(I5>3,"Safe",(IF(I5<1.8, "Distress", "Grey")))`

- The function indicates that the company is in a safe condition and will unlikely file for bankruptcy.

** How Does the Formula Work? **

- The
**IF(I5<1.8, “Distress”, “Grey”)**function determines whether the condition cell**I5**(means Z score value) is met, and will return**“Distress”**if it is true and return**“Grey”**if it is false. - The
**IF(I5>3,”Safe”,(IF(I5<1.8, “Distress”, “Grey”)))**formula determines whether the condition cell**I5**(means Z score value) is met, and will return**“Safe”**if it is true and return**“Distress”**or**“Grey”**if it is false.

**Read More:** How to Calculate Z Score in Excel

**Download the Practice Workbook**

## Related Articles

- How to Calculate Critical Z Score in Excel
- How to Calculate P Value from Z Score in Excel
- How to Calculate Z-Score with 95 Confidence Interval in Excel
- How to Calculate Probability from Z-Score in Excel

**<< Go Back to Z Score in Excel | Excel for Statistics | Learn Excel**