# How to calculate the Sharpe ratio in Excel

You can watch a video tutorial here.

To calculate the Sharpe ratio in Excel, you need to have the following information in place:

1. The rate of return of the portfolio for a given period
2. The risk-free rate of return for the same period The Sharpe ratio is used to evaluate the relationship between risk and return for a portfolio. Excel does not have a function for this calculation but you can create the formula for the ratio in Excel. The formula for calculating the Sharpe ratio is:

– Sharpe ratio  = (return of the portfolio – risk-free return) / standard deviation of the excess returns of the portfolio
>The return of the portfolio is taken from the actual performance of the portfolio
>Risk-free return is the return of an investment that is risk-free such as government bonds
>The standard deviation of the portfolio will be computed using the STDEV.P() function

– STDEV.P() function: returns the standard deviation of a set of numbers
>Syntax: STDEV.P(range of numbers)

### Step 1 – Compute the excess returns – Select the cell where the result is to appear
– Type the formula using cell references:
= Rate of return – Risk-free rate of return
– Press Enter

### Step 2 – Copy the formula – Using the fill handle from the first cell, drag the formula to the remaining cells
OR
a) Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
b) Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

### Step 3 – Compute the average excess returns – Select the cell where the result is to appear
– Type the formula using cell references:
= AVERAGE (range of Excess returns)
– Press Enter

### Step 4 – Compute the standard deviation – Select the cell where the result is to appear
– Type the formula using cell references:
= STDEV.P(range of Excess returns)
– Press Enter

### Step 5 – Format the standard deviation as percentage – Select the cell with the value
– Go to Home > Number
– Click on the percentage sign (%)
OR
Open the Format Cells window (Home > Number and click on the arrow to expand the menu OR Right-click and select Format Cells  from the context menu OR Go to Home > Cells > Format > Format Cells) and select Number > Percentage and click OK

### Step 6 – Compute the Sharpe ratio – Select the cell where the result is to appear
– Type the formula using cell references:
= ROUND(Average excess return/ Standard deviation,2)
Note: The ROUND() function is used to display 2 decimal places
Press Enter