sum from n=1 to 100 statistics ---- Sharpe Ratio

J

Jon

I'm trying to figure out how to enter a summation into Excel to calculate the
Sharpe Ratio for an investment portfolio.

The first part of the calculation is:

Ri = Return for period I
Mr = Mean of Return set R

Mr = (sum from I=1-N of Ri) / N

Any help?

Thanks,

Jon
 
B

Bernard Liengme

If the Ri values are in A1:A20, then compute the average with
=AVERAGE(A1:A20).
Or did I miss something?
Bernard
 
J

Jon

Hi Bernard,

That would work for the first part of the Sharpe Ratio, if it were for a
static set of data. I'm trying to work on a single cell equation that will
accomplish the following:

Where Mr = Mean of Return set R
Ri = Return for Period I
SD = Period Standard Deviation
Rrf - Period Risk Free Return

1) automatically update as data for new periods becomes available ---
2) Mr = average return of all periods (i.e. sum from I=1-N of Ri / N)
3) SD = (sum from I=1 to N (Ri - Mr)^2 / (N-1)) ^ 1/2
4) Sharpe Ratio = (Mr - Rrf) / SD

So the single cell equation would really be along the lines of: "(average
return of all periods - risk free return) / ((sum from I=1 to N (Ri - Mr)^2 /
(N-1)) ^ 1/2)

Thanks,

Jon
 
T

Tushar Mehta

If your returns are in col. A starting with A2 (and A1 is a heading of
some sorts), define the name (Insert | Name > Define...)
Returns =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Then, all you need is the formula =(AVERAGE(Returns)-B1)/STDEV(Returns)
where B1 contains the risk free return.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
R

Ron Rosenfeld

So the single cell equation would really be along the lines of: "(average
return of all periods - risk free return) / ((sum from I=1 to N (Ri - Mr)^2 /
(N-1)) ^ 1/2)
Click to expand...

This is how I understood Sharpe to have expressed his ratio:

=AVERAGE(Total_return - risk_free_return) /
STDEV(Total_return - risk_free_return)

Although, in Excel, I would have thought to use STDEVP instead of STDEV.

Both AVERAGE and STDEV or STDEVP ignore blank cells (as well as text or logical
values).

So I set up a table where I enter the data, and have the AVERAGE and STDEVP
formulas refer to a large enough range.

Something like:

Risk_Free Total Total -
Return Return Risk_Free


If the above are in columns A:C and start in Row 3, you could compute the
Sharpe Ratio with the formula:

=AVERAGE(C3:C100)/STDEVP(C3:C100)

or however many rows/periods you require. As you fill in each row with new
data, the formula will continue to compute accurately.




--ron
 
D

Dana DeLouis

I might be wrong, but I got the same answer from an example by using the
following Array formula.
Rp was a column of portfolio returns, and Rf was a column of Risk free
returns.

=AVERAGE(rp-rf)/STDEV(rp-rf)

{Ctlr+Shift+Enter}

HTH
Dana DeLouis
 
R

Ron Rosenfeld

I might be wrong, but I got the same answer from an example by using the
following Array formula.
Rp was a column of portfolio returns, and Rf was a column of Risk free
returns.

=AVERAGE(rp-rf)/STDEV(rp-rf)

{Ctlr+Shift+Enter}
Click to expand...

That works, too.


--ron
 
Top