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:

`=(Rpâ€“Rf)/Ïƒd`

Where,

**R**is the portfolio’s_{p}**Expected Return**or**Actual Return**.**R**is the portfolio’s_{f }**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**.

`=C5-$C$13`

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

`=IF(D5<0,D5,0)`

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

**=E5^2**

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

`=D11/6`

- 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)`

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

`=(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 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**