| Index | Recent Threads | Who's Online | User List | Search |
|
|
 |
Portfolio123 » List all forums » Forum: Simulations and Portfolios » Thread: Trying to replicate Sharpe in Excel |
|
Total posts in this thread: 15
|
[Request a Feature]
[Post new Thread] [Add To My Favorites] [Watch this Thread] |
| Author |
|
|
btiedt1
Advanced Member
|
I've downloaded my monthly and weekly returns and tried to replicate the sharpe that the summary page shows, but can't seem to do it. I've taken my montly returns, found the average. Then annualized it (*12). I've foudn the std. dev. of monthly returns, and multiplied by sqrt 12. Then I calculated the excess return by taking the annualized return minus rfr(2.71) and divided this by the annualized s.d. of returns. my rfr is the 3 year average of 90d tbill. My sharpe calc is off by about 0.40, so even if my rfr is off a little, it wouldn't account for the difference. Any ideas? Thanks. |
||
|
|
btiedt1
Advanced Member
|
I am attempting to replicate the sharpe for one of my long portfolios in P123 to verify that I am calculating it correctly in excel. I want to be able to correctly calculate the sharpe, etc. for a short portfolio I have created. Does anyone have any ideas on why I may be off? Thanks! |
||
|
|
marco
|
Looking into it. It looks like weekly prices are used, not monthly. This might have been done because we wanted to have more # of samples for portfolios, so that a sharpe ratio could be meaningful with only 6 months of data. I'll have a definite answer soon. ---------------------------------------- Portfolio123 Staff |
||
|
|
marco
|
We use daily price changes to calculate statistics, which are then annualized. Stats are not calculated if the port/sim has less than 6 months of data, which is probably the reason why we went with daily. However I think even 6 data points is enough to calculate meaningful stats. Does anybody have any objections about switching the stats to use monthly sampling? It will make our sharpe ratios more readily comparable with things like mutual funds, and etfs. ---------------------------------------- Portfolio123 Staff |
||
|
|
Sterling
Advanced Member
|
I don't compare those ratios with those of mutual funds and ETFs, but from what I know basic statistics textbooks usually say that a sample size should be at least 30 when dealing with a normal distribution so I'm iffy on the wisdom of such a change. To this statistics layman's ears it sounds like it's going to become less accurate. |
||
|
|
btiedt1
Advanced Member
|
Ideally we would be able to have "short sell" portfolios that calculate the Sharpe, annual returns, drawdown, etc, in P1,2,3 then I wouldn't have to attempt to re-create the statistics in excel. Maybe I missed it somewhere, but what is the status of this? Thanks! |
||
|
|
marco
|
UPDATE We're reviewing the calculations. We'll post a detailed explanation soon and an Excel spreadsheet with an example. ---------------------------------------- Portfolio123 Staff |
||
|
|
marco
|
Attached please find an Excel spreadsheet with the calculations for the risk statistics. This is an updated version of the algorithms that has not been released yet. We've found some problems during the review of the current algorithms. We've made the necessary changes to calculate stats as in the spreadsheet and plan to release them next week. As mentioned before we use the daily changes for the statistics. One of the problems we encountered was in converting the risk-free rate of return to a daily return. Since we have monthly data for the 13-week t-bill (Yahoo ticker ^IRX) we had to use the same value for each day of the month. Please let us know if you have any comments about the calculations. For this example we used the GARP 500K model. 10-13-06 ATTACHMENT REMOVED. PLEASE SEE FOLLOW UP POST ---------------------------------------- Portfolio123 Staff ---------------------------------------- [Edit 1 times, last edit by portfolio123 at Oct 13, 2006 4:02:37 PM] |
||
|
|
stoctoni
Advanced Member
|
How did you come up with the risk-free rate of .02162 in cell E1393? |
||
|
|
stoctoni
Advanced Member
|
I don't think the beta is calculated correctly. I don't think the beta takes unsystematic risk vs. systematic risk into account, thus t-bill as risk-free-rate does not factor into the beta calculation. Thus the portfolio return is regressed against benchmark return with the portfolio return representing the dependent variables (Ys) and benchmark return representing the independent variables (Xs). Of course, it's been a while since I took my financial statistics class... I could be missing something. |
||
|
|
[Show Thread Printable Version] [Post new Thread] |
