:: Subscribe to our newsletter
 
Articles / Directory
06/17/2013
Latest version of TraderCode(v5.6) includes new Technical Analysis indicators, Point-and-Figure Charting and Strategy Backtesting.



06/17/2013
InvestmentCode, a comprehensive suite of
Financial calculators and models for Excel is now available.



09/01/2009
Launch of
Free Investment and Financial Calculator for Excel.



09/08/2007
Launch of
TinyGraphs - open source add-in for creating sparklines and tiny charts in Excel.



1/02/2009
Launch of NeuralCode(v1.0) for
Neural Networks Trading.



Free Stock/Equity Valuation Spreadsheet Calculator

Equity Valuation using the Dividend Discount Model

The Dividend Discount Model (DDM) is a method used for valuing the price of a stock for a company which pays out dividends. The model assumes that the price of a stock is equivalent to the sum of all of its future dividend payments discounted to the present value. The model is simple in theory but have various scenarios due to the different ways that dividends could be paid out. This spreadsheet allows you to value a stock using the Dividend Discount Model in the following scenarios:
  • Price of Stock with Zero Growth Dividends
  • Price of Stock with Constant Growth Dividends (Gordon Model)

Price of Stock with Zero Growth Dividend

Consider the case where a company pays out all its earnings as dividends. In this case, no earnings are retained to further grow the company. The stockholders can expect that future earnings will be flat and there will not be any further increase in the dividends payout. If the above assumptions are made for a company, it will be very easy to value the price of the stock for this company. Using the Dividend Discount Model, we simply need to sum all the company's future dividends which is fixed at a constant amount. This is equivalent to the calculation of a perpetuity using the Time Value of Money concepts.



The PriceofStock-ZeroGrowth worksheet calculates the Price of Stock using the perpetuity formula as below:

Price of Stock Today = Dividend of Stock at Time period 1 / Required Return/Discount Rate



The PriceofStock-ZeroGrowth-SA worksheet also values the price of stock using the Dividend Discount Model and uses Excel Data Tables to perform Sensitivity Analysis on the price of stock by varying the Required Return.



Price of Stock with Constant Growth Dividends (Gordon Model)

Consider the case where a company pays out part of its earnings as dividends and retains part of the earnings for future growth. If the company stays healthy, grows at a constant rate and is able to pay out dividends that grow at a constant rate, how do we value the price of the stock of this company?

Using the Dividend Discount Model to value the price of the stock, we sum all the company's future dividends, which in this case is assuming to grow at a constant rate. This model is also known as Gordon Growth Model, named after it's author Myron Gordon.

The PriceofStock-ConstantGrowth worksheet calculates and values the price of the stock using the following formula:

Price of Stock Today = Dividend of Stock at Time period 1 / (Required Return-Growth Rate)



Download Free Equity Valuation spreadsheet - v1.0

System Requirements

Microsoft® Windows XP®, Microsoft® Windows Vista®, Windows 7 or Windows 8
Windows Server 2003, 2008, 2012
512 MB RAM
5 MB of Hard Disk space
Excel 2002, 2003, XP, 2007, 2010, 2013

License

By downloading this software from our web site, you agree to the terms of our license agreement.

Download

FreeStockEquityValuation.zip (Zip Format - 93 KB)

Get the Professional version

Benefits
  • Unlocked
  • Allows removal of copyright message in the template
  • Allows commercial use within the company
  • Supports Price of Stock with Zero Growth Dividends
  • Supports Price of Stock with Constant Growth Dividends
  • Supports Price of Stock at Time N with Contant Growth Dividends (Terminal Value)
  • Supports Price of Stock with Two Stage Growth Dividends
  • Supports Price of Stock with Non Constant Growth Dividends
  • Full source code
Limited Time Bonus
  • Free Visual Basic for Applications Training worth USD$30 (Over 100 pages!)
Details PDF Specifications

Price

USD30.00 - Purchase








 

Copyright (c) 2007-2014, ConnectCode Pte Ltd. All Rights Reserved.


Trademark Acknowledgements

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. Avery is the trademark of Avery Dennison Corporation. All other product names are trademarks, registered trademarks, or service marks of their respective owners