Data analysis tools & spreadsheets
The following is a brief selection of some of the Excel spreadsheets I have built and use within my business. Please note that as spreadsheets have been designed on a Macintosh operating system there may be some formatting issues if opening/viewing on a PC (or device without Microsoft Excel).
4Sight
|
4Sight is a trading simulator designed to help advisers test, learn from, and improve their investment decision-making processes.
Unlike traditional portfolio and risk simulators which "normalise" the statistical properties and behaviour of assets, 4Sight uses an algorithm that takes real-world (non-parametric) returns and incorporates exogenous economic, political, environmental and market events. Importantly, while the data driving the simulator is real, to encourage investors to look at the data supporting their investment decisions, the names of countries have been changed. Also unlike most simulators, investment costs are included, as are alternative assets such as residential real estate and commodities. To test the investor's skill level, 4Sight also pits the investor against other investors, each of which is programmed to implement their own unique investment style. Background The ideas behind 4Sight not only come from observing the behaviour of investors (and investment advisers) but also from the growing body of research into neurofinance which finds that investors' decision-making process and abilities are often influenced by the body's physiological responses to stimuli (in particular the cognitive response to fluctuations in testosterone and cortisol). Learning to compensate for, if not control, one's biases (emotional, cognitive and chemical) sets the framework for a more rational, robust and consistent approach to investment and risk management. In Development: 4Sight+ 4Sight+ will allow the user to run their portfolio (in the simulation environment) as a professional investment fund. This includes modeling fund flows based on both relative and absolute performance (after-fees), as well as business and staff costs. The idea to include these features comes from the observation that many investors are willing to underperform, and pay higher fees, so long as returns were not negative, while many investors feel compelled to withdraw from financial investments or managers if their performance is negative, even returns were better than their peers. |
Monte Carlo Simulation - Portfolio modelling
|
This Monte Carlo simulator provides a comparison of three portfolios (plus Risk Free Rate), with ability to incorporate the impact of tax, fees, composition of returns, portfolio turnover, return objectives and inflation.
Importantly the simulation allows us to take into account the client's cash flows (contributions/withdrawals). This allows the user to consider how a static portfolio (i.e, constant Strategic Asset Allocation) might be expected to perform. Analysis includes the following:
Incorporated in the analysis are several qualitative factors, such as the client's personality and the importance of reaching certain goals. This includes two questions to assess the client's attitude toward statistically measurable risks that tend to elicit emotional rather than rational responses (gambling question and insurance question). In this simulator we try to capture client reactions to potential loss both as probability of loss, and magnitude of loss. Based on evidence presented by Kahneman, Tversky and others, we see that people tend to make decisions that are overly reliant on mental accounting biases. This means that even if we know how a client feels about a specific level of risk for one specific event, we cannot extrapolate this to describe how they would react to another event For example, if a person is presented with a gamble where heads they win $100 and tails they win nothing, and they tell us they would be willing to pay $35 for such a bet, then we might expect them to pay $70 for the same gamble but with $200 prize money. In reality they tend to become more cautious as the stakes risk. However, if the gamble was framed in reverse, and the outcomes offered potential losses, then we might expect the investor to become more risk-seeking. This illustrates the non-linear nature of risk and loss aversion. In the spreadsheet this flows through to two charts: "Portfolio Selection - Behavioural Risk Adjusted Mean", and "Client Satisfaction". Note: As it stands today, I do not use these qualitative aspects to determine a client's portfolio. Rather this was an exercise in thinking about how client questionnaires and risk profiles may be better administered to clients (this is perhaps a job for a psychometrician to develop further?).
|
Advanced Portfolio Risk Management: making predictions in uncertain markets
|
One of the greatest challenges any investment advisor must face is building investment portfolios when market and asset analysis provide widely conflicting views as to an asset's prospects, and suitability for the client.
Using a current example, while one set of data may suggest US equities are moderately overvalued, another may argue that the price is justified due to a low interest rate environment or strong near-term earnings growth. This simulator attempts to illustrate both the problems and opportunities this can create. Broken down to it's most basic level, if we can agree that over the very long term an investor's return (expressed as CAGR) should reflect the rate of real (inflation-adjusted) earnings growth, less tax and fees, then we also be able to agree that most volatility is due to investors (subjective) pricing of those earnings[1]. If this assumption is right, then over the very long term (multiple decades) investor return forecasts should be calculated on long-term real earnings growth and volatility on the value of their investment re-based / adjusted to reflect current market anomalies (i.e., where asset prices are under/over valued). Of course undertaking this type of analysis will inevitably cause inaccurate short-term forecasts, but over the long term it can offer valuable insights by estimating the return from assets on both a historical (investment capital adjusted) basis, as well as based on shorter-term market forecasts. This enables us to determine whether an asset that may currently look very expensive is, in fact, a better investment proposition than an asset that looks very cheap but still offers a lousy return. This simulator uses three measures of relative value (forward P/E, Risk Premium and 10-Year compound return) with the user able to adjust the settings to suit their own opinion of relevance. Of course we could complete this analysis using some fairly basic maths, but it would be challenging to make sense of how this might interact with an investor's cash flows. This simulator simplifies this process and allows the user to manipulate data to ensure a more thorough analysis of portfolio outcomes. This spreadsheet runs a series of Monte Carlo simulations, separating the four portfolios into eight different accounts, each of which run 10,000 independent trials over 20 time periods while incorporating the client's cash flow requirements/contributions (to correct for sequencing risk). Volatility and returns are modelled on an after-tax basis. Although time periods are, by default, illustrated in single-year increments this can be easily adjusted by changing return and volatility inputs (ie., to adjust to period (n) from annualised return/volatility statistics: returns = r^(n), volatility = vol*(sqrt(n)) ). [1] This forms a topic for a considerable amount of research and literature that suggests an inverse relationship between the level of dividends paid and asset volatility. An extension of this is the seemingly conflicting finding that franking credits are not fully valued by the market. One possible explanation for this is anchoring and mental accounting. I have also included our general formula for measuring downside risk. I developed this as a way to quickly and cleanly quantify downside risk under various scenarios. Here, we measure the cumulative cash flows of a risky asset against a low-risk asset on a comparable investment horizon. Therefore we use the yield curve as our yardstick, not the cash rate. This gives us the potential alpha for the risky asset (or portfolio). To this we add 1, then divide the current market price of the asset (or market's) earnings: this gives us an approximate of the level at which earnings must be priced for the risky asset to deliver the same return as the low-risk asset. Once we have this we can compare this against a range of factors, such as current price of earnings, historical price of earnings, or target price of earnings (the list is nearly limitless). We do this by measuring the quantum of change required, divided by the volatility of the factor, multiplied by the period of our analysis. Please contact me for a copy. |
Property vs Shares (Simulator)
|
Despite roughly 40% of Australians' private wealth being tied up in residential property (predominantly the family home) most investors and advisers hold a somewhat distorted, over-romantisiced view of property as an investment class.
Of course this is not entirely unexpected. Not only because of the tendency for investors to treat anecdotal and empirical evidence as equals but because the other key source of information tends to be from real estate agents, "advertorials" and industry associations. The objective of this simulator is to not only help clients gain a better understanding of property, but also to help other advisers illustrate to their clients the inherent risks and costs associated with property investment.
|
Life Expectancy Calculator
|
Based on Australian Life Tables (2014), this simple life expectancy calculator is designed to help with client discussions around issues such as capital adequacy and the risks of outliving their retirement savings.
|
Volatility-adjusted returns: adjusting for time, cash flows and leverage
|
Is volatility risk? In my opinion, this question is a bit like asking whether the sky is blue: the answer reflects an individuals interpretation of the question.
Over the past decade designing portfolios for, and consulting with, investors large and small, the volatility/risk debate is still the one I find most intriguing. Part of this, I believe, is because for most of us the idea of losing money makes us feel very uncomfortable, even if it's only a temporary "paper loss". The idea of redeeming your money today for less than you may have had a day, week or month earlier, strikes fear into many investors' hearts. This fear is even more powerful when an investor seems helpless to take action to recover those losses (such as contributing more to their savings). With emotions running high, it's not surprising we see many investors continue to make irrational decisions; for example, people who, on the day of their retirement move all their wealth to cash, despite the thirty or more years of retirement ahead of them. For some, the prospect of a slow and steady errosion of their wealth is better than the somewhat bumpier flight path offered by a diversified portfolio. Of course, if we were being truly rational, each individual investor's focus should be on maximising the value of their wealth at a predetermined and specific point in the future. Risk in this case can be viewed in probabilistic terms: what are the chances I will not reach this goal? What is the probability of landing at each point above or below this goal? Etc.,etc.... In some ways it is fortunate that most people don't think this way. Not only would this put many investment advisers out of a job, but it would likely result in problems raising capital and would make tools such as monetary policy much less effective. Nevertheless, if we, as individual investors, take the effort to differentiate between volatility and risk, considerable benefits await. Long-term investors with a tolerance for risk can be paid for their ability to stay the course. Liquidity premiums, additional risk premia (in particular through non-market-cap sector allocation), generally more favourable tax treatment, and the sensible use of leverage. This spreadsheet seeks to provide a simple illustration of how an investor should allocate their capital over their stated investment horizon while factoring in their comfort level with debt. To do this each portfolio is modelled independently with their returns over time discounted to volatility in order to reflect (with the stated confidence level) the minimum level of return the portfolio would be expected to achieve. This incorporates the client's cash flows. By laying each of these "risk adjusted" portfolios side-by-side we can determine which one provides the best risk-adjusted return. This is assumed to be the most appropriate asset for the client to select. Also illustrated are the expected return and portfolio value if the client's "exit" date were pushed back 10 years or brought forward 10 years. Importantly, "exit" date is just meant to infer an arbitrary "investment horizon" used by clients. For example, let's say the aforementioned recent retiree is 65 years of age and uncomfortable investing beyond 5 years, even though he is in good health and expected to live into his nineties. By educating our clients and quantifying the difference in prospective return and potential risk we find many investors become much more accepting of volatility and comfortable taking a long-term view of their investment journey.
|
Income Tax Calculator
|
A quick and easy Income Tax calculator (based on tax rates announced in the 2016 Federal Budget).
|