ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Calculating Sortino Ration (Downside Deviation) (https://www.excelbanter.com/excel-worksheet-functions/200275-calculating-sortino-ration-downside-deviation.html)

TimH
Calculating Sortino Ration (Downside Deviation)
 
I am trying to write a formula to calculate the Sortino Ratio defined as
(Expected (Observed) Return - Minimum Acceptable Return(MAR))/Downside
Deviation. Downside Deviation is the Standard Deviation of those returns
that are < MAR. I am familiar with STDEV function and IF function. Is there
a way to embed the IF function into the STDEV function so that it calculates
the STDEV only on the values in the range below a certain level?

ExcelBanter AI
Answer: Calculating Sortino Ration (Downside Deviation)
 
Yes, you can use the IF function within the STDEV function to calculate the downside deviation.
  1. First, calculate the minimum acceptable return (MAR) and the expected (observed) return for your investment portfolio.
  2. Next, create a range of returns for your investment portfolio. Let's say the range is
    Formula:
    A1:A10 
    .
  3. Use the IF function to create a new range that only includes returns that are less than the MAR. The formula would be:
    Formula:
    =IF(A1<MAR,A1,"") 
  4. Copy the formula down the range to include all the returns in your portfolio.
  5. Now, use the STDEV function to calculate the standard deviation of the new range you just created. The formula would be:
    Formula:
    =STDEV(range) 
  6. Finally, use the Sortino Ratio formula to calculate the ratio. The formula would be:
    Formula:
    =(Expected Return - MAR)/STDEV(range) 

By using the IF function within the STDEV function, you can calculate the downside deviation (standard deviation of returns less than MAR) and use it to calculate the Sortino Ratio.

Teethless mama
Calculating Sortino Ration (Downside Deviation)
 
=IF(STDEV(A1:A5)<your value,STDEV(A1:A5),"")


"TimH" wrote:

I am trying to write a formula to calculate the Sortino Ratio defined as
(Expected (Observed) Return - Minimum Acceptable Return(MAR))/Downside
Deviation. Downside Deviation is the Standard Deviation of those returns
that are < MAR. I am familiar with STDEV function and IF function. Is there
a way to embed the IF function into the STDEV function so that it calculates
the STDEV only on the values in the range below a certain level?

TimH
Calculating Sortino Ration (Downside Deviation)
 
I believe this will return the STDEV if it is below a certain level. What
I'm looking for is to calulate the STDEV of only those values below a certain
level. For example. Let's say the dataset is 6%, 8%, 3%, 2%, 9%,-4%. And
the MAR is 5%. I want to calculate the STDEV(3%, 2%, -4%) that is, only
those values below 5% other values are excluded.

"Teethless mama" wrote:

=IF(STDEV(A1:A5)<your value,STDEV(A1:A5),"")


"TimH" wrote:

I am trying to write a formula to calculate the Sortino Ratio defined as
(Expected (Observed) Return - Minimum Acceptable Return(MAR))/Downside
Deviation. Downside Deviation is the Standard Deviation of those returns
that are < MAR. I am familiar with STDEV function and IF function. Is there
a way to embed the IF function into the STDEV function so that it calculates
the STDEV only on the values in the range below a certain level?

Glenn
Calculating Sortino Ration (Downside Deviation)
 
TimH wrote:
I believe this will return the STDEV if it is below a certain level. What
I'm looking for is to calulate the STDEV of only those values below a certain
level. For example. Let's say the dataset is 6%, 8%, 3%, 2%, 9%,-4%. And
the MAR is 5%. I want to calculate the STDEV(3%, 2%, -4%) that is, only
those values below 5% other values are excluded.


With your data in A1:A6, array-enter the following:

=STDEV(IF(A1:A6<0.05,A1:A6,""))

Max
Calculating Sortino Ration (Downside Deviation)
 
Try, array-entered*:
=STDEV(IF(A1:A10<5%,A1:A10))

*Press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"TimH" wrote:
I believe this will return the STDEV if it is below a certain level. What
I'm looking for is to calulate the STDEV of only those values below a certain
level. For example. Let's say the dataset is 6%, 8%, 3%, 2%, 9%,-4%. And
the MAR is 5%. I want to calculate the STDEV(3%, 2%, -4%) that is, only
those values below 5% other values are excluded.

TimH
Calculating Sortino Ration (Downside Deviation)
 
That works! Thanks a lot.

"Max" wrote:

Try, array-entered*:
=STDEV(IF(A1:A10<5%,A1:A10))

*Press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"TimH" wrote:
I believe this will return the STDEV if it is below a certain level. What
I'm looking for is to calulate the STDEV of only those values below a certain
level. For example. Let's say the dataset is 6%, 8%, 3%, 2%, 9%,-4%. And
the MAR is 5%. I want to calculate the STDEV(3%, 2%, -4%) that is, only
those values below 5% other values are excluded.

Max
Calculating Sortino Ration (Downside Deviation)
 
Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"TimH" wrote in message
...
That works! Thanks a lot.

Moti wig
Calculating Sortino Ration (Downside Deviation)
 
Hi Tim

I look for the full Sortino ratio calculation, i read in.eggheadcafe.com that 2 years ago
you write this formula to EXCEL. i will be very grateful if you can help with the sortino ratio calculation in excel , the MAR that you use ,and what is a "good numbers" as result

Thank you
Moti


On Tuesday, August 26, 2008 11:45 AM Tim wrote:

I am trying to write a formula to calculate the Sortino Ratio defined as
(Expected (Observed) Return - Minimum Acceptable Return(MAR))/Downside
Deviation. Downside Deviation is the Standard Deviation of those returns
that are < MAR. I am familiar with STDEV function and IF function. Is there
a way to embed the IF function into the STDEV function so that it calculates
the STDEV only on the values in the range below a certain level?


On Tuesday, August 26, 2008 12:00 PM Teethlessmam wrote:

=IF(STDEV(A1:A5)<your value,STDEV(A1:A5),"")


"TimH" wrote:


On Tuesday, August 26, 2008 12:07 PM Tim wrote:

I believe this will return the STDEV if it is below a certain level. What
I'm looking for is to calulate the STDEV of only those values below a certain
level. For example. Let's say the dataset is 6%, 8%, 3%, 2%, 9%,-4%. And
the MAR is 5%. I want to calculate the STDEV(3%, 2%, -4%) that is, only
those values below 5% other values are excluded.

"Teethless mama" wrote:


On Tuesday, August 26, 2008 12:16 PM Glenn wrote:

TimH wrote:

With your data in A1:A6, array-enter the following:

=STDEV(IF(A1:A6<0.05,A1:A6,""))


On Tuesday, August 26, 2008 12:23 PM demechani wrote:

Try, array-entered*:
=STDEV(IF(A1:A10<5%,A1:A10))

*Press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"TimH" wrote:


On Tuesday, August 26, 2008 12:48 PM Tim wrote:

That works! Thanks a lot.

"Max" wrote:


On Tuesday, August 26, 2008 11:46 PM Max wrote:

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---


Submitted via EggHeadCafe
ASP.NET- How to Raise Custom Events from a UserControl
http://www.eggheadcafe.com/tutorials...ercontrol.aspx


All times are GMT +1. The time now is 01:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com