Stock Valuation Excel (FREE VERSION)

Stock Valuation with Fair Value Calculation (FREE VERSION)

Description
Welcome to this excel sheet!

It puts you in the position to perform a stock valuation according to various aspects (price, fundamental, dividends) within a few minutes and to determine the fair value using different methods (P/E Price/Earnings, DCF Discounted Cash Flow, Estimation, Ten Cap, Payback Time).

The information is compiled from various worksheets that can be easily added or updated via copy & paste or imports. Below I will explain the individual worksheets.

Stock valuation of key figures incl. Fair value in less than 10 minutes:
https://www.youtube.com/watch?v=4JYur2CUy2k

Stock valuation key figures - update quarterly results in less than 3 minutes
https://www.youtube.com/watch?v=yW1LjLSCWfo


"This Excel template ""for free"" has full functionality with the following exceptions:
- Update of aktien_stocks worksheet is not possible and formulas for calculation & DataCleansing are hidden
- Worksheet Erläuterung_Explanation is locked

Full version (= complete functionality and no locks in Excel) is available here: https://www.eloquens.com/tool/Qk1gtGzJ/finance/stock-analysis/stock-valuation

Feedback is very welcome via Private Message here: https://www.eloquens.com/channel/markus-polzer



Stock Valuation Criteria
"The shares are rated according to 3 criteria:
1) Price
2) Fundamental
3) Dividends"

Regarding price rating
a maximum of 20 points can be achieved here - the individual possible points are:
- 2 points: current price < fair value according to P/E method (15% return expectation, 25% margin of safety)
- 2 points: current price < fair value according to DCF method (15% return expectation, 25% margin of safety)
- 2 points: actual P/E < P/E 5 Year Median (price / earnings ratio)"
- 1 point: current price < fair value according to P/E method (15% return expectation, without margin of safety)
- 1 point: current price < fair value according to DCF method (15% return expectation, without margin of safety)
- 1 point: current price < fair value according to analysts estimate for 5 years (15% return expectation)
- 1 point: current price < fair value according to analysts estimate for 5 years (10% return expectation)
- 1 point: current price < MA200 moving average for 200 days """
- 1 point: analyst estimate on"" ""Buy"" ""
- 1 point: Price / Owner Earnings <10
- 1 point: current P/B < P/B 5 year median (price / book value ratio)
- 1 point: current P/S < P/S 5 year median (prics / sales ratio)
- 1 point: current P/C < P/C 5 year median (price / cash flow ratio)
- 1 point: current EV / EBIT < EV / EBIT 5 year median (Enterprise Value / EBIT Ratio)
- 1 point: PEG ratio < 1 (price-earning-growth ratio)
- 1 point: Price To Graham Number <= 2
- 1 point: Price To Peter Lynch Fair Value <= 2"

Regarding fundamental valuation
it can be reached a maximum of 26 points - below the individual possible points:
- 2 points: earnings per share growth 1 year > 10%
- 2 points: earnings per share growth 5 year median > 10%
- 2 points: EV / EBITDA <15 (Enterprise Value to EBITDA)
- 2 points: ROIC return on invested capita l>= 10"
- 1 point: book value per share growth 5 year median > 10%
- 1 point: earnings per share > 0
- 1 point: free cash flow > 0
- 1 point: free cash flow growth 5 year median > 10%
- 1 point: Piotroski F-Score >= 6
- 1 point: sales growth 1 year > 5%
- 1 point: sales growth 5 year median > 5%
- 1 point: net income growth 5 year median >= 10%
- 1 point: operating profit growth 5 year median> = 10%"
- 1 point: share buybacks last year (hence >= 0)
- 1 point: liabilities to equity ratio <= 2
- 1 point: ROIC Return on Invested Capital Growth 5 year median >= 10%
- 1 point: earnings per share growth estimate >= 5%
- 1 point: sales growth estimate for the next 5 years >= 5%
- 1 point: EBIT growth estimate for the next 5 years >= 5%
- 1 point: profit growth estimate for the next 5 years >= 5%
- 1 point: Free Cash Flow growth estimate for the next 5 years >= 5%
- 1 point: earnings per share decline <-50% in the last 10 years"

Regarding dividend valuation
it can be reached a maximum of 3 points - below the individual possible points:
- 1 point: dividend per share growth estimate 5 years >= 10%
- 1 point: payout ratio <= 35%
- 1 point: dividend per share - stability 10 years (no decline >50% in the last 10 years)

This Best Practice includes
1 Excel Sheet for Stock Valuation (FREE VERSION)

RapidTech1898 offers you this Best Practice for free!

download for free

Add to bookmarks

Discuss


4.5 / 5 (29 votes)

please wait...