This is my favourite analysis model currently, but it is a bit tedious to put together; there are a lot of moving parts and it is easily broken.

Just as a bit of background: I've been using this as a proxy for margin of safety, however, it is actually a calculation for the intrinsic value of a security, which is then compared to the actual price. When the stock is trading at a discount, in my mind, a margin of safety is created and the stock becomes a purchase candidate.

This isn't a margin of safety in the way Mr. Graham described it however. You can read a definition here.

I guess part of reason for not using margin of safety is because currently interest rates are so low almost everything has a margin of safety. Or maybe I don't understand it well enough and I'm scared because I don't really get it. Perhaps I should actually attempt the calculation and then maybe some kind soul out there can provide some feedback.

**Ohlson's Clean Surplus Theory**says that the intrinsic value of a stock equals the net book value of the firm's assets plus the expected present value of future abnormal earnings (goodwill). Abnormal earnings is the difference between actual and expected earnings. Expected earnings are calculated by multiplying opening shareholders' equity (I use book value per share) by the firm's return on equity less the dividend payout ratio.

To build the spreadsheet for the calculation, set up a financial analysis template in Verdant Analysis that includes:

- beta (this is a measure, not a line item),
- Shareholder's Equity (B/S),
- Preferred shares, redeemable,
- Nonredeemable Pref. Stock,
- Total Shares Outstanding,
- Dividends per share,
- EPS,
- Preferred Dividends.

Once exported, you can optionally name the front sheet "data" and set up a second sheet called "formula".

On the formula sheet, I carried over the security names and actual price directly. The next 22 columns are:

- Theoretical price (this is the intrinsic value),
- Premium/discount (vis a vis the actual price),
- A column for each of book value at t through t+6,
- Sum of the present value of expected abnormal earnings,
- A column for each of the expected abnormal earnings at t+1 through t+7,
- Firm's Cost of Capital (calculated using CAPM),
- Return on Equity,
- Risk Free Rate,
- Expected Market Premium,
- Dividend Payout Ratio.

**Dividend payout**is dividends per share divided by EPS:

**=round(data!L2/data!M2,2)**

The next category,

**projected market return**, is the only estimate that is required in this formula, which is one reason why I like it. This number drives the firm's cost of capital calculation (using CAPM). I'm using a value of 10%, but you can and should adjust this number to your own insights and predictions. I did another little post on why I choose 10% projected market return, for those who are interested.

The one year risk free rate is .14%.

**Return on Equity**is (Net Income after Tax - Preferred Dividends) divided by (Shareholder Equity - Preferred Shares). The formula looks like this:

**=round((data!G2-data!N2)/(data!H2-data!I2-data!J2),2)**

Next we calculate the firm's individual

**cost of capital**using the capital asset pricing model (CAPM), which employees "beta". Mr. Graham and that fellow from Omaha have raised some very worthy points about the usefulness of beta. One of them said, and I paraphrase here, "beta is a really stupid measure of risk." He used an example of an over-priced low beta stock versus an under-valued high-beta stock.

Those guys are geniuses and not because they know a lot of esoteric stuff (they might, I don't know). For me they're geniuses because they THINK. They look at something and they figure out if it makes sense. They don't just accept something blindly. I think it is also called common sense, in my humble opinion, a most uncommon virtue.

How do we reconcile this then?

Well, mathematically, beta is just the output of a regression that measures how a security moves with the market. So I feel that if we drop the pre-conceived notions about it (like it's the only statistic of interest) and we just take it for what it is, then it can be useful.

Here is another aside: we've had some trouble with our betas (we meaning Verdant

**Analysis). At this nascent stage in our development we don't have a dedicated data feed; we collect publicly available, non-propriety information (like financial statement data, closing price information, interest rates etc.) from a number of places. We collect all the price and market information needed to calculate beta and we run the calculation ourselves (so we know exactly what it is: a 5-year rolling beta).**

However, if one of those places "changes" something, we can break.

Anyway, something broke and some of our betas got off. It's fixed, but not yet deployed. I'm sorry and it's disappointing to not use our own betas in the calculation, but sometimes in life, a lot of the time in fact, you just have to let it go and move on. So, on the data sheet there is another column for beta where I pulled betas from Google Finance and Yahoo Finance.

To calculate the firm's cost of capital using CAPM add the risk free rate to the firm's beta multiplied by the market premium, or:

=V2+data!O2*(W2-V2)

We next calculate the

**BV per share**for time t. It is the common shareholders' equity divided by the common shares outstanding, or:

=round((data!H2-data!I2-data!J2)/data!K2,2)

To calculate

**subsequent book values**we multiply the previous BV (for example, to get BV(t+1) the previous is BV(t)) by a growth factor. The growth factor is the return on equity for the earnings that remain in the company (earnings that are not paid out as dividends). It is 1 + ROE x (1 - dividend payout ratio), or:

=E2*(1+$U2*(1-$X2))

This calculation is repeated until all of the future book value per share are calculated.

Calculating the

**abnormal earnings**involves multiplying the previous year's book value per share by the difference between the return on equity and the dividend payout ratio. In other words, E(a)(t+1) = BV(t) * (ROE - DPO), or:

=E2*(1+$U2*(1-$X2))

Once again, repeat until all abnormal earnings are calculated. Note, there can be negative abnormal earnings is the dividend payout ratio is greater than the return on equity.

Next, sum the present value of the abnormal earnings:

=ROUND(M2/(1+T2)+N2/(1+T2)^2+O2/(1+T2)^3+P2/(1+T2)^4+Q2/(1+T2)^5+R2/(1+T2)^6+S2/(1+T2)^7,2)

and then add this to the book value at time to calculate the

**theoretical price**:

=E2+L2.

The

**premium/discount**calculation is calculated as the premium or discount of the actual price relative to the theoretical price or:

=round((B2-C2)/abs(C2),2).

Note, we take the absolute value of the denominator in case the theoretical price is negative.

We are looking for securities trading at a discount relative to their theoretical price.

In the next post, we'll look at the numbers and have a think about them, because of course, just because it says it doesn't mean it's real. If we ever want to approach the great ones, we have to apply some common sense.

Thank you for reading and please let me know if you have any comments or questions.

The spreadsheet with all the calculations is here.

Jen

## No comments:

## Post a Comment