Hey guys,

Below is the link to download my **Stock valuation via DDM and CAPM with Beta Calculation** spreadsheet.

In brief, the spreadsheet automatically downloads stock, market, sector and rates data from various websites before performing a number of calculations to produce an estimate of the stocks intrinsic value. The spreadsheet calculates the stocks systematic risk (beta), cost of equity via the Capital Asset Pricing Model (CAPM), and intrinsic value via the Dividend Discount Model (DDM) and minor variations of.

The following instructional video runs through the steps for analysing an example stock, Transurban (TCL) in this instance, and provides a high-level description of the calculations which are carried out.

The second instructional video below runs through the VBA subroutines which perform most of the calculations in the background. This is not necessary viewing for people who simply want to use the spreadsheet without knowing the mechanics of the all calculations.

A more detailed explanation of the mechanics behind the spreadsheet follows…

Firstly, the spreadsheet, at the command of the user, downloads the following data from various websites:

- Stocks previous closing price (among other key statistics) from Yahoo Finance;
- Current Australian Government Bond yields from Bloomberg;
- Current list of all the company’s listed on the ASX;
- Stocks complete historical pricing data from Yahoo Finance;
- Market (e.g. ASX200) complete historical index data from Yahoo Finance; and
- GICS Sector (e.g. Industrials) complete historical index data from Yahoo Finance.

The spreadsheet can be updated on a daily basis simply by repeating the steps outlined in the above instructional videos, however, any new historical pricing or index data will be inserted above existing the data, i.e. the original data set won’t be replaced but added to thus improving accuracy of estimates over time.

After all the necessary data has been downloaded/updated, the spreadsheet can calculate the company’s systematic risk or Beta. This is done using daily, weekly, monthly and yearly Holding Period Returns (HPRs) allowing the user make a choice based on personal preference. In my opinion, the shorter the holding period, the more accurate the results, for example, there are more data points available for daily HPRs compared with yearly HPR.

For each holding period duration, Beta is calculated two ways for additional comparison. The company’s returns are compared with the markets returns (e.g. ASX200 returns) to produce a typically more accurate Beta estimate. Alternatively, the company’s returns are compared with the returns of the relevant GICS Sector Index (e.g. Industrials). I would typically only use Beta estimates produced via Market comparisons but sometimes it might be useful to review estimates produced via Sector comparisons.

Once Beta has been calculated, the cost of equity can be calculated via the Capital Asset Pricing Model (CAPM). The cost of equity is calculated for daily, weekly, monthly and yearly compounding intervals. For example, the weekly cost of equity value represents the rate of return an investor requires to invest in this company for 1 week given the company’s current systematic risk (beta).

The CAPM uses the current Australian government bond yield as the risk-free rate. The particular yield (2, 5, 10 or 15 year maturity) used is based on the user defined investment horizon. For long-term investors this will likely be 15 years, but 2 years might be more suitable for short-term traders. The selected risk-free rate adopted is appropriately scaled according to each holding period duration.

The Dividend Discount Model (DDM) is then applied for both constant growth and zero growth scenarios, where the growth factor is calculated based on the company’s Return on Equity (ROE) and Dividend Payout Ratio.

An alternative method to the DDM is also presented on the first CAPM sheet. This method compounds the user defined, previous years’ annual dividend forward by the growth rate for one to forty years. The present value of each of the expected future dividend payments is then calculated by discounting the cash flow with the appropriate cost of equity given the holding period duration. The intrinsic value is the sum of all discounted future cash flows up to the end of the user’s investment horizon. This is the same concept as the DDM except this method assumes a finite investment horizon (as opposed to the infinite investment horizon assumed by the classic DDM) and might prove more suitable if the growth rate used is greater than the cost of equity calculated.

There is a secondary method presented for calculating the company’s cost of equity and, ultimately, intrinsic value. This method is presented in the CAPM2 sheet and is fundamentally based on how the company’s average **yearly **returns compare with the markets or sectors average **yearly **returns. These average yearly returns are a compound average calculated with daily, weekly and monthly compounding periods. Rather than using yearly holding period returns which consider only one data point per year, and perhaps overlook large spikes and/or dips in stock price, this method considers the following:

- 12 data points for monthly compounding yearly averages;
- approximately 52 data points for weekly compounding; and
- approximately 252 data points for daily compounding;

while still considering the covariance between yearly returns. This might be the user preferred method but in my opinion produces less accurate results than those obtained via first CAPM method.

I hope you find this spreadsheet handy and feel free to leave any comments you might have.

Stay tuned for the next spreadsheet which will calculate the variance of your portfolio with only a few clicks and strokes.

Cheers,

Nick