Calculating Sortino Ration (Downside Deviation)

T

TimH

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?
 
T

TimH

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

Glenn

TimH said:
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.
Click to expand...

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

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

Moti wig

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
 
T

tyrian

Joined
Jun 28, 2011
Messages
8
Reaction score
0
There's a spreadsheet to calculate the Sortino Ratio here.

Moti wig said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Downside deviation and Semi-Deviation 2
Calculating STDEV and other Calcs based on another cells value 3
Gain / Loss Standard Deviation 1
Calculating Conditional Standard Deviation? 5
Downside Deviation 1
Standard deviation calculation error... 7
Loss Deviation 1
Finding minimum variance with excel 1

Top