Treynor Ratio Performance Metric with Excel

Last Update: January 7, 2021

Portfolio performance metrics consist of portfolio expected or realized risk premium by unit of risk.

This topic is part of Investment Portfolio Analysis with Excel course. Feel free to take a look at Course Curriculum.

This tutorial has an educational and informational purpose and doesn’t constitute any type of trading or investment advice. All content, including spreadsheet calculations and data, is presented for personal educational use exclusively and with no guarantee of exactness of completeness. Past performance doesn’t guarantee future results. Please read full Disclaimer.

An example of portfolio performance metrics is Treynor ratio [1] which consists of portfolio expected or realized risk premium by unit of systematic market risk.

1. Formula notation.

1.1. Ex-ante or expected Treynor ratio formula notation.

E(t(r_{p}))=\frac{E(r_{p}-r_{f})}{E(\beta_{r_{p}})}

E(\beta_{r_{p}})=\frac{E(\sigma(r_{p},r_{m}))}{E(\sigma^2(r_{m}))}

Where E(t(r_{p})) = ex-ante or expected portfolio returns Treynor ratio, E(r_{p}-r_{f}) = ex-ante or expected portfolio returns risk premium, E(\beta_{r_{p}}) = ex-ante or expected portfolio returns market beta, E(\sigma(r_{p},r_{m})) = ex-ante or expected portfolio and market returns covariance, E(\sigma^2(r_{m})) = ex-ante or expected market returns variance, r_{f}\;or\;r_{b} = risk free or benchmark returns can be used.

1.2. Ex-post or realized Treynor ratio formula notation.

t(r_{p})=\frac{\bar{r}_{p}-\bar{r}_{f}}{\beta_{r_{p}}}

\beta_{r_{p}}=\frac{\sigma(r_{p},r_{m})}{\sigma^2(r_{m})}

Where t(r_{p}) = ex-post or realized portfolio returns Treynor ratio, \bar{r}_{p}-\bar{r}_{f} = ex-post or realized portfolio returns risk premium, \bar{r}_{p} = ex-post or realized portfolio returns mean, \bar{r}_{f} = ex-post or realized risk free returns mean, \beta_{r_{p}} = ex-post or realized portfolio returns market beta, \sigma(r_{p},r_{m}) = ex-post or realized portfolio and market returns covariance, \sigma^2(r_{m}) = ex-post or realized market returns variance, r_{f}\;or\;r_{b} = risk free or benchmark returns can be used.

1.3. Ex-post or realized Treynor ratio risk free or benchmark returns through time period assumptions formulas notation.

if\;r_{f,t=1\rightarrow n}\neq c\rightarrow t(r_{p})=\frac{\bar{r}_{p}-\bar{r}_{f}}{\beta_{r_{p}-r_{f}}}

\beta_{r_{p}-r_{f}}=\frac{\sigma(r_{p}-r_{f},r_{m}-r_{f})}{\sigma^2(r_{m}-r_{f})}

if\;r_{f,t=1\rightarrow n}=c\rightarrow t(r_{p})=\frac{\bar{r}_{p}-r_{f}}{\beta_{r_{p}}}

if\;r_{f,t=1\rightarrow n}=0\rightarrow t(r_{p})=\frac{\bar{r}_{p}}{\beta_{r_{p}}}

\beta_{r_{p}}=\frac{\sigma(r_{p},r_{m})}{\sigma^2(r_{m})}

Where r_{f,t=1\rightarrow n} = ex-post or realized risk free returns through time period, n = number of observations in time period, c = constant, t(r_{p}) = ex-post or realized portfolio returns Treynor ratio, \bar{r}_{p}-\bar{r}_{f} = ex-post or realized portfolio returns risk premium, \bar{r}_{p} = ex-post or realized portfolio returns mean, \bar{r}_{f} = ex-post or realized risk free returns mean, \beta_{r_{p}-r_{f}} = ex-post or realized portfolio returns risk premiums market beta, \sigma(r_{p}-r_{f},r_{m}-r_{f}) = ex-post or realized portfolio and market returns risk premiums covariance, \sigma^2(r_{m}-r_{f}) = ex-post or realized market returns risk premiums variance, r_{f} = ex-post or realized risk free return, \beta_{r_{p}} = ex-post or realized portfolio returns market beta, \sigma(r_{p},r_{m}) = ex-post or realized portfolio and market returns covariance, \sigma^2(r_{m}) = ex-post or realized market returns variance, r_{f}\;or\;r_{b} = risk free or benchmark returns can be used.

2. Excel example.

2.1. Treynor ratio performance metric data.

  • Data: S&P 500® index replicating ETF (ticker symbol: SPY) adjusted close prices and market portfolio [2] monthly arithmetic returns (2007-2016).

2.2. Treynor ratio performance metric calculation.

  • Note: ex-post or realized Treynor ratio calculation and risk-free return assumption not fixed and only included for educational purposes.

2.3. Treynor ratio performance metric calculation formulas.

3. References.

[1] Jack L. Treynor. “How to Rate Management of Investment Funds”. Harvard Business Review. 1965.

[2] Eugene F. Fama and Kenneth F. French. “Common Risk Factors in the Returns on Stocks and Bonds,” Journal of Financial Economics. 1993.