Calculation of Sharpe Ratio with Excel

Calculate the Sharpe Ratio with Excel This article describes how you can implement the Sharpe Ratio in Excel. As an alternative method, I’ll also give some VBA code that can also be used to calculate the Sharpe Ratio. If you just want the spreadsheet, then click here, but read on if you want to understand its implementation. The Sharpe Ratio is a commonly used benchmark that describes how well an investment uses risk to get return. Given several investment choices, the Sharpe Ratio can be used to quickly decide which one is a better use of your money. It’s equal to the effective return of an investment divided by its standard deviation (the latter quantity being a way to measure risk). This is the Sharpe Ratio formula There are several assumptions which can often mislead investors. The primary failing is that the math assumes the investment returns are normally distributed. This isn’t always the case – sometimes returns can be skewed or have other characteristics not described by the normal distribution The math behind the Sharpe Ratio can be quite daunting, but the resulting calculations are simple, and surprisingly easy to implement in Excel. Let’s get started! Steps to Calculate Sharpe Ratio in Excel Step 1: First insert your mutual fund returns in a column. You can get this data from your investment provider, and can either be month-on-month, or year-on-year. Step 2: Then in the next column, insert the risk-free return for each month or year. This is literally the return you would have got if you’d invested your money in a no-risk bank account (in case you need to, raise the yearly return to a power of 1/12 to convert it to a monthly return). Step 3: Then in the next column, subtract the risk-free return from the actual return. This is your Excess Return Step 3: Now calculate    the average of the Excess return. In the example above the formula would be =AVERAGE(D5:D16) the Standard Deviation of the Exess Return. For my example, the formula would be =STDEV(D5:D16) Finally calculate the Sharpe Ratio by dividing the average of the Exess Return by its Standard Deviation (in my example this would be =D18/D19) VBA for the Sharpe Ratio A cleaner solution is the following VBA function. Function SharpeRatio(InvestReturn, RiskFree) As Double Dim AverageReturn As Double Dim StandardDev As Double Dim ExcessReturn() As Double Dim nValues As Integer nValues = InvestReturn.Rows.Count ReDim ExcessReturn(1 To nValues) For i = 1 To nValues ExcessReturn(i) = InvestReturn(i) - RiskFree(i) Next i AverageReturn = Application.WorksheetFunction.Average(ExcessReturn) StandardDev = Application.WorksheetFunction.StDev(ExcessReturn) SharpeRatio = AverageReturn / StandardDev End Function This function can be called by giving it two arguments; the first is the range containing the investment returns, while the second range contains the risk-free interest rates. For my example, the formula would be =SharpeRatio(B5:B16,C5:C16). Quantifying Investment Risk: The Sharpe Ratio Key Points     Calculating Sharpe Ratios Putting Sharpe Ratios to Work Other Considerations Points to Remember Much as coaches use statistics to help them evaluate the performance of their sports team and individual players, plan sponsors can turn to quantitative tools to aid them in selecting investment options and monitoring their performance. One of the most valuable is the Sharpe ratio, a frequently used formula for comparing investments to determine which offer the most return for a given amount of risk. Simply put, the ratio measures risk-adjusted returns. The Sharpe ratio is essential for making a fully informed decision -- not just one based on past returns. Risk-adjusted return is a key consideration for fiduciaries in carrying out their responsibility to offer an investment menu that enables participants to assemble portfolios with a wide variety of risk and reward combinations. Specifically, the Sharpe ratio measures the amount of return earned per unit of risk. It was devised in the 1960s by William F. Sharpe, a pioneering portfolio theorist, former finance professor at Stanford University and a Nobel Laureate in economics. The ratio -- which can be applied to individual securities, pooled funds such as mutual funds, and portfolios -- is commonly used to rank mutual funds with similar objectives over a given period of time. More versatile than some other risk measurement tools, it can be employed to compare investments from different asset classes. Calculating Sharpe Ratios Calculating the ratio is straightforward. The formula is: Sharpe ratio = (investment's return % - risk-free return %) ÷ investment's standard deviation In this formula, risk-free return is usually represented by the yield on a 90-day Treasury bill. Standard deviation, a common measure of volatility, measures the degree to which an investment's returns over a given period -- three years for example -- varied from the investment's average return over that period. The higher the standard deviation, the greater the variation. A hypothetical example shows how the Sharpe ratio is determined. If a fund produced an average annual return of 12% over the most recent three years, with a standard deviation of 10, and the T-bill rate averaged 4%, the fund's Sharpe ratio would be .80 (12% - 4% ÷ 10 = .80). Putting Sharpe Ratios to Work In practice, it's often sufficient to remember this rule of thumb: the higher the Sharpe ratio, the better an investment's returns have been relative to the amount of investment risk the investor has taken. Sharpe ratios can be obtained from fund companies and also from mutual fund rating and ranking services, such as Morningstar, Standard & Poor's, and Lipper. The Sharpe ratio has pros and cons. On the plus side, it avoids a drawback of alpha and beta, two related measures of volatility frequently used in fund analysis. Unlike the Sharpe ratio, they measure volatility against an index benchmark, which in practice may not be closely correlated to the fund. The Sharpe ratio uses only the volatility of the investment itself, based on standard deviation of its returns. As a result, it can be used to directly compare equity and fixed-income funds. The Sharpe ratio's main disadvantage is that it's just a raw number. As such, it's not meaningful except in comparison with ratios for other investments over the same time period (and, normally, with similar objectives). Another shortcoming: The ratio doesn't take into account non-quantifiable factors that can affect performance, such as prevailing economic and market conditions or a change in fund managers. In addition, when comparing investments with negative returns, the calculation can produce a ratio that is counterintuitive -- that is, a fund with a higher standard deviation may have a higher Sharpe ratio than another fund with a lower standard deviation. In such cases, other risk assessments need to be considered. Risk Measures at a Glance In addition to the Sharpe ratio, here are four measures of risk often used in investment analysis: Beta compares an investment's volatility against a benchmark such as the S&P 500. It shows how an investment's historical returns have fluctuated in relation to the broader market represented by the benchmark. For example, a beta of 1.20 would indicate that a fund had fluctuated 20% more than the benchmark, which has a beta of 1. Alpha shows the relationship between an investment's historical beta and its current performance. An alpha of 0 indicates the investment performed as expected. A positive alpha means the investment returned more than its beta indicated; a negative alpha signifies that it returned less. R-squared(R2) quantifies how much of a fund's performance can be attributed to the performance of a benchmark index. The value of R2 ranges between 0 and 1 and measures the proportion of a fund's variation that is due to variation in the benchmark. For example, for a fund with an R2 of 0.70, 70% of the fund's variation can be attributed to variation in the benchmark. Standard deviation reveals the volatility of an investment's returns over time, with a high standard deviation indicating greater historical volatility. Standard deviation can be used to compare any type of security with any other. Other Considerations In addition, relying on Sharpe ratios based on readily available fund data may not give a sufficiently long-term view of a fund's risk-adjusted performance. In cases where standard deviation is provided only for a fund's most recent three-year period, additional research is required in order to calculate the ratio for longer periods. Like other risk assessment tools, the Sharpe ratio is also open to the broad criticism that it can only show how investments have behaved in the past, which, of course, may not be a reliable predictor of future performance. While the Sharpe ratio has limitations, it is regarded as a valid statistic for comparing funds and other investment assets. Used as a screening tool, it provides an objective measure of an investment's riskadjusted past performance. Used in conjunction with well-defined selection criteria and monitoring policies, it can help plan sponsors create and maintain a suitable array of investment choices for the benefit of plan participants. Points to Remember 1. 2. 3. 4. The Sharpe ratio is a statistical tool for comparing the risk-adjusted performance of investments over a given time period. The ratio is frequently used to rank mutual funds and other pooled funds. It can also be used to compare individual securities and investment portfolios. The ratio measures how much an investment returned in excess of a risk-free investment per unit of risk taken. The three-month Treasury bill rate is often used to represent the risk-free return, while standard deviation of returns represents risk in the formula for calculating the ratio. Like other risk measures, the Sharpe ratio has limitations. It cannot predict an investment's future risk-adjusted performance, nor does it shed light on qualitative and external factors, such as a change in fund management, that may affect performance.