Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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?

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default 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,""))


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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.


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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.


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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.



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Average & Deviation ross27 Excel Worksheet Functions 1 January 15th 08 03:43 AM
Calculating Conditional Standard Deviation? Harimau Excel Worksheet Functions 5 June 27th 07 01:40 PM
Calculating Std Deviation based on a condition Hari Excel Discussion (Misc queries) 0 June 22nd 06 12:58 PM
Calculating deviation from mean and % change for varying years smurray444 Excel Discussion (Misc queries) 1 February 10th 06 03:05 PM
Histrogramms: Calculating average and standard deviation Jens Eichelbaum Excel Worksheet Functions 2 November 23rd 04 03:10 AM


All times are GMT +1. The time now is 09:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"