# How to Calculate Intrinsic Values of Shares in Excel

By

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.