Dividend Discount Model With Excel VBA

Click to Download Workbook: Gordon Growth Model VBA

Dividends are a portion of profits or retained earnings that are dispersed amongst the shareholders of a corporation. Why are dividends important? Why do some companies have a sky-high dividend while other companies haven’t paid a dividend since their IPO (Initial Public Offering)? One reason is that the companies that aren’t paying dividends may potentially invest most of their retained earnings back into their business for research and development. These companies may be in their infancy stage, or in a rapidly expanding sector like Technology. Typically speaking, companies that pay dividends are well established, perhaps blue-chip companies with steady performance over time. These rapidly expanding companies typically are called growth companies. Albeit, there is a never-ending discussion on differentiating growth and value stocks, the baseline understanding is when you are buying a growth company, you are anticipating an increase in future earnings growth, where the past was already significant. In essence, buy high, sell higher. With value stocks, you are looking for fundamentally undervalued and mispriced within the marked based on multiple criteria like solid financial statements, decent fundamental ratios, and etc.

With this all being said, companies usually pay out dividends on a quarterly basis. We can download these from Yahoo Finance over a given time frame. Surprisingly historical dividends can still be pulled from the Yahoo API, which is a plus since we will be implementing a specific dividend discount model in VBA.

The model we will be implementing is the Gordon Growth Model. The equation for the model goes as follows. This is a method of vaulting a company based upon the notion that the intrinsic value will be based upon the present value of future dividends.

To elaborate on this formula, essentially, we are taking the last paid dividend and multiplying it by 1 + the compound annual growth rate of the dividends for the company. This will be our D1 or next paid dividend. Next, we have a required rate of return which should be greater than the dividend growth rate, which is g in this case. The required rate of return, in this case, is the theoretical riskiness of the stock. What I mean by this is that the given rate of return on an asset may be 7%, while you could obtain a return of 2% to 3% by putting your money into Treasury Bills or a bank essentially risk-free. The g component will be our computed dividend growth rate. Note that we will use daily regression in order to calculate the compound annual growth rate (CAGR). The reason we are calculating CAGR instead of the arithmetic growth rate is that sometimes historical dividends can jump around quite frequently, and the CAGR method will take into consideration large discrepancies in dividends as well as annual compounding.

In reality, the formula for the intrinsic value of a company will look reflect this equation over some time frame in the future.

It can be condensed to the formula displayed prior.

So to review, all we need to calculate the intrinsic value of a company by the Gordon Growth Model is:

  • Quarterly Historical Dividends
  • A Growth Rate (CAGR)
  • Rate of Return
  • Last Paid Dividend

Let’s start, shall we? First, input a time frame for the dividend data, frequency (choose d, the Yahoo query URL includes this parameter although the dividends are reported quarterly). Next input a ticker symbol and press the form control button.

You will then be prompted by an input box to enter a required rate of return. The model assumes the required rate of return is greater than the growth rate, so keep that in mind. The input box will tell you what the calculated CAGR is also.

The output from the user inputs and VBA calculations is displayed below:

About the author

programmingforfinance

Hi, I'm Frank. I have a passion for coding and extend it primarily within the realm of Finance.

View all posts

4 Comments

  • hello,
    I think your project is really good, i would like to understand how you make your code in the worksheet. However it’s block with a password.
    Can you send me the password ?
    Thanks a lot

    Better regards

  • The model still working? Cause I’ve tried to run and seems reporting a error.

    If you were extracting the data from yahoo finance using the old api, maybe be the cause of problem. Thx

Leave a Reply

Your email address will not be published. Required fields are marked *