How to Calculate Sortino Ratio in Excel (2 Methods)

In this article, I will discuss how you can calculate the Sortino Ratio in Excel. I will calculate this using Excel formulas and VBA. Before starting the calculation, let’s have a few words about the Sortino Ratio.

What Is Sortino Ratio?

The Sortino ratio is a risk-adjustment metric used to calculate the return from a certain investment for a given level of negative risk. This ratio is used in Statistics mostly and was introduced by Frank A. Sortino & Price in 1994. The Sortino ratio is a refined version of the more widely known Sharpe ratio. In the portfolio theory, the Sharpe ratio considers total price deviation in the calculation. However, the Sortino ratio considers a downside deviation while calculating.

The basic formula to calculate the Sortino ratio is:

Where,

• R_p is the portfolio’s Expected Return or Actual Return.
• R_f is the portfolio’s Minimum Acceptable Return (MAR)/Risk-free rate of return.
• σd is the Downside Deviation or Standard Deviation of negative asset return.

How to Calculate Sortino Ratio in Excel: 2 Methods

1. Calculate the Sortino Ratio Using Excel Formulas

To calculate the Sortino ratio using the Excel formula, let’s consider the below scenario:

Suppose, we have the average return percentage of a certain company for 6 months. The risk-free return of the portfolio is 7%.

Now I will calculate the Sortino ratio for the company. Follow the below steps to calculate the Sortino ratio for the above data.

Steps:

• Firstly, we will calculate the Excess Return.
• To do that, type the below formula in Cell D5.

• Now press Enter.

• Next, we will get the below result.
• After that, use the Fill Handle (+) tool to get the excess return for the rest of the values.

• As a result, here we get all the Excess Returns for the given values.

• Further, we will separate only Negative Excess Returns using the below formula:

• Then, use the below formula to calculate the square of Negative Excess Returns.

• Consequently, using the SUM function formula, I calculated the total of Excess Returns.
• Similarly, I have calculated the total of the square of Negative Excess Return values using the SUM formula.

• Here comes the next step calculation, let’s calculate the average of the Excess Return using the below formula:

• Afterward, I will calculate the Downside Risk or Downside Standard Deviation.
• The formula to calculate Downside Risk is:

• So, here is our formula to calculate Downward Risk.

• Finally, to calculate the Sortino ratio divide Average Excess Return by Downside Risk.

⏩ Note:

If you want to calculate the Sortino ratio against the Actual Return of a random month, follow the below steps:

• After calculating the Downside Risk following the steps above, subtract the Risk-Free Return from the specific Actual Return.
• For Example, to calculate the Sortino ratio for the Average Return of Cell C7, use the below formula:

• Upon entering the formula here is the final output.
• You can calculate the Sortino ratio for any other Actual Return and thus compare which investment is safe.

Read More: How to Calculate Sharpe Ratio in Excel

2. Excel VBA to Calculate Sortino Ratio

Now I will calculate the Sortino ratio using the VBA User Defined Function (UDF). This method is very simple as you won’t have to use a lot of formulas. Here I will use the same dataset that was used in Method 1.

Follow the below steps to calculate the Sortino ratio by applying VBA.

Steps:

• First, from the Ribbon, go to the Developer tab.
• Next, select Visual Basic.

• Consequently, the VBA window will appear.
• Now, go to the Insert option.
• After that, select a new Module from there.

• Then, type the below code in the newly inserted Module.

Function Ratio_Sortino(returns As Range, MAR As Variant) As Variant
Dim x As Integer
Dim y As Integer
Dim RetAvg As Double
Dim m2 As Double
Dim dev As Double
x = returns.Rows.Count
RetAvg = WorksheetFunction.Average(returns)
moment2d = 0
For y = 1 To x
If returns(y) - MAR < 0 Then
m2 = m2 + ((returns(y) - MAR) ^ 2)
End If
Next
dev = Sqr(m2 / x)
If dev > 0 Then
Ratio_Sortino = (RetAvg - MAR) / dev
Else
Ratio_Sortino = "undefined"
End If
End Function

• Further, type the below formula in Cell C13 and hit Enter.

• As a result, we will get the Sortino ratio for the above data.

Read More: How to Calculate Ratio of 3 Numbers in Excel

Observations from Calculated Sortino Ratio

• From the above methods you can see that by calculating the Sortino ratio for a specific portfolio’s Actual Return, you can check the return on investment.
• By comparing the Sortino ratio of certain actual returns, you can check which investment is safer to pursue.
• The Sortino ratio is really helpful to retail investors as it helps them to make the right decision about any investment.

Download Practice Workbook

Conclusion

In the above article, I have tried to discuss several methods to calculate the Sortino ratio in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

Related Articles

• How to Calculate Average Ratio in Excel
• How to Calculate Male Female Ratio in Excel
• How to Calculate Odds Ratio in Excel
• How to Calculate Compa Ratio in Excel
• How to Calculate Current Ratio in Excel

<< Go Back to Ratio in Excel | Calculate in Excel | Learn Excel