Portfolio Performance Excel File
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)
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
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.
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”.
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 
should’t you be using the stdev of excess returns when calculatng the sharpe ratio instead of using the stdev of portfolio returns…
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.
If I may, in your Excel sheet you are computing the Sharpe ratio using the mean and standard deviation of the S&P500 or Portfolio raw returns, while these have to be calculated from the excess returns instead (cf. http://www.stanford.edu/~wfsharpe/art/sr/SR.htm). That is, from each monthly raw return, you should deduct the compounded RF rate (0.43 in your sheet).
Thank you very much for the spreadsheet.
I think there is a minor mistake in the calculation of the coefficient of determination. In column Y, the formulas should be
=C…*$G$18+$G$19
instead of
=D…*$G$18+$G$19.
Hence, the coefficient of determination turns out to be 0.01 instead of 0.14, which you can verify by running a regression through “Data Analysis”.
Regards,
hi, ppl i have a course of portfolio management, and i am trying to calculate the returns of my portfolio, i would like to ask 2 questions, we were given 1million to our portfolio, but i only spent around 990,000 so should my balance of portfolio star with 990,000 or 1 million and seconly if i want to calculate my returns monthly, how should i do it, the difference between each month close price and the bought price of the first month, or the difference between the opening price and the closing price of each month, thanks
You also need to multiply your standard deviation figures by the square root of 12 in order to annualize the figure from monthly returns. For example, C16 should read:
=STDEV(C3:C14)*sqrt(12)
Appreciate the many comments here. My apologies for not updating the sheet as I’ve been busy with many changes in my life since the days when I was writing in this blog more full time. If anyone has updated the excel file and would like to share with the rest of the community, I’d be happy to post it!
Here’s an Excel spreadsheet that demonstrates how you can find the investment weights for a portfolio with the maximum Sharpe Ratio – otherwise known as the Sharpe Optimal Portfolio: http://optimizeyourportfolio.blogspot.com/2011/05/sharpe-optimal-portfolio-with-excel.html
Sorry Dan. Valiant effort, but these ratios aren’t correct. In fact, they aren’t even in the ballpark. For instance, the Sharpe Ratio is calculated using the ANNUALIZED returns and st. deviations. Thus, the formula in G8 should read;
=((C15*12)-$G$2)/(C16*(SQRT(12)))
There’s an Excel spreadsheet to calculate the investment weights in Sharpe Optimal Portfolio at http://investexcel.net/216/calculating-a-sharpe-optimal-portfolio-with-excel/
thank you