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.

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

What Is Sortino Ratio?

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.

2 Methods to Calculate Sortino Ratio in Excel

1. Calculate 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 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 Do Ratio Analysis in Excel Sheet Format (with Quick Steps)

2. Excel VBA to Calculate Sortino Ratio

Now I will calculate the Sortino ratio using 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 in Excel (4 Handy Methods)

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.
• Sortino ratio is really helpful to retail investors as it helps them to make the right decision about any investment.

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

• Calculate Ratio of 3 Numbers in Excel (3 Quick Methods)
• How to Calculate Ratio Between Two Numbers in Excel (5 Ways)
• Calculate Compa Ratio in Excel (3 Suitable Examples)
• How to Calculate Average Ratio in Excel (2 Simple Ways)
• Use Interest Coverage Ratio Formula in Excel (2 Easy Methods)
• How to Calculate Sharpe Ratio in Excel (2 Common Cases)
• Debt Service Coverage Ratio Formula in Excel