Free spreadsheets - Aussie Stock Forums

Results 1 to 7 of 7
  1. #1

    Default Free spreadsheets


    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.


    1. Return Forecast

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

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

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

    * "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.


    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/...-part-3b-of-4/

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

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

    Disclosure: Long cash, gold, stocks.

  2. #2
    Mod: Call me Dendrobranchiata prawn_86's Avatar
    Join Date
    May 2007
    Los Angeles
    Blog Entries

    Default Re: Free spreadsheets

    Great work, this will be of use to a heap of people i am sure

  3. #3
    skc's Avatar
    Join Date
    Aug 2008
    The dark side
    Blog Entries

    Default Re: Free spreadsheets

    Thanks Sinner. I haven't spent the time looking at them in any detail, but coming from you I am sure it's top shelf.

    Very generous of you for posting them.

  4. #4

    Default Re: Free spreadsheets

    It looses the formula's when I open with excel.

    Looks like you have discounted the dividend back to front in the second spread sheet.

  5. #5

    Default Re: Free spreadsheets

    Nice work Sinner

    Always good to see another trader/programmer


  6. #6

    Default Re: Free spreadsheets

    Quote Originally Posted by craft View Post
    It looses the formula's when I open with excel.

    Looks like you have discounted the dividend back to front in the second spread sheet.
    The P/E terminal values are also being discounted at 8.27% not 7.64% in that example. Discount rate is your required return, I'm guessing you are applying your 7.64% to the closing balance which is not correct.

  7. #7

    Default Re: Free spreadsheets

    craft, tomorrow (when I am back in front of the correct computer) I will post up an Excel version and use your comments to try to see what I did wrong with the second spreadsheet, please stay tuned as I'm sure your input will help me understand this stuff a little better.
    Disclosure: Long cash, gold, stocks.

Similar Threads

  1. Please feel free to make suggestions!
    By Joe Blow in forum General Chat
    Replies: 93
    Last Post: 29th-April-2008, 09:20 PM
  2. Free | Options Trading Course!
    By JetDollars in forum Derivatives
    Replies: 9
    Last Post: 29th-September-2005, 02:08 PM
  3. Free Trade Agreement
    By SharingIsCaring in forum General Investment and Economics
    Replies: 0
    Last Post: 6th-November-2004, 12:42 AM
  4. Spread Betting by IG Index - Tax free gains?
    By RichKid in forum Trading Strategies/Systems
    Replies: 0
    Last Post: 10th-October-2004, 04:14 PM
  5. Comments on Oz/USA free trade agreement
    By Jett_Star in forum General Investment and Economics
    Replies: 1
    Last Post: 8th-August-2004, 07:00 AM

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Aussie Stock Forums