The book value of shareholders’ equity, also known as the book value of equity or the net worth of a company, is the amount of money that would be left for the common shareholders if the company sold all of its assets and paid off all of its liabilities. It is calculated by subtracting the total liabilities from the total assets on the balance sheet. The book value of equity per share (BVPS) is the book value of equity divided by the number of common shares outstanding. It represents the theoretical value of each share if the company were to liquidate.
To calculate the book value of equity and the BVPS in Excel, we need to follow these steps:
- Enter the values of the common stock, retained earnings, and additional paid-in capital (APIC) in cells A1, A2, and A3, respectively. These are the components of the common equity, which is the portion of the shareholders’ equity that belongs to the common shareholders. The common stock is the par value of the shares issued, the retained earnings are the accumulated profits that have not been distributed as dividends, and the APIC is the excess amount that the shareholders paid over the par value of the shares.
- Enter the formula
=A1+A2+A3in cell A4 to calculate the common equity. This is the same as the book value of equity if the company has no preferred stock or other equity items. - Enter the value of the total number of common shares outstanding in cell A5. This is the number of shares that are issued and not repurchased by the company.
- Enter the formula
=A4/A5in cell A6 to calculate the BVPS. This is the book value of equity divided by the number of common shares outstanding.
For example, suppose a company has the following balance sheet data:
| Item | Value |
|---|---|
| Common stock ($1 par value) | $10 million |
| Retained earnings | $15 million |
| Additional paid-in capital | $5 million |
| Total liabilities | $20 million |
| Total assets | $50 million |
| Common shares outstanding | 12 million |
We can enter these values in Excel as follows:
| A | B |
|---|---|
| Common stock | 10000000 |
| Retained earnings | 15000000 |
| Additional paid-in capital | 5000000 |
| Common equity | =A1+A2+A3 |
| Common shares outstanding | 12000000 |
| BVPS | =A4/A5 |
The resulting values are:
| A | B |
|---|---|
| Common stock | 10000000 |
| Retained earnings | 15000000 |
| Additional paid-in capital | 5000000 |
| Common equity | 30000000 |
| Common shares outstanding | 12000000 |
| BVPS | 2.5 |
This means that the book value of equity is $30 million and the BVPS is $2.5. If the company were to liquidate, each common shareholder would receive $2.5 per share.
Another approach to calculate the book value of equity and the BVPS is to use the formula =Total Assets - Total Liabilities in cell A4 instead of adding the common equity components. This will give the same result as long as the company has no preferred stock or other equity items. However, this approach may not be as accurate or transparent as the first one, since it does not show the breakdown of the common equity.
