How to Calculate Intrinsic Values of Shares in Excel

by Ryan Menezes

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.

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).

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.

Type "0" in cell A1. This represents the current year.

Type "=A1+1" into cell A2. Click the cell's lower-right corner and drag it downward to extend it.

Type the value of the shares' current annual dividends into cell B1. For example, if the shares produce dividends of $20, then type "20".

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.

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.

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.

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.

About the Author

Ryan Menezes is a professional writer and blogger. He has a Bachelor of Science in journalism from Boston University and has written for the American Civil Liberties Union, the marketing firm InSegment and the project management service Assembla. He is also a member of Mensa and the American Parliamentary Debate Association.

Photo Credits

  • Creatas/Creatas/Getty Images