How to Calculate Intrinsic Values of Shares in Excel

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 valueThe 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 modelOne 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.

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 atknowledgecenter@fool.com. Thanks -- and Fool on!

The article How to Calculate Intrinsic Values of Shares in Excel originally appeared on Fool.com.

Try any of our Foolish newsletter services free for 30 days. We Fools may not all hold the same opinions, but we all believe that considering a diverse range of insights makes us better investors. The Motley Fool has a disclosure policy.