Hi,
Thought I would throw up some recent spreadsheets I made in the process of my normal researching. Usually before I turn an idea into a proper program I try to make a spreadsheet first to understand it better.
Now I am posting this stuff just in the spirit of sharing, none of the below should be considered endorsement of a particular trading strategy, nor am I suggesting the below be used in any serious fashion without a lot of refinement and work. But perhaps you will spot a mistake I made, or have an interesting observation or find it useful in inspiring your own ideas.
They are .ODS (Open Document Spreadsheet) format so should be compatible with Excel/OpenOffice/LibreOffice/Google Spreadsheet.
Fill in the orange sections to see the results in bold.
View attachment files.zip
1. Return Forecast
Given:
* Investment duration (years, should never be less than 10)
* Forecast business growth (EPS)
* Forecast annual dividend yield
* Long term dividend yield average
* Current valuation (can be whatever you like, e.g. Price/Sales, CAPE, Market Cap/GDP, etc)
* Long term valuation average (average value of entire valuation dataset)
* Current risk free rate (this can be your discount rate, or it can be the current risk AAA rate or whatever you like)
* Forecast volatility (I use std dev of last 63 days daily return, but you can use Implied Volatility or GARCH/ARIMA/whatever inputs you like).
Outputs:
* Forecast annualised return for the investment duration based on growth, valuation mean reversion (valuation and dividend models) and dividend yield.
* A Sharpe ratio exposure metric based on forecast annualised return, assumed risk free rate and forecast volatility. The idea here is to scale your exposure by the Sharpe ratio on a daily/weekly/monthly/quarterly/yearly basis.
This concept is based on my understanding of some work from John Hussman.
Here is an example of the S&P ASX 200 index:
2. Intrinsic Value calculator
I am not sure where I got the idea for this one but I was reading something which described what is essentially a crude intrinsic valuation calculator and this was my attempt to implement.
Given:
* Current EPS
* Current DPS
* Your chosen discount rate
* Forecast EPS growth for each year (by default the spreadsheet assumes homogenous EPS growth)
* Forecast DPS growth for each year (by default the spreadsheet assumes homogenous DPS growth)
Outputs:
* "Intrinsic Value" price of the security today if the market values the security at a P/E of 10 in 10 years.
* "Intrinsic Value" price of the security today if the market values the security at a P/E of 15 in 10 years.
* "Intrinsic Value" price of the security today if the market values the security at a P/E of 20 in 10 years.
Example:
3. FS_SCORE calculator
In the book "Quantitative Value" an improved version of the Piotroski score is presented. Now I am an owner of this book and I really like the methodology presented within. Full disclosure, I own some of the NYSE listed ETF which uses this methodology (QVAL). Now if you don't own the book, don't fret, since every part of the book is available as free blog posts (see example below).
Piotroski score: http://www.investopedia.com/terms/p/piotroski-score.asp
FS_SCORE: http://blog.alphaarchitect.com/2012...y-high-quality-stocks-continued-part-3b-of-4/
Given:
* Net income (current and prev year)
* Total assets (current and prev year)
* CapEx (current and prev year)
* Cashflow from Operations (current and prev year)
* Long term debt (current and prev year)
* Current assets (current and prev year)
* Current liabilities (current and prev year)
* Equity purchase $ amount
* Equity issuance $ amount
* Cost of goods sold (current and prev year)
* Revenue (current and prev year)
Outputs:
* Free cash flow (current and prev year)
* Accruals
* Gross margin (current and prev year)
* Return on Assets (current and prev year)
* FCF on Assets (current and prev year)
* Leverage (current and prev year)
* Liquidity (current and prev year)
* Margin (current and prev year)
* Turnover (current and prev year)
* FS_SCORE (rates the company out of 10 on 3 areas)
Example:
Thought I would throw up some recent spreadsheets I made in the process of my normal researching. Usually before I turn an idea into a proper program I try to make a spreadsheet first to understand it better.
Now I am posting this stuff just in the spirit of sharing, none of the below should be considered endorsement of a particular trading strategy, nor am I suggesting the below be used in any serious fashion without a lot of refinement and work. But perhaps you will spot a mistake I made, or have an interesting observation or find it useful in inspiring your own ideas.
They are .ODS (Open Document Spreadsheet) format so should be compatible with Excel/OpenOffice/LibreOffice/Google Spreadsheet.
Fill in the orange sections to see the results in bold.
View attachment files.zip
1. Return Forecast
Given:
* Investment duration (years, should never be less than 10)
* Forecast business growth (EPS)
* Forecast annual dividend yield
* Long term dividend yield average
* Current valuation (can be whatever you like, e.g. Price/Sales, CAPE, Market Cap/GDP, etc)
* Long term valuation average (average value of entire valuation dataset)
* Current risk free rate (this can be your discount rate, or it can be the current risk AAA rate or whatever you like)
* Forecast volatility (I use std dev of last 63 days daily return, but you can use Implied Volatility or GARCH/ARIMA/whatever inputs you like).
Outputs:
* Forecast annualised return for the investment duration based on growth, valuation mean reversion (valuation and dividend models) and dividend yield.
* A Sharpe ratio exposure metric based on forecast annualised return, assumed risk free rate and forecast volatility. The idea here is to scale your exposure by the Sharpe ratio on a daily/weekly/monthly/quarterly/yearly basis.
This concept is based on my understanding of some work from John Hussman.
Here is an example of the S&P ASX 200 index:
2. Intrinsic Value calculator
I am not sure where I got the idea for this one but I was reading something which described what is essentially a crude intrinsic valuation calculator and this was my attempt to implement.
Given:
* Current EPS
* Current DPS
* Your chosen discount rate
* Forecast EPS growth for each year (by default the spreadsheet assumes homogenous EPS growth)
* Forecast DPS growth for each year (by default the spreadsheet assumes homogenous DPS growth)
Outputs:
* "Intrinsic Value" price of the security today if the market values the security at a P/E of 10 in 10 years.
* "Intrinsic Value" price of the security today if the market values the security at a P/E of 15 in 10 years.
* "Intrinsic Value" price of the security today if the market values the security at a P/E of 20 in 10 years.
Example:
3. FS_SCORE calculator
In the book "Quantitative Value" an improved version of the Piotroski score is presented. Now I am an owner of this book and I really like the methodology presented within. Full disclosure, I own some of the NYSE listed ETF which uses this methodology (QVAL). Now if you don't own the book, don't fret, since every part of the book is available as free blog posts (see example below).
Piotroski score: http://www.investopedia.com/terms/p/piotroski-score.asp
FS_SCORE: http://blog.alphaarchitect.com/2012...y-high-quality-stocks-continued-part-3b-of-4/
Given:
* Net income (current and prev year)
* Total assets (current and prev year)
* CapEx (current and prev year)
* Cashflow from Operations (current and prev year)
* Long term debt (current and prev year)
* Current assets (current and prev year)
* Current liabilities (current and prev year)
* Equity purchase $ amount
* Equity issuance $ amount
* Cost of goods sold (current and prev year)
* Revenue (current and prev year)
Outputs:
* Free cash flow (current and prev year)
* Accruals
* Gross margin (current and prev year)
* Return on Assets (current and prev year)
* FCF on Assets (current and prev year)
* Leverage (current and prev year)
* Liquidity (current and prev year)
* Margin (current and prev year)
* Turnover (current and prev year)
* FS_SCORE (rates the company out of 10 on 3 areas)
Example:
