The market price of a stock doesn’t necessarily reflect its intrinsic value. Several economic theories use different approaches toward valuing companies, but one of the simplest involves calculations that you can easily do on an Excel spreadsheet. Below, we’ll go through the basics of the dividend discount model.
The dividend discount model and intrinsic value
The dividend discount model starts from the fundamental assumption that a stock’s value is determined by what it pays shareholders in dividends, both currently and in the future. Therefore, the model has you come up with estimates of those dividend payments and then calculates their present value.
In order to use the model, you have to make several assumptions. First, you must choose a discount rate to apply to future dividend payments in order to reflect their present value accurately. You then must also make a guess as to how a stock’s dividends will change in the future. Finally, you’ll have to choose whether to assume the company will continue to pay dividends indefinitely or pick a projected date on which payments will stop and the company will wrap up its business.
Using Excel to implement the dividend discount model
One way to use Excel to show how the dividend discount model works is to set up a timeline that reflects the value of each year’s dividends, going however far into the future you choose. The columns will include the year, that year’s dividend, the discount factor, and the present value of the dividend. Each year will get a row.
To let you change your assumptions, have separate boxes that let you input the assumed discount rate and the annual growth rate for the dividend. For the first row, put in the current year, the actual current dividend, and a discount factor of 1. The present value equals the dividend divided by the discount factor, which in the first row will simply equal the current dividend.
For the second row, calculate the year by adding 1 to the previous year. The dividend equals the previous year’s dividend multiplied by 1 plus the assumed annual dividend growth rate. The discount factor equals the previous year’s discount factor multiplied by 1 plus the assumed discount rate. Then divide the year’s dividend by the discount factor.
When you’ve included enough rows to reflect as many years into the future as you like, pick a cell to hold the sum of all the present values of the dividends. That sum equals the intrinsic value of the stock. The picture below shows one example using a 20-year span, a discount rate of 10%, and a flat dividend of $0.50 per share.
The primary downside of the dividend discount model is that it’s very sensitive to the assumptions that you make. Nevertheless, the model provides a good starting point for assessing whether the current market value of a stock is anywhere close to what its intrinsic value might be.
The $15,978 Social Security bonus most retirees completely overlook
If you’re like most Americans, you’re a few years (or more) behind on your retirement savings. But a handful of little-known “Social Security secrets” could help ensure a boost in your retirement income. In fact, one MarketWatch reporter argues that if more Americans knew about this, the government would have to shell out an extra $10 billion annually. For example: one easy, 17-minute trick could pay you as much as $15,978 more… each year! Once you learn how to take advantage of all these loopholes, we think you could retire confidently with the peace of mind we’re all after. Simply click here to discover how you can take advantage of these strategies.
This article is part of The Motley Fool’s Knowledge Center, which was created based on the collected wisdom of a fantastic community of investors. We’d love to hear your questions, thoughts, and opinions on the Knowledge Center in general or this page in particular. Your input will help us help the world invest, better! Email us at email@example.com . Thanks — and Fool on!
– Stock investment