My new investment spreadsheet

Recently I’ve made a few changes to my investment strategy and those changes are now reflected in this investment spreadsheet, which you can download and use if you so wish (and at your own risk).

These changes have been made in line with my commitment to a Kaizen approach to investing, i.e. continuous small improvements.

The changes reflect lessons that have been learned in 2014 and most of the lessons relate to increasing my focus on higher-quality businesses with less debt.

The spreadsheet covers the first step in my investment analysis process, which is a purely quantitative step. With the new changes it now calculates the following various factors:

  • Dividend Discipline – The company should have a 10-year unbroken record of reliable dividends
  • Growth Rate – An average of the revenue, earnings and dividend growth rate over the last decade (uses book value instead of revenue for insurance companies and banks). Should be at least 2% per year
  • Growth Quality – A measure of how progressive growth had been for revenues (or book value as above), earnings and dividends over the last decade. Should be at least 50%
  • Profitability – Median 10-year net return on capital employed (“net” because it uses earnings after tax rather than the more usual earnings before interest and tax), or median 10-year return on equity for insurance companies and banks. Should be at least 7%
  • Debt Ratio – A ratio of company debt (borrowings) to Earnings Power (5-year average profit after tax). Should be below 5
  • Pension Ratio – Ratio of pension liabilities to Earnings Power (as above). Should be below 10
  • PE10 – A more robust way to value companies (especially defensive companies) than the standard PE ratio is the price to 10-year average earnings ratio. Should be below 30
  • PD10 – Price to 10-year average dividend ratio. Should be below 60
  • Market Comparison – Compares Growth Rate, Growth Quality, Profitability, PE10, PD10 and dividend yield against the FTSE 100. At least 3 out of those 6 should be above the market average

It also looks at insurance company leverage:

  • Capital Leverage – Premium to Surplus ratio (i.e. net written premium to tangible equity). Should be below 2
  • Equity Allocation – Investment capital (“float”) allocation to equities. Should be below 10%
  • Underwriting Profitability – Combined Operating Ratio. Should be below 99%

And bank leverage and liquidity:

  • Capital Leverage – Core Equity Tier 1 ratio. Should be above 10%
  • Liquidity – Liquidity Coverage Ratio. Should be above 100%
  • Liquidity – Net Stable Funding Ratio. Should be above 100%

The spreadsheet also contains an overview of the sort of investment questions I ask after a company manages to pass the various quantitative checks. The questions cover both the individual company and how it will fit in with the rest of the existing portfolio.

If you’re looking for somewhere to get 10 years of data you could try:

Or you can get a summary of the data, i.e. just the Growth Rate score and so on, for all eligible FTSE All-Share companies, on the UK Value Investor website.

Feel free to leave your favourite data source in the comments if you can think of any better ones than those I’ve listed, which should ideally cover 10 years of data.

Author: John Kingham

I cover both the theory and practice of investing in high-quality UK dividend stocks for long-term income and growth.

%d bloggers like this: