P-SPEC
11-15-2005, 11:49 AM
alright this is all off the top of my head so bear with me....the example I'm going to use is Exxon Mobil.
There are 3 different types of valuations: zero growth rate dividends, constant growth dividends and supernormal growth. Constant growth is used for mature companies whose growth has stablized while supernormal growth is used for fast growing companies like Google. Since Exxon Mobil is in the Maturity stage of the business cycle, I'm gonna use the constant growth model. The formula is very simple and is as follows:
P0 = D1/(r-g) [P0=Price now, D1= dividend, r = required rate of return g = growth]
The first step is to find the dividend, from yahoo finance we find Exxon Mobil (XOM) to have a dividend of 1.16. Looking at the increase in dividends over the determined terminal value, almost always between 5-7 years, we see that the constant growth rate for XOM is 9%
Next we need to find our expected rate of return. By going to the yahoo finance historical prices page, we can obtain a daily closing listing for the past year on XOM http://finance.yahoo.com/q/hp?s=XOM&a=00&b=1&c=2004&d=11&e=31&f=2004&g=d
downloading these into a spreadsheet and then sorting them from jan.-dec. we can begin our valuation. by subtracting the previous day from the next days closing and dividing by the previous day you will get the actual return for each day. You can also do the same for an index such as the S&P 500 in order to determine market risk. by using simple excel functions you can find the standard deviation and variance of both XOM and the S&P. Next using the Covariance function, you can find the correlation between XOM and S&P.
var xom -.0000956
var sp -.0000488
covariance(xom,s&p) - .0000380
To find Beta (which is the risk measure of the stock against the market S&P 500 has a beta of 1.0, anything lower than 1 is less risky than the market, higher that 1 is more risky than the market itself) you simply divide the covariance of the stock and index by the variance of the index: .0000380/.0000956 = 0.78. The Beta of XOM is 0.78, meaning that it is less volatile then the market itself.
Now back to the valuation. We have our dividend and we want to find our required rate of return. To find the expected rate simply average the daily closing returns over the specified time period of the stock you are valuing. You can do this using the Average function in excel and selecting the column of closing returns. This will give you an average of 0.11 or 11%. This is your required rate of return. Now all thats left is to plug our info in:
D1 = 1.16
r = 11%
g = 9%
Again the formula is:
P0 = D1/(r-g)
P0 = 1.16/(.11-.09)
P0 = $58.00
This formula values Exxon Mobil currently at $58/share.....the actual current share price is around $57 http://finance.yahoo.com/q?s=XOM .....are they undervalued?...not really. all things equal, the prices will converge on the true value and you might make a few basis points, but nothing to get excited over. However this is how Value fund managers evaluate and analyze potential companies for thier portfolio and when researching a company can give you a good idea of what you can expect to return. However if there is mispricing, do not consider it arbitrage, because we all know the stock market doesnt always have to make sense.
There are 3 different types of valuations: zero growth rate dividends, constant growth dividends and supernormal growth. Constant growth is used for mature companies whose growth has stablized while supernormal growth is used for fast growing companies like Google. Since Exxon Mobil is in the Maturity stage of the business cycle, I'm gonna use the constant growth model. The formula is very simple and is as follows:
P0 = D1/(r-g) [P0=Price now, D1= dividend, r = required rate of return g = growth]
The first step is to find the dividend, from yahoo finance we find Exxon Mobil (XOM) to have a dividend of 1.16. Looking at the increase in dividends over the determined terminal value, almost always between 5-7 years, we see that the constant growth rate for XOM is 9%
Next we need to find our expected rate of return. By going to the yahoo finance historical prices page, we can obtain a daily closing listing for the past year on XOM http://finance.yahoo.com/q/hp?s=XOM&a=00&b=1&c=2004&d=11&e=31&f=2004&g=d
downloading these into a spreadsheet and then sorting them from jan.-dec. we can begin our valuation. by subtracting the previous day from the next days closing and dividing by the previous day you will get the actual return for each day. You can also do the same for an index such as the S&P 500 in order to determine market risk. by using simple excel functions you can find the standard deviation and variance of both XOM and the S&P. Next using the Covariance function, you can find the correlation between XOM and S&P.
var xom -.0000956
var sp -.0000488
covariance(xom,s&p) - .0000380
To find Beta (which is the risk measure of the stock against the market S&P 500 has a beta of 1.0, anything lower than 1 is less risky than the market, higher that 1 is more risky than the market itself) you simply divide the covariance of the stock and index by the variance of the index: .0000380/.0000956 = 0.78. The Beta of XOM is 0.78, meaning that it is less volatile then the market itself.
Now back to the valuation. We have our dividend and we want to find our required rate of return. To find the expected rate simply average the daily closing returns over the specified time period of the stock you are valuing. You can do this using the Average function in excel and selecting the column of closing returns. This will give you an average of 0.11 or 11%. This is your required rate of return. Now all thats left is to plug our info in:
D1 = 1.16
r = 11%
g = 9%
Again the formula is:
P0 = D1/(r-g)
P0 = 1.16/(.11-.09)
P0 = $58.00
This formula values Exxon Mobil currently at $58/share.....the actual current share price is around $57 http://finance.yahoo.com/q?s=XOM .....are they undervalued?...not really. all things equal, the prices will converge on the true value and you might make a few basis points, but nothing to get excited over. However this is how Value fund managers evaluate and analyze potential companies for thier portfolio and when researching a company can give you a good idea of what you can expect to return. However if there is mispricing, do not consider it arbitrage, because we all know the stock market doesnt always have to make sense.