Portfolio Performance Excel File

Posted by Dan Hung
November 13, 2007

Just got a comment on my last post during the “Portfolio Performance Metrics” series. I realized that I never did get the final post with a portfolio performance Excel file online. Here it is! Here’s an Excel file which allows you to calculate Sortino and Sharpe Ratios as well as Beta and Jensen’s Alpha and correlation between a standardized index and a portfolio. I’ve set it up for the S&P 500, but the last few months are just filled in as 2.00%. Take a look and let me know if I can improve it.

1 Year Portfolio Performance Metrics (right click -> save as)

More on this topic (What's this?)
From SPX 666 to RUT 666: where will markets take us next (CHARTLINES (tm), 3/5/10)
ROSENBERG: THE MARKET LOOKS TOPPY (THE PRAGMATIC CAPITALIST, 3/3/10)
The Dividend Stock Recovery: Get Ready for a High-Yield Bonanza (Investment U, 2/22/10)
Read more on S&P 500 (SPX), Beta at Wikinvest
Portfolio Management, Tutorials

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments
Comment by Morad on December 9, 2007 @ 5:43 am

thank you

Comment by Russell on February 12, 2008 @ 5:59 am

Nice spreadsheet
Is there any info about the kind of benchmarks one is looking for in terms of “good” and “bad” ratios. Terminology is awful but I hope you get the gist.

Comment by Dan Hung on February 12, 2008 @ 2:14 pm

Hi there, I’m not sure exactly which ratios you’re referring to. But, I’m assuming its the portfolio performance ratios I’ve included in this sheet. If you check out the link I have for “Portfolio Performance Metrics” in the above post, you’ll find some posts on different performance ratios and I think that will help you get an idea of what you’re looking for as far as “good” or “bad”.

Comment by Bruce on April 10, 2008 @ 12:09 am

Hi Dan, thx for the ss. was interested in the sortino ratio calcs.

Do you do any simulation of future performance to guide position sizing?

It seems many books, bloggers, and gurus are oblivious to the pitfalls of using excel stdev and mean for this purpose, as they are normal distro formulae.

I currently run my own Monte’s in excel using a lognormal freq dist random number generator.
formula is
=LOGINV(RAND(),mu,sigma)

However, based on readings of Taleb and Mandelbrot, I presume we would all be better off applying power-law FDs, or better still, parabolic fractal FDs.

any chance of a spreadsheet for that ;)

Comment by Nic on January 13, 2009 @ 7:11 am

should’t you be using the stdev of excess returns when calculatng the sharpe ratio instead of using the stdev of portfolio returns…

Comment by Dan Hung on January 13, 2009 @ 10:16 am

I believe that if you assume a constant target return (and in my case this is the risk free rate) then the SD of excess returns is actually the same as the SD of portfolio returns.

Comment by Farrukh Siraj on December 8, 2009 @ 3:09 am

Really nice work…. its really helpfull for me and i appreciate you buddy.

Leave a comment

(required)

(required)