# 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:

- The rate of return of the portfolio for a given period
- 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**