How Can You Calculate Book Value of Equity per Share (BVPS) in Excel?

The book value of equity per share (BVPS) measures a stock's valuation that allows investors to assess the financial health of a company. The BVPS can gauge whether a stock is undervalued or overvalued by using a snapshot of its current common equity and shares outstanding.

The BVPS is calculated by dividing a company's common equity value by its total number of shares outstanding:

For example, assume company ABC's value of common equity is $100 million, and it has shares outstanding of 10 million. Therefore, its BVPS is $10 ($100 million/10 million).

You can calculate a company's BVPS using Microsoft Excel. First, enter the value of a common stock, retained earnings, and additional paid-in capital into cells A1 through A3. Then, in cell A4, enter the formula "=A1 + A2 + A3". This yields the value of common equity.

Then, enter the formula for the BVPS. Enter the total number of shares outstanding into cell A5. Then in cell A6, enter the formula "=A4 / A5".

For example, assume company DEF has common shares of $11 million, retained earnings of $5 million, additional paid-in capital of $2 million and outstanding shares of 1 million. You would enter "=$11000000" into cell A1, "=$5000000" into cell A2, "=$2000000" into cell A3 and "=1000000" into cell A5. In cell A4, enter the corresponding formula for the value of common equity. The resulting BVPS is $18. If company DEF's current stock price is trading below $18, it is currently undervalued.