The market price of a share of a company's stock indicates how much investors are willing to pay for it. The share's intrinsic price, however, describes the sum of all future cash flows from the stock. In an ideal market, the two prices will nearly equal each other. But investors' risk tolerance can convince them to pay more or less for shares. You can calculate the value of shares in Microsoft Excel to determine whether you have overvalued or undervalued a stock.
1. Type the dividends' growth rate into cell E1. For example, if the shares' dividends grow at the rate of 7 percent annually, then enter "0.07" (without quotes here and in all the following steps).
2. Type the discount rate, which is the opportunity cost of investment, into cell F1. For example, if you estimate this rate at 13 percent, type "0.13" into cell F1.
3. Type "0" in cell A1. This represents the current year.
4. Type "=A1+1" into cell A2. Click the cell's lower-right corner and drag it downward to extend it.
5. Type the value of the shares' current annual dividends into cell B1. For example, if the shares produce dividends of $20, then type "20".
6. Type "=$B$1*(1+$E$1)^A2" into cell B2. Click the cell's lower-right corner and drag it downward to extend it. The column now displays the share's future dividends.
7. Type "=(1+$F$1)^A1" into cell C1. Click the cell's lower-right corner and drag it downward to extend it. The column now displays each year's discount rate.
8. Type "=B1/C1" into cell D1. Click the cell's lower-right corner and drag it downward to extend it. The column now displays each year's discounted cash flow. This value decreases each year and eventually reaches zero.
9. Type "=SUM(D1:D100)" into cell G1. Change "D100" to the address of a cell from column D that contains the value 0. Cell G1 calculates the shares' intrinsic value. With this example, it shows a value of $354.31.
- Creatas/Creatas/Getty Images