Risk Adjusted Return - Sharpe Ratio using MS excel sheet free

(Here is a sample Excel sheet to compute the Risk adjusted Return using Sharpe ratio if you directly want to go to the mathematics)

A ratio was developed by Nobel Laureate Bill Sharpe to measure risk-adjusted return of an investment. It is calculated by subtracting the risk-free rate from the rate of return for a portfolio and dividing the result by the standard deviation of the portfolio returns. Or,

Sharpe Ratio = Excess return over risk free return/ Annualized standard deviation of returns

The Sharpe ratio tells us whether the returns of a portfolio are due to smart investment decisions or a result of excess investor risk. This measurement is very useful because although one portfolio or fund can reap higher returns than its peers, it is only a good investment if those higher returns do not come with too much additional risk. The greater a portfolio's Sharpe ratio, the better its risk-adjusted performance has been. A lower number is worse.

Stocks have performed better, on a risk-adjusted basis than Treasury Bonds, because the Sharpe Ratio on stocks is higher than Treasury Bonds. A negative Sharpe Ratio is considered very bad. It means you could have done better, on a risk adjusted basis, by holding cash. The point of risk adjusted return is not to look at return in a vacuum, but rather to consider how much risk you had to take in order to generate "excess return" - the amount of return over a market benchmark or the risk free rate i.e. 10 year Treasuries or 90 day T-bill.

Volatility, also known as the standard deviation of return, is the statistical measure of risk in a portfolio. A standard deviation of zero would mean an investment has a return rate that never varies, like a bank account paying compound interest at a guaranteed rate. (Definition: Standard Deviation is a statistical measure of the distance a quantity is likely to lie from its average value. In finance, standard deviation is applied to the annual rate of return of an investment, to measure the investment's volatility, or risk.)

Here is an example:

Say you have an investment portfolio, You can calculate the value of your investment account periodically, say every month.

Then calculate the average monthly return over some number of months, by averaging the returns for those months. You also calculate the standard deviation of the monthly returns over the same period. Note that you might need data for few months to calculate the standard deviation.

Then annualize the numbers by:

- Multiplying the average monthly return by 12

- Multiplying the standard deviation of the monthly returns by square root of 12

You also need a number for the "risk-free return" which is the annualized return currently available on "risk-free" investments. This is usually assumed to be the return on a 90-day T-Bill (currently about 5% per year)

You now calculate the "Excess return" which is the annualized return achieved by your investment in excess of the risk-free rate of return available. This is the extra return you receive by assuming some risk. Then the Sharpe Ratio is calculated as:

Sharpe Ratio = [Annualized annual return - Risk free return] / Annualized standard deviation of returns;

Here is how you can use Microsoft Excel to calculate Sharpe Ratio for your portfolio. Fill out each column in the excel sheet as follows:

You can see a sample calculation of Sharpe ratio in excel sheet here.

- Column A: Fill out Month Name
- Column B: Beginning Balance
- Column C: Ending Balance
- Column D: Monthly Returns = ((C/B)-1)*100 (to get %)
- On cell E2: Get the average of all monthly returns. E2 = AVERAGE(populated cells from column D)
- On cell E3: Annualized returns = E2 * 12;
- On cell F2: Get the Standard Deviations for all months. F2 = STDEV(populated cells from column D)
- On cell F3: Annualized standard deviation = F2 * SQRT(12);
- On cell G2: Get the 3 month treasury bill from www.ustreas.gov, lets say it is 5%
- On cell G3: Sharpe Ratio = (E3 - G2)/F3

Given all this data, you can strive to achieve higher Sharpe Ratio for your portfolio. Or if your Sharpe Ratio is low, then you can figure out where to fix it.

-Nidhi

Related Posts:

• Why are ETFs so sexy
• How to select Mutual Funds
• Position Sizing