Simba backtesting spreadsheet: risk metrics, risk ratios

This blog article is the second of a series intended to document the inner workings of the Simba backtesting spreadsheet. The objective is two-fold:

  • Provide a better understanding of how the spreadsheet works, to facilitate peer reviews and future maintenance.
  • Describe techniques that have been used to compute various investment metrics in a compact manner, which could be useful in other contexts (e.g. your own spreadsheet).

The first article of the series discussed the layered structure of the spreadsheet. The intent of this second article is to elaborate on how risk metrics (e.g. volatility, drawdowns, etc) and risk ratios (e.g. Sharpe, Sortino, etc) are computed.

To follow this article in detail, you should open a separate window using version 17a of the Simba spreadsheet, which can be downloaded by clicking on this link. The cell references are of course subject to change in future updates, even if the general structure will probably stay very similar.

Basic metrics and Sharpe ratio

The discussion will be mostly centered on the Compare_Portfolios worksheet. Some metrics and ratios are also computed in other tabs of the spreadsheet, but Compare_Portfolios provides the most extensive set of those.

Risk ratios typically divide one quantity by another, to attempt to capture a measure of both risk and reward in one number, so that different strategies can be easily compared, and possibly optimized aiming at ‘the best ratio’. Such an approach can be viewed as somewhat debatable though, compared to analyzing the individual risk and reward metrics in a separate manner, each having their own merits. The Simba spreadsheet breaks down the ratio computation in two steps (basic risk and return metrics first, then risk ratios), so that either approach can be used.

The most well known ratio is probably the Sharpe ratio. Historical and technical background can be found in the corresponding wikipedia page. The 1994 revision of the ratio is used here, using a benchmark (usually some sort of ‘risk free’ vehicle) to compare the portfolio to.

Check the screenshot provided below, and the formula in cell E109. The Sharpe ratio formula takes the difference between the (arithmetic) average return of the portfolio being studied (E97) and the average return of a benchmark (E91), and divides it by the standard deviation of the excess returns of the portfolio against the benchmark (E101). The formula is also protected against extreme cases (e.g. null or quasi-null deviation).

Simba Blog2 Compare

To fully understand the underlying mechanics, let’s switch to the Portfolio_Math tab. As was described in the first article of this series, this worksheet not only computes the annual returns of the portfolios being studied, but also includes various benchmarks, which are themselves modeled as portfolios. Check columns I, J, and K, which are there to assist the computation of various risk metrics and ratios.

Simba Blog2 Portfolio Math

Column J in particular is used for computing both Sharpe and Sortino ratios, and is dubbed “Minimal Acceptable Return” (aka MAR, a terminology coming from the Sortino ratio). Its default setting is simple, the Treasury Bills data series (usually perceived as a risk-free return). It can be easily customized by changing the blue cells in the MAR column.

If you come back to the Compare_Portfolios tab, you’ll easily figure out how the MAR benchmark returns are used in the formulas used in cells E91 and E101.

Semi-deviations and Sortino ratio

Debates about shortcomings of risk ratios are endless, and there is no shortage of alternate measures being suggested. One of the most popular is the Sortino ratio, and its core idea is well described by the corresponding Wikipedia page:

It is a modification of the Sharpe ratio but penalizes only those returns falling below a user-specified target or required rate of return, while the Sharpe ratio penalizes both upside and downside volatility equally. 

In Compare_Portfolios, the formula for the Sortino ratio is very similar to the formula for the Sharpe ratio, see cell E110. The more complicated math happens for the downside volatility, also known as downside (semi) deviation. Technical background can be found in this Investopedia page. The formula in cell E102 implements such logic, and one can notice that this is pretty close to the inner mechanics of a standard deviation formula, but the MIN(0, …) part is what makes the formula focus on downside volatility compared to the MAR benchmark.

Although this isn’t used in any risk ratio, for symmetry purposes, the upside (semi) deviation is also provided as a basic metric of interest (see cell E103), and of course, defined in a very similar way as the downside (semi) deviation.

Portfolio Alpha and Beta

To characterize the historical returns of a given investment vehicle, academics and practitioners often use the concept of Alpha and Beta (Alpha is a measure of reward, while Beta is more of a measure of risk):

  • Alpha essentially captures the excess return compared to a market benchmark (e.g. a 60/40 portfolio in the US),
  • Beta essentially captures the volatility of the investment compared to the market benchmark.

The corresponding formulas are a tad complicated, Beta using a ratio between covariance and variance quantities, and Alpha building on top of the Beta computation. Wikipedia pages (Alpha and Beta) provide plenty of background for the curious mind who would be eager to unravel those complex formulas.

Note that those formulas refer to a risk-free benchmark and a market benchmark portfolio, which are defined (and can be customized) in columns I and K of the Portfolio_Math tab. International investors might notably be interested in changing the default setting of the market benchmark, which is centered on US asset classes.


The various risk ratios discussed so far (e.g. Sharpe, Sortino, Beta) tend to associate risk with volatility, i.e. year over year variations of return. This is certainly a common practice, notably for academics, but individual investors (accumulators or retirees) might perceive that what matters more in real life are portfolio balance drawdowns, and their cumulative effect (emotional and quantitative) over a long time period. As Investopedia explains:

A drawdown is the peak-to-trough decline during a specific recorded period of an investment, fund or commodity. A drawdown is usually quoted as the percentage between the peak and the subsequent trough. 

Readers of the first article of the series might remember that, in the Portfolio_Math tab, the portfolio balance trajectory is computed in both nominal and real terms. Scrolling down a bit further, those results are leveraged (e.g. check cell AB1371 and below), to perform a peak to trough calculation for each year of interest.

In Compare_Portfolios, this is used in turn to compute the maximum drawdown (the deepest crisis) in the appropriate time period (e.g. check cell E104, a simple MAX() formula).

In Analyze_Portfolio, the same data is used to provide a drawdown chart. As you can see below, the portfolio being studied (P1) went down nearly 30% in nominal value three times since 1970 – quite disturbing times for investors.

Simba Blog2 Drawdowns

The chart comes with a few controls (the blue cells above), allowing the user to display the chart in nominal or real terms, and to switch on/off the display of an unbalanced portfolio (in addition to the regular display of an annually rebalanced portfolio). We’ll come back to unbalancing in a separate article.

Mechanically, the nominal/real control (cell E166) is a drop-down menu, something easily achieved with the data validation function of Excel (or LibreOffice).

In this case, some ‘under the hood’ logic occurs in Analyze_Portfolio. The data validation list is made of the two cells E267/E268. And the choice being made in the drop-down menu is translated to a boolean quantity in cell E272. The intent is to reference this boolean value in Portfolio_Math (see cell M92), which controls the actual peak to trough math (e.g. check cell M1371 and below), the peak being computed by the MAX() part of the formula.

Finally, let’s explain the warning in cell B93 of Compare_portfolios. All computations in the Simba spreadsheet are performed based on annual returns. This means that intra-year events aren’t captured (e.g. Black Monday in Oct-87). Practically speaking, annual drawdown math remains significant as the truly impacting crises last longer than a few months, but still, please remember that such Simba computations are somewhat coarse. The same point applies to standard deviation (and variations of), by the way.

Ulcer index and Ulcer performance ratio

A deep drawdown is certainly a traumatic event, but it is a little misleading to simply focus on the maximum (deepest) drawdown of a time period, a one-time event. As recent investors experienced, two deep drawdowns in a row can have quite a rattling effect on one’s nerves (and wallet!).

The aptly named Ulcer index (and the corresponding ratio) provides a new perspective on risk, essentially quantifying the depth and duration of all drawdowns occurring in a time period. It is described on Wikipedia, but the discussion provided by the author (Peter Martin) is much more informative (click here), providing extensive background on why this seems to be a more relevant metric than volatility metrics (e.g. standard deviation), and how to perform the corresponding math.

Once we have the drawdowns occurring year over year in a given time period for a given portfolio, the Ulcer Index calculation is straightforward (see cell E105 in Compare_Portfolios, an array formula computing the square root of the average annual drawdowns to the power of 2). Note that such formula gives more weight to deep drawdowns, thanks to the power of 2.

A corresponding risk ratio (the Ulcer performance ratio) is computed (see cell E111) by simply replacing a measure of volatility in the denominator by the Ulcer Index.

Visualizing risk(s) in various ways

As previously mentioned, equating risk with volatility (e.g. standard deviation) may satisfy academics, but may not be such a good recommendation in real life, oversimplifying the concerns of investors and their personal circumstances. Looking at things from various angles, either using basic risk and return metrics, or looking at risk ratios (or both) might provide more insights. The Simba spreadsheet provides flexibility in this respect.

The following screenshot compares the same five portfolios over the same time period, using two very different risk metrics on the horizontal axis (one is the usual standard deviation, the other is the safe withdrawal rate over 30 years cycles -which we’ll discuss in more depth in a separate article), while the vertical axis displays annual returns (i.e. rewards). The control in the blue cell allows the user to select which risk metric to focus on.

Simba Blog2 Risk Return

Let’s explain the corresponding mechanics. As we’ve seen, all risk metrics are available in Compare_Portfolios (e.g. rows 94 to 124). Checking which cell depends on the control cell of the left chart (E129), you’ll find cell C196 in the ‘under the hood’ section, which matches the value of the control cell to the list of risk metrics above, in order to compute a row index. Based on C196, the formulas in cells E196/F196/etc are simple OFFSET() formulas to get the value of the corresponding risk metrics. And the chart uses those cells (E196/F196/etc) as data source. Such chart display allows separating the assessment of risk vs. reward, while getting a good sense of the relationship.

In the Lazy_Portfolios worksheet, more extensive charts are provided to compare the risk metrics and the risk ratios for the 25 portfolios being compared. A very similar logic as the one we just explained is used (see rows 269 to 280).


Leveraging on the layered structure of the Simba spreadsheet, portfolio returns and drawdowns are computed in the Portfolio_Math tab for portfolios defined in the Analyze_Portfolio, Compare_Portfolios and Lazy_Portfolios tabs. In addition, returns for benchmarks (e.g. risk-free, US market returns) are also computed in Portfolio_Math.

Various types of risk metrics and risk ratios are then computed in Analyze_Portfolio, Compare_Portfolios and Lazy_Portfolios, according to common industry definitions. Those metrics and ratios are displayed in tables, and also illustrated in charts driven by flexible controls. This is simply achieved by some ‘under the hood’ computations, driven by control cells.

Posted in Asset allocation, Market history, Market statistics, Portfolios
February 2018
« Jan    
Follow Financial Page on
%d bloggers like this: