Follow along with me in this effortless Microsoft Excel tutorial to learn how to calculate the Sharpe Ratio in Microsoft Excel.

If you’ve ever worked in investment analysis, you know that comparing returns without considering risk is like evaluating a project only by revenue, not cost. That’s where the Sharpe Ratio comes in. It shows how much return you’re getting for the risk you’re taking.

But let’s be honest, calculating it in Excel isn’t always intuitive. Between standard deviations and risk-free rates, it can feel like you’re juggling too many variables. I’ve been there, and that’s exactly why I created this tutorial. I’ll show you step-by-step how to calculate the Sharpe ratio in Excel without any confusion. Let’s walk through it together.

Manual Calculation Using Cell References

There are multiple automated ways to calculate the Sharpe Ratio in Excel. However, those methods don’t make you an expert in understanding the underlying elements of this measurement for investment performance tracking.

So, you must learn from the basics, like calculating the mean return, risk-free rate, standard deviation, etc. Then, input these values in the standard Sharpe Ratio calculation formula to get a resulting value.

Let me walk you through the manual method to calculate the Sharpe index in simple steps using real-world data.

Firstly, you must organize the source data, which could be a monthly returns data table of a mutual fund, in the format shown above.

Secondly, create the following fields in the data table:

• Average Return
• Std Dev of Return
• Risk-Free Rate

Thirdly, since the data table is ready, let’s begin calculating the reward-to-variability ratio or the Sharpe measure for the investment using the following steps:

Calculate the Mean of Returns

Select the cell where you want to calculate the mean of the monthly returns for a mutual fund and enter the following formula in it:

=AVERAGE(B5:B16)

Ensure you change the cell range reference in the AVERAGE formula according to your own dataset. For the current exercise, it’s B5:B16 as this range contains all the monthly return values of the mutual fund.

Hit Enter to calculate the cell.

Change the percentage value to decimals. For example, 11% will be 0.11.

Calculate the Std Dev of Returns

To calculate Std Dev for the returns, select a cell where you want the output and enter the following formula into it:

=STDEV.S(B5:B16)

Don’t forget to change the cell range reference according to your source dataset.

Hit Enter to calculate the Std Dev for returns.

Get the Risk-Free Rate

You can fetch the risk-free rate from the 3-Month Treasury Bill Rate (USA).

Visit the TB3MS ticker website and fetch the risk-free rate. This is 4.26 at the time of writing this Excel tutorial. Convert this to a decimal value, which is 0.0426.

You don’t compulsorily have to consider TB3MS as the benchmark for a risk-free rate. You can choose any other government or sovereign bonds that tend to come with the lowest or virtually zero risk.

You can consider any government-issued bonds that are relevant to your geographic location. For example, T-Bills in the US and UK T-Bills for the UK, and so on.

Calculate the Sharpe Ratio

Now, you’re free to calculate the Sharpe index using the derived values you created so far from the source data.

Select a cell where you wish to calculate the Sharpe Ratio and enter the following formula:

=(Average of return-Risk free rate)/Std Dev of returns

According to the exercise data table, here’s the actual formula:

=(E4-E6)/E5

Enter this formula in a cell of your choice. Ensure you adjust the cell references according to your own data set.

Hit Enter to calculate the Sharpe Ratio.

Congratulations! You’ve successfully calculated the Sharpe index in Excel using Mean, Std Dev, and risk-free rate for your investment returns for 12 months.

Calculating Annualized Sharpe Ratio

Calculating the annualized Sharpe Ratio in Excel is a widely used method in finance and analytics to assess the return of an investment relative to its risk. This method takes your periodic returns, usually monthly, and adjusts the Sharpe index so it reflects a full year’s perspective.

The main advantage of annualizing the Sharpe measure is that it gives context. Monthly or weekly Sharpe Ratios don’t always reveal how consistent an investment is over the long term, especially if returns are volatile.

Annualizing helps you evaluate performance with a time-adjusted lens, which is how most professionals, including fund managers and analysts, report performance.

Start by entering your monthly return percentages into a single Excel column. Ideally, use 12 data points for one year of returns to keep it simple. Refer to the example shown in the screenshot above.

In a nearby cell, use the following formula to calculate the mean monthly return, which will serve as your expected return per period.

=AVERAGE(B5:B16)

Adjust the cell references of your formula according to your own dataset. Hit Enter to calculate the cell.

In the adjacent cell, enter the decimal value of the average percentage return, which is 0.11 for 11%.

In another cell, calculate the standard deviation of your monthly returns using the given formula below. This gives you a measure of how much the returns fluctuate month to month.

=STDEV.S(B5:B16)

Don’t forget to change the cell range reference of the formula according to your own dataset.

Press Enter on the keyboard to get the Std Dev value.

Get the current annualized risk-free rate (such as the 3-month US T-Bill rate) from FRED.

Then, convert it to a monthly rate using the following formula:

=(1 + D6)^(1/12) - 1

You only need to change the cell reference D2, according to your own worksheet, where you’ve saved the risk-free rate.

Now, calculate the monthly Sharpe Ratio by subtracting the monthly risk-free rate from the average monthly return, then dividing the result by the standard deviation you calculated earlier.

Here’s the formula you can use:

=(E4-E6)/D5

In this formula, E4 is the mean return, E6 is the monthly risk-free rate, and D5 is the Std Dev of the returns.

Press Enter on the keyboard to calculate the monthly Sharpe Ratio.

To annualize the Sharpe Ratio, multiply the monthly it by the square root of 12. To accomplish this, use this formula:

=D7 * SQRT(12)

In the formula given above, D7 is the monthly Sharpe Ratio. Change the cell reference to a different cell where you’ve put the monthly Sharpe Ratio value in your worksheet.

Hit Enter to calculate the cell.

This is how you scale the ratio up to reflect a full year of risk-adjusted performance.

Using Excel’s DATA ANALYSIS Toolpak

The Data Analysis ToolPak is a built-in statistical analysis tool for all sorts of data in Excel. So, you can also use this tool to calculate the required inputs of the Sharpe Ratio, like the mean of returns and the standard deviation of returns.

Data Analysis ToolPak isn’t active by default. You must enable it from the Developer tab in the Excel ribbon menu.

Click on the Excel Add-ins command button inside the Add-ins block to open the Add-ins dialog box.

There, checkmark the checkbox for Analysis ToolPak – VBA and click OK to save the changes you made.

Now, go to the Data > Analysis block to find the Data Analysis command. Click on that.

The Data Analysis dialog box will open. Find and select the Descriptive Statistics function from the list and click OK to open a new configuration dialog box.

On the Descriptive Statistics dialog box, click on the Input Range field and select the cell range where you’ve saved the monthly returns of a mutual fund.

Now, click on the Output Range field and select a cell on the active worksheet. This is the cell where the statistical analysis will be saved as a table.

Click OK to create the descriptive statistical analysis for the source data.

Now, get the 3-Month Treasury Bill Rate (USA) from FRED and use it as the risk-free rate, which is 4.26% or 0.0426 in decimal values. The risk-free rate changes regularly. So, get the most updated value when you’re practicing this method.

So, you now have all the components you need to calculate the Sharpe Ratio. Use the following formula:

Sharpe Ratio = (Average returns - Risk-free rate)/ Std Dev of returns

For example, if the Risk-free rate is in G6, the Mean is in E6, and the Standard Deviation is in 0.01880925, the Sharpe Ratio formula will be as outlined below:

=(E6-G6)/E10

When you’re using this formula, you’ll have to change the cell references according to your own worksheet.

Press Enter to calculate the Sharpe Ratio.

VBA Macro for Sharpe Ratio Calculation

If you wish to automate the Sharpe Ratio calculation process, you can use Excel VBA. It allows you to create a macro. Then, you can run the macro whenever you need the Sharpe index for a range of returns.

If you’re building tools for users who aren’t comfortable writing formulas or selecting cells manually, a VBA-driven process with dialog boxes makes it easy and error-proof.

Also, if you’re recalculating Sharpe Ratios regularly across multiple sheets, workbooks, or datasets (e.g., for clients or portfolio reports), VBA can save time by automating everything.

Since the process involves creating a macro from a VBA script, learn the steps from this quick guide:

Now that you know how to create a macro, use this VBA script to set up your own macro:

Sub CalculateSharpeRatio()

    Dim returnRange As Range
    Dim riskFreeRate As Double
    Dim outputCell As Range
    Dim avgReturn As Double
    Dim stdDev As Double
    Dim sharpeRatio As Double
    
    ' Select return value range
    On Error Resume Next
    Set returnRange = Application.InputBox("Select the range of return values (as decimal, e.g., 0.03 for 3%)", "Select Return Range", Type:=8)
    If returnRange Is Nothing Then
        MsgBox "Operation cancelled.", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0

    ' Enter risk-free rate
    riskFreeRate = Application.InputBox("Enter the risk-free rate as a decimal (e.g., 0.0426 for 4.26%)", "Enter Risk-Free Rate", Type:=1)
    If riskFreeRate = 0 And Str(riskFreeRate) = "" Then
        MsgBox "Risk-free rate not entered. Operation cancelled.", vbExclamation
        Exit Sub
    End If

    ' Select output cell
    On Error Resume Next
    Set outputCell = Application.InputBox("Select the cell where the Sharpe Ratio should appear", "Select Output Cell", Type:=8)
    If outputCell Is Nothing Then
        MsgBox "Output cell not selected. Operation cancelled.", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0

    ' Calculate average return and standard deviation
    avgReturn = Application.WorksheetFunction.Average(returnRange)
    stdDev = Application.WorksheetFunction.StDev_S(returnRange)

    ' Avoid division by zero
    If stdDev = 0 Then
        MsgBox "Standard deviation is zero. Cannot calculate Sharpe Ratio.", vbCritical
        Exit Sub
    End If

    ' Calculate Sharpe Ratio
    sharpeRatio = (avgReturn - riskFreeRate) / stdDev

    ' Output result
    outputCell.Value = sharpeRatio
    outputCell.NumberFormat = "0.0000"
    MsgBox "Sharpe Ratio calculated and placed in " & outputCell.Address, vbInformation

End Sub

When the macro is ready, press Alt + F8 to launch the Macro dialog box.

Select the CalculateSharpeRatio macro and hit Run to execute the macro.

A dialog box will ask you to select the cell range that contains the returns from an investment account.

Another input box will ask you to enter the risk-free rate in its decimal form. For example, if the risk-free rate is 4.26%, type 0.0426.

Finally, select a cell where you want the output using the dedicated input box.

That’s it! The VBA macro should have calculated the Sharpe Ratio for you.

Conclusions

Calculating the Sharpe Ratio to measure investment performance shouldn’t be a complicated process if you use the methods presented in this Excel guide.

Did the Excel tutorial help you learn a new skill to pick investments smartly? Share an acknowledgment or feedback in the comment box.

Also, share this Excel guide with your colleagues and friends to help them pick the best investment with functional analysis.