Calmar-style return/drawdown ratio and a CAPM excess-return worksheet

Separate historical drawdown ratios from model-relative return residuals

This Calmar-style calculation divides supplied CAGR by the magnitude of maximum peak-to-trough drawdown over a specified observation window. Use a consistently sampled total-return wealth series and report the window, frequency and fee basis. Maximum drawdown is an observed path statistic, not a complete measure of tail risk or a forecast. Zero drawdown makes the ratio undefined.

The second calculation subtracts a supplied CAPM benchmark from portfolio return. It is a single-period residual, not an estimate of manager skill. Jensen’s alpha is generally estimated as a time-series regression intercept with statistical uncertainty. Here use F2 for portfolio return, C2 for risk-free return and E2 for market return over the same period; D2 is the supplied beta. Do not substitute a multi-year CAGR for a one-period return.

Calculate Calmar Ratio (CAGR / |Max Drawdown|)

A2 is annualized return as a decimal; B2 is the observed drawdown fraction, either sign convention. This unlevered wealth example requires CAGR>=-1 and drawdown magnitude<=1. A zero denominator returns an explicit undefined result.

=IF(OR(COUNT(A2:B2)<>2,A2< -1,ABS(B2)>1),"Invalid inputs",IF(B2 = 0, "Undefined (zero drawdown)", ROUND(A2 / ABS(B2), 2)))

Same-period return residual against supplied CAPM beta

F2=portfolio return, C2=risk-free return, E2=market return, all same-period decimals; D2=beta, which may be negative. The 0.02 result is 2 percentage points, not proof of repeatable outperformance.

=IF(COUNT(C2:F2)<>4,"Invalid inputs",ROUND(F2 - (C2 + D2 * (E2 - C2)), 4))

Further reading

• Microsoft Support — ABS function
• Jensen (1968) — The Performance of Mutual Funds in the Period 1945–1964