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:

 

=(Rp–Rf)/σd

Where,

  • Rp is the portfolio’s Expected Return or Actual Return.
  • Ris 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%.

Calculate Sortino Ratio Using Excel Formulas

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.
=C5-$C$13
  • Now press Enter.

Calculate Sortino Ratio Using Excel Formulas

  • 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.

Calculate Sortino Ratio Using Excel Formulas

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

Calculate Sortino Ratio Using Excel Formulas

  • Further, we will separate only Negative Excess Returns using the below formula:
=IF(D5<0,D5,0)

Calculate Sortino Ratio Using Excel Formulas

  • Then, use the below formula to calculate the square of Negative Excess Returns.
=E5^2

Calculate Sortino Ratio Using Excel Formulas

  • 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.

Calculate Sortino Ratio Using Excel Formulas

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

Calculate Sortino Ratio Using Excel Formulas

  • Afterward, I will calculate the Downside Risk or Downside Standard Deviation.
  • The formula to calculate Downside Risk is:
 =√(∑(Square of Negative Excess Returns)/No. of months)
  • So, here is our formula to calculate Downward Risk.
=SQRT(F11/6)

Calculate Sortino Ratio Using Excel Formulas

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

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:
=(C7-C13)/C14

  • 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.

Excel VBA to Calculate Sortino Ratio

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.

Excel VBA to Calculate Sortino Ratio

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

Excel VBA to Calculate Sortino Ratio

  • 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

Excel VBA to Calculate Sortino Ratio

  • 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

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo