Portfolio analysis and optimizationPortfolio Management

Sortino Ratio Of Portfolio (With Marketxls Formulas)

Written by admin
Wed Jun 21 2017
Sortino Ratio in Excel
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Sortino Ratio in Excel
 

We know that Sharpe ratio is one of the most common ratios to measure the reward versus the risk of an investment opportunity. Sortino ratio is a modified version of the Sharpe ratio. While the Sharpe ratio measures the return over the overall risk (stock volatility) in the portfolio, Sortino ratio only considers the downside risk in the portfolio. In this article, we will learn about what Sortino ratio is, how MarketXLS calculates it, and how to calculate the same in Excel using MarketXLS functions.

Contents:

  1. What is Sortino Ratio?
  2. Using Sortino Ratio
  3. How MarketXLS Calculates Sortino Ratio
  4. Sortino Ratio in Excel

What is the Sortino Ratio?

Sortino ratio is a modified version of the Sharpe Ratio. It is named after Dr. Frank Sortino from the Pension Research Institute. A higher Sharpe ratio signifies relatively less risk. In other words, even if a portfolio’s performance is average and the risk is low, the Sharpe ratio will become large. This is, however, not enough because investors don’t just want to look at the average performance of a portfolio. And this is where the Sortino ratio differs from the Sharpe ratio. Instead of using standard deviation as the denominator, it uses only downside deviation.

Sortino ratio measures excess return to the risk of not meeting an investor’s MAR.

Where:

Minimum Acceptable Return refers to a rate determined by the investor which may be an absolute return, an index return, a risk-free rate, or even zero.

The important thing to note here is that Sortino ratio quantifies downside volatility without penalizing upside volatility thereby addressing the shortcomings of Sharpe ratio.

Using Sortino Ratio

Sortino ratio is suitable as a relative measure to compare the performance of portfolios, one fund with another, or to compare a fund with a benchmark index. A higher ratio indicates better risk-adjusted-performance. It indicates a low risk of incurring large losses (because it considers only downside volatility).

Let’s take an example to understand this. Let’s say you are considering an investment in two funds A and B. Fund A has a return of 10% in the first year and -10% in the second year. Fund B has a 0% return in the first year and a 20% return in the second year.

For both Funds A and B, the total variance is the same, i.e., 20%. However, Fund B is definitely a better investment. Sharpe ratio will not differentiate between the two investments. However, the Sortino ratio will spot the negative volatility in Fund A and will be able to identify Fund B as a better investment.

How MarketXLS Calculates Sortino Ratio of Portfolio

MarketXLS® completely automates the process of calculating the Sortino ratio and returns an annualized value to facilitate maximum comparability between different portfolios.

For either the ex-ante or ex-post, the following inputs are required:

Required Fields:

  1. Symbol of the asset:
    Enter the ticker under which the security in your portfolio is traded.
  2. Weight of the asset in your portfolio:
    Enter the percentage weight of each security as shown below.

Example:

 

Optional Fields:

  1. Minimum acceptable return(Mar)/ risk-free-rate(Rf):
    To account for individuality in investor outlook, we make sure to give you the flexibility of determining what Mar/Rf you think is most viable. Default value: marketXLS assumes a Mar/Rf of 0% by default to calculate the ratio.
  2. Months under observation:
    To determine how many months you want to utilize retrospectively to arrive at your calculation, you may input the number of months you feel best, given that all the securities in your portfolio have existed long enough to have data for those periods.  Default Value: In the absence of any user input, marketXLS uses the data for the preceding 12 months from the date of query.

Calculation:

The following is how the MarketXLS add-in calculates the Sortino ratio of portfolio, one may note, that this also serves as an explanatory step-by-step guide as to how to calculate the ratio.

1. Weighted Monthly Return Calculation:

Monthly return is calculated as follows:
Where:

Note: to maintain consistency with theory in this regard, Close Price is a security’s Adjusted Close of the last day of a given month and the Open Price is the Adjusted Close price for the last day of the previous month.

The values so received are multiplied by the respective weights of each security in the portfolio, and their sum is the weighted return for that month.

This process is repeated for all the months that are to be taken into consideration.

2. Average Monthly Return Calculation:

The average portfolio monthly return is calculated by taking the mean of values arrived at in step 1.

3. Standard Deviation of Downside calculation:

The portfolio downside values are calculated as follows:

  1. MAR is subtracted from individual values of the monthly returns
  2. The value of the downside is either 0 or the value attained in the step above, whichever is lower.

This is repeated for the data for each month.

The downside deviation is calculated using the following formula for the Monthly Downside as calculated above :

4. Calculation of Sortino Ratio:

Consequently, all necessary components to calculate Sharpe ratio are available we can simply calculate the Sharpe ratio as follows:

Note: Since the calculation is based on monthly data, a monthly estimation of the risk-free-rate should be used.

5. Annualising the Resultant Ratio:

The Sortino ratio is annualized by multiplying the value attained in Step 4 with the square root of 12. This number is used because monthly data is utilized for the given stocks.

Sortino Ratio in Excel

We have created a template to calculate the Sortino Ratio in Excel.  Especially relevant is that we can calculate the Sortino ratio in Excel using MarketXLS functions. In the template, where you can enter your stock portfolio and it will automatically calculate the Sortino Ratio of the portfolio. Also, the template is flexible and we can extend it to calculate the Sortino ratio of any portfolio.

Let us review the steps involved in calculating the Sharpe Ratio of Portfolio in Excel.

1. Get Daily Stock Prices

Get daily stock prices for the last one year for each stock in your portfolio. To do this, simply add the stock symbols in Excel, select the cells containing the symbols, and then press the ‘1 Year Data’ button in the MarketXLS panel.

2. Calculate Daily Returns

Calculate daily returns for the period using the following formula:

Periodic Return = (Price 1 – Price 0) / Price 0

Note: Here, Price 1 is the adjusted close of the last day of the present period, and price 0 is the adjusted close of the last day of the previous period. The period could be days, weeks, months, or even years.

3. Calculate Excess Returns and Excess Negative Returns

Using the periodic returns and MAR, you can calculate the excess returns and also the negative excess returns.

4. Calculate Average Annualized Excess Returns and Downside Volatility

We can now calculate the annualized excess returns and also the downside volatility using the negative excess returns.

5. Calculate Sortino Ratio

Finally, you can calculate the Sortino ratio using the formula provided above.

6. Annualise Ratio

Finally, to facilitate comparison among different portfolios, annualize the Sortino ratio by multiplying it with the annualizing factor as follows:

Annualizing Factor = SQRT(Number of Periods a Year)

For instance, if taking daily data, seeing as a trading year has 252 days, multiply the ratio with the square root of 252 to arrive at the annualized value of the ratio.

Similarly,

        • for yearly returns, annualizing factor = SQRT(1)
        • for monthly returns, annualizing factor = SQRT(12)
        • for weekly returns, annualizing factor = SQRT(52)
        • for daily returns, annualizing factor = SQRT(252)

​MarketXLS has a function called the Sortino Ratio. You can simply use this function to calculate the Sortino ratio for stocks.
=SortinoRatio(“Stock Symbol”, Optional [“Period”], Optional [“Risk Free Rate”])

This function will report the Sortino Ratio of the stock within the time period specified. The default period is twelve months and the default risk-free rate is zero.

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates

Top 100 Gainers Today

Top 100 losers Today

Real gdpReal personal consumption expReal private investmentReal govt expenditureReal net exportsReal exportsReal importsFederal receiptsFederal outlaysFederal surplus or deficitFederal debtReal private investment nonresidentialReal private investment residentialReal potential gdpReal personal incomeReal personal consumption exp monthlyRpce durable goodsRpce nondurable goodsRpce servicesPersonal savings rateMonetary baseCurrency in circulationBank reservesMoney supply m1Money supply m2Sp500DjiaWilshire indexVixFinancial stress indexCorporate bond index aaCorporate bond index bbbFederal funds rateTreasury rate 3mTreasury rate 1yTreasury rate 5yTreasury rate 10yTips 5yTips 10yBond yield aaaBond yield baaMortgage rate 15yMortgage rate 30yUs dollar weighted averageUsdollar to euroUsdollar to poundYuan to usdollarCanadiandollar to usdollarYen to usdollarCpiCpi wo food energyCpi foodCpi energyChain price indexChain price index wo food energyGdp price deflatorPpi final demandPpi finished goodsPpi materialPpi crude goodsPpi final demand wo food energyPpi finished goods wo food energyHouse price indexHouse price index 20cityCrude oil priceGasoline priceNatural gas priceIndustrial productionCapacity utilisationInventoriesSales retail foodVehicle sales light weightManufacture orders durablesManufacture orders capital goodsLoansConsumer credit outstandingCorporate profitsHousing startsBuilding permitsResidential constructionEmployees nonfarmEmployees privateEmployees goods producingEmployees service providingEmployees governmentUnemployment rateInitial cliamsAverage weeks unemployedJob openingsHiresSeparationsQuitsLayoffs dischargesHours of productionHourly earningsReal outputPopulationLabor forceLabor force participation rate
Search for a stock

Top MarketXLS Rank stocks

CRA InternationalInc. logo

CRA InternationalInc.

89.32
 
USD
 
1.50
 
(1.71%)
81
Rank
Optionable: Yes
Market Cap: 646 M
Industry: Business Services
52 week range    
78.05   
   115.47
Booz Allen Hamilton Holding Corporation logo

Booz Allen Hamilton Holding Corporation

90.36
 
USD
 
1.83
 
(2.07%)
81
Rank
Optionable: Yes
Market Cap: 11,592 M
Industry: Business Services
52 week range    
69.32   
   90.99
Gaming and Leisure Properties Inc. logo

Gaming and Leisure Properties Inc.

45.86
 
USD
 
-0.26
 
(-0.56%)
79
Rank
Optionable: Yes
Market Cap: 11,555 M
Industry: REIT - Diversified
52 week range    
40.57   
   48.57
General Mills Inc. logo

General Mills Inc.

75.45
 
USD
 
0.73
 
(0.98%)
79
Rank
Optionable: Yes
Market Cap: 42,311 M
Industry: Packaged Foods
52 week range    
55.38   
   75.00
Sanderson Farms Inc. logo

Sanderson Farms Inc.

215.53
 
USD
 
-3.08
 
(-1.41%)
78
Rank
Optionable: Yes
Market Cap: 4,908 M
Industry: Packaged Foods
52 week range    
175.42   
   221.63
Ituran Location and Control Ltd. logo

Ituran Location and Control Ltd.

24.49
 
USD
 
0.01
 
(0.04%)
77
Rank
Optionable: Yes
Market Cap: 504 M
Industry: Communication Equipment
52 week range    
19.61   
   29.50
Republic Services Inc. logo

Republic Services Inc.

130.87
 
USD
 
1.12
 
(0.86%)
76
Rank
Optionable: Yes
Market Cap: 40,406 M
Industry: Waste Management
52 week range    
109.24   
   145.00
ConAgra Brands Inc. logo

ConAgra Brands Inc.

34.24
 
USD
 
-0.09
 
(-0.26%)
76
Rank
Optionable: Yes
Market Cap: 16,287 M
Industry: Packaged Foods
52 week range    
29.80   
   36.67
Penske Automotive Group Inc. logo

Penske Automotive Group Inc.

104.69
 
USD
 
-5.36
 
(-4.87%)
75
Rank
Optionable: Yes
Market Cap: 8,406 M
Industry: Auto & Truck Dealerships
52 week range    
71.77   
   123.60
Franklin Covey Company logo

Franklin Covey Company

46.18
 
USD
 
8.15
 
(21.43%)
75
Rank
Optionable: Yes
Market Cap: 557 M
Industry: Education & Training Services
52 week range    
33.41   
   52.52

More Features

Stand with Ukraine

As the situation in Ukraine escalates, many of us in MarketXLS are left with emotions too overwhelming to name. If you’d like to show your support, but aren’t sure how to, we want to help make it easier for you to act.

For any amount donated, we’ll extend your MarketXLS subscription for double of the donated amount. Please send proof of your payment to support@marketxls.com to avail the extention

From all of us at MarketXLS, thank you!