Few days ago, I have posted the Absolute PE Valuation Model on Financial Statement Analysis Template (v1.30) which built on top on the Financial Statement Analysis Template.
Today, I have created a new worksheet to perform earnings based valuation based on Dividend Discount Model (DDM). You can click here to access the said template on the One Drive.
The "V-DDM" worksheet is a new valuation worksheet that I created based on the existing Financial Statement Analysis Template and all the required data will be auto picked by the V-DDM worksheet after you filled in the purple colour input worksheet as per specified in Financial Statement Analysis Template.
This worksheet will calculate the DDM's intrinsic value of the stock price based on 6 different scenarios.
- No Growth
- Constant Growth Dividend
- Growth on Y1-Y5 and then Constant Growth Dividend
- Growth on Y1-Y5, Y-6-Y10 and then Constant Growth Dividend
- Growth on Y1-Y5 and then Sell the Shares
- Growth on Y1-Y5, Y6-Y10 and then Sell the Shares
You can review the data setup sections in order to confirm all of the inputs. Please refer to Dividend Discount Model (DDM) writeup if you not sure about the data setup. The input data can be override on the grey color cell. (It is similar to Financial Statement Analysis Template.)
The data setup will picked value from supporting data sections to serve as default value. You can review the key financial data and override the default value on grey color cell.
And, last but not least you can alway review to the Calculation Detail section in order to check out the how the excel calculate the intrinsic value.
You may interest to view the previous release on the template:-
- Absolute PE Valuation Model on Financial Statement Analysis Template (v1.30)
- Magic Formula on Financial Statement Analysis Excel Template (v1.20)
- DCF Analysis Excel Template (v1.11) - to include Non Operating Assets
- Discounted Cash Flow (DCF) Analysis Excel Template (v1.10)
- Analysis Template v1.02 and Answer to the Questions
- Financial Statement Analysis Template
References:-
- DDM section on The Best Stock Valuation Method
Intelligent Investor
There are some update on the V-DDM Sheet, please refer to v1.51 http://intelligentinvestor8.blogspot.com/2014/07/update-v-asset-and-v-ddm-on-financial.html
2014-07-05 18:48