The Excel Portfolio Optimization model calculates the optimal capital weightings for portfolios of financial or business investments that maximizes return for the least risk. The design of the model enables it to be applied to either financial instrument or business portfolios. Additional technical analysis indicators perform optimized back testing to establish the technical parameters required to provide the highest total return from signal trading.
The key features of the Portfolio Optimization model include:
Ease and flexibility of input, with embedded help prompts.
Ability to specify the number of units held in each product or business with support for long and short positions.
Specify minimum and maximum constraints per investment for the optimized portfolio.
Limit optimization to downside risk or semi-deviation below a threshold target return under the Sortino Ratio.
Sharpe or Sortino ratio is coupled with analysis of the gain/loss profile under the Omega ratio.
Intuitive graphical result display with Monte Carlo simulation, including probability analysis on specified target return level.
Chart displays the efficient frontier analysis with ability to choose from preset or custom portfolio sets with risk and return profiles matching required investment strategies.
Ability to run a rolling back test of periodic optimizations to evaluate the subsequent value added of optimization at incremental time periods.
Detailed charting and analysis of back tested technical indicators including SMA, ROC, MACD, RSI and Bollinger Bands for maximum return on optimized signal trading strategies.
Compatible with all versions of Excel for Windows as well as Excel for Mac 2011 and 2004 as a cross platform portfolio and investment optimization solution.
Reviewed by Real Estate Research Analyst on Very valuable tool to educate clients and consultants about real estate's role in a diversified portfolio. It does exactly what it is designed to do, without being overly complicated. If you need to run very powerful portfolio models this will not get you there, but for moderately complicated analyses this is a fast and efficient tool.
Reviewed by Haiying on I am currently enrolled in an online course offered by Coursera and taught by Gautam Kaul of the University of Michigan's Ross school of business. I am taking this course in order to learn how to invest a small sum in the stock market. This course entitled "Introduction to Finance" teaches all of what one would need to know about finance except how to actually conduct the calculations that have proven to be of some merit in judging portfolios. As a consequence of being enrolled in this course and now nearly at the end of it, the lectures detailed how to calculate betas and variances while merely outlaying the principles without going into the tedium of the actual calculations. This portfolio optimization program has made these lectures far more understandable. Also the way be which the program downloads financial data from the Yahoo website and then allows one to conduct various calculations in keeping with financial theory is very constructive. In that sense the program is very powerful since one is freed from the pain of having to learn how to write code for Excel to conduct these calculations, the results of which could make you more money. I intend to use this program to evaluate my portfolio and to see how best to modify this in order to maximize return. I highly recommend this program.
Reviewed by Michael Berns on I first came across the Portfolio Optimization spreadsheet in 2008 when I was looking for a simpler solution to optimize my multiple investment portfolios. I was impressed with the functionality and clarity of the analysis. However, I ended up using a different web based solution that had less analytics but was more integrated in terms of the loading of historical data for US and Europe. As part of my MBA program I was looking into portfolio optimization again earlier this year and was very happy to find out that the team at Business Spreadsheets had taken the next step - integrating historical data and even some technical analysis. This is a great and fully integrated product now. I heartily recommend it to other finance and business students as well as private investors.
Reviewed by DJ on I recently downloaded the spreadsheet from Business Spreadsheets after searching the web for an Excel spreadsheet for portfolio optimization. The simplicity of the Portfolio Optimization is incredible, while at the same time producing advanced financial metrics to evaluate every angle of Investments. That makes it a suitable spreadsheet for professional and private users alike. I would recommend this to everyone in search for a deeper understanding of their investments.
Reviewed by Farrokh Emami on Impressive tool, very nice and well-designed dashboard for following and tracking all the essential indicators, results and charts. Easy to use and accurate regarding the real-time data entry and quite reasonable processing time for huge amounts of data.
Reviewed by Dave on A popular method of portfolio design is Asset Allocation. Developing a portfolio based on Asset Allocation requires the answers to three questions: What? How much? And When? The Portfolio Optimization spreadsheet will assist the user to answer the 2nd and 3rd questions using primarily Monte Carlo simulations to try to estimate the optimal combination of portfolio components and their fractions.
To begin, the user must develop a list of candidates for inclusion in the portfolio. One could generate that list based on any criteria desirable--a family of ETFs, a family of stock from various industries, or, a set of investments from different classes--e.g. large cap, small cap, bonds, commodities, real estate, etc. Ideally the list of candidates would not be strongly correlated since the purpose of Asset Allocation is to optimize a portfolio based on the independent behaviors. In any case, the Portfolio Optimizations tool does not directly assist in generating the list of candidates, however, the tool can use up to 100 candidates.
The second thing that is required is a time series of either periodic returns or prices. For dividend paying entities, periodic returns are probably preferable, but otherwise, either will work fine. There is a second spreadsheet included in the package for downloading time series, but I have not used it yet.
On the first tab of the spreadsheet, the various time series (which must have the same number of entries for each entity) are entered into each column, along with (optionally) the date and the name of the candidate. This is straightforward using copy and paste from another spreadsheet, for example.
Once the data entry is complete, the Optimize Portfolio button is pressed and the 4 step process begins. The first step is the construction of the "Correlation Matrix" which determines the degree to which the historical performance of each candidate is correlated with each other candidate. With 15 candidates, this process is complete within a few seconds. Once the Correlation Matrix is complete, the program generates a family of random portfolios. This is an adjustable parameter, but with my set of 15 candidates, it generated 22500 portfolios. The algorithm for doing this is not apparent, but it appears that it's probably choosing 10 randomly chosen fractions between the user-specified minimum and maximum allowed contribution of each candidate and permuting these with all other available combinations.
The third step is the computation of portfolio returns using Monte Carlos simulations, presumably based on the average and standard deviation of the returns of each portfolio candidate. Presumably this is done for all of random portfolios generated in step 2, however, there is no information provided about this step. It is however very quick. The optimized portfolio is chosen from among the population of 22500 random portfolios, apparently based on the distribution of returns generated by the Monte Carlo procedure.
One interesting aspect of the software is that it does not always come up with the same optimal portfolio, even with the same starting parameters. For example, in my 15 candidate system, the first run found an optimum with two bond ETF's, AGG and SHY having fractions of 12.44 and 4.69% respectively. The second run had 13.72 and 17.59% for the same two ETFs. (And, again, the inputs did not change at all.) The third run was 19.48 and 17.4 for these two candidates. Note that all the other candidates' fractions also changed. There are multiple reasons why the simulation may not be reproducible, but that discussion is beyond the scope of the review.
The third question is WHEN to purchase the components of the portfolio, and the spreadsheet offers some technical analysis tools to try to help answer that questions. I've not explored those tools yet.
Overall, the portfolio optimization template seems like a stable platform, but the lack of reproducibility is worrisome. However, since it probably relates to the particular instruments chosen, it may not, in fact be a major concern.
Reviewed by Eric on Worked with the trial version for a few days and decided to buy it. The time needed to build such a template is too much compare to the price asked. The spreadsheets are well suited for my needs. I'm a stockbroker and this Excel spreadsheet is an excellent addition to the tools I have.
Reviewed by Arnie on I find the optimization software to be robust, easy-to-use and highly informative. All of that for an affordable price - what is there not to like?
Reviewed by Mike on I have been using the Portfolio Optimization model for 4 years and have greatly benefited from the modifications made to analyzing risk. Business Spreadsheets has been very responsive and open to suggested enhancements. I look forward to incorporating the new technical analysis features into my business. Great product and great service!
Reviewed by Henry Tata on I have been trying various portfolio optimization tools. This one offered by Business Spreadsheets is the one I love the most. Friendly interface with help functions, easy to use, and fast results. It is definitely a good tool to have for those who are working on their 401k, and some other personal investing, like what I am doing. Thanks.
Reviewed by BenW on I have used this for about a year to make allocation decisions for my 401k. It provides a good framework to make this difficult decision and I've had better results than all of my colleagues (that I know about) who don't have a process. If your company doesn't give you any advice, and you want a process to allocate your 401k, then this spreadsheet is a very inexpensive investment with a fast payback. The fact that you can play around with the software before buying made it a 'no-brainer' purchase for me.
Reviewed by Woodman on If you would like to take a classic interpretation of asset allocation using MPT (Markowitz portfolio theory), this is a great spreadsheet. I like that they also calculate some technical indicators. If they could integrate the MPT with the technicals, the product would be even better but this is a very good start. I'd also like to see the ability to look at the many ETFs that are out in the marketplace. Right now this program is good for optimizing up to 100 different instruments.
Reviewed by Bmendel on I think this program works really well especially for the price. There are a few things that I would like to see in future editions of the product such as the ability to compare the optimized portfolio to the return of the S&P500 index and the ability to run more than 100 securities at one time. Other features I would like to see is the ability to download the data from Yahoo or other sources directly into the optimizer instead of having to copy and paste the data from the downloader. Given the complex calculations that it is doing it is relatively quick but that will depend on the speed of the computer.
Reviewed by John on Huge upgrades from the previous edition, which was already good. Added technical analysis tools are immensely helpful - will definitely keep checking in to buy updates.
Reviewed by Jeff Albright on I scanned the internet looking for how to calculate efficient frontiers with correlation analysis and was finding that I would have to virtually write the algebra for the entire analysis myself, a daunting task for any non-professional statistician. When I came across the portfolio optimization spreadsheet on Business Spreadsheets this was a godsend. Extremely user friendly and had all the variable inputs I needed. I will definitely be coming back for other spreadsheets as needed.
Reviewed by Morten Uth on Fantastic spreadsheet. Like others, I have tried to make my own spreadsheet after graduation from my financial bachelor, but this product is amazing. Making your own is waste of time!
Reviewed by Sura on A great product. Used during trial period, was very impressed with its simple interface and inputs. Tweaked my 401K with my MBA knowledge. For anyone who wants to achieve better performance on their 401k, my suggestion is try with this first. My goal is tweak at least once 18 months using this tool. Love this product.
Reviewed by VKapasi on Was always interested in portfolio optimization. I have been evaluating various offerings from others and stumbled upon this on the web. After reviewing and studying, I concluded that it saved lots of time and provided sufficient information in a short time to go the next step.
Reviewed by ChaseM on The portfolio optimizer is exactly what I was looking for. As an MBA student, I've been exposed to methods of portfolio management, attempting to build my own portfolio optimizer. I've used this primarily as a learning tool, but have since applied it to my real life portfolio. Easy to use. Well done.
Reviewed by John on As an economics major, struggling with Excel is just a part of life. However, massive Markowitz optimization problems do not have to waste an entire evening. This is simply the best tool I have found and if you want to save yourself a lot of time, just buy this.
Reviewed by Nicolas Raele on I've been looking for a software capable of analyzing, calculating and displaying information regarding portfolio optimization in a fast and efficient way. This is by far the best and most user-friendly solution I've found, and it is just perfect for my bachelor's thesis I am currently working on regarding this topic. Definitely a time saver and highly recommended.
Reviewed by TigerW11 on Optimization of a portfolio of investments has never been easier or more time saving with this tool. Not only does the spreadsheet give you different scenarios to put your portfolio in the best position, but it also gives you the indepth analysis metrics behind different optimization strategies.
Reviewed by Dejan on I have been using Portfolio Optimization for a research within my Master studies. I find it very easy to use and the speed is unbelievable. The fact that such low priced product offers optimization for up to 100 assets is also unbelievable. Performance measurement ratios were a nice touch to add.
Reviewed by Ziad on The optimization model is developed in the best way available to help in fund allocation among various securities. As well, the guide provided makes the model user friendly. Overall, this is a great model to use and count on.
Reviewed by Dina Bou Saba on I haven't had a product as efficient or useful as the Portfolio Optimization. I appreciate its precision as it's very helpful and supportive. Many thanks.
Reviewed by Kash79 on When I downloaded the portfolio optimization software I had a 30 day trial period on it. However, upon using it for 2 hours and implementing different scenarios with different stocks, I was amazed with the outcome. It practically gives you the optimal weights of products you have selected in order to maximize profits with the least risk possible. It uses Monte Carlo simulations and is very simple, clear and concise to use program. It accentuates your data into charts and graphs and enables you to look at different scenarios. I recommend it to beginners as well as junior professionals.
Reviewed by Alpha on I was trying to make all of my tasks simpler since I was bombarded with so many. Making a portfolio optimization report was not making my life any easier. Thanks to you guys and the templates you developed, you saved me the time and effort to finish all that I needed to do. Thank you very much.
Reviewed by Harry on This product is a very useful way to introduce optimization into a classroom in an applied rather than a theoretical manner. It takes some experience to make sure the constraints and data are correctly entered.
Reviewed by Chris D on I used this product for modeling a multi-asset class portfolio. The thing that attracted to this particular product over others in the market was its simplicity of use. The free trial certainly helped with putting the model through its paces and I was very impressed.
Many other models require multi-page instruction books to use, but this one was so user-friendly that I wouldn't bother looking for another one. But don't let the simplicity make you think it is a substandard product. It is anything but and produced very meaningful results and could be easily tailored to fit my specific needs. I would not recommend any other product for portfolio optimization over this one.
Reviewed by Calsymc on I used portfolio optimization to calculate the optimal weightings of electricity generation assets for the UK. Easy to use and avoids complex programming. Useful results section which I copied into my dissertation results. Worth the saved time! Thanks.
Reviewed by Morgana on I like the graphic representation of the information in addition to the charts. I like having the option of using price or return as an input. Some spreadsheets do not give you the choice. I would like to see more definitions, equations used and methodology. Great tool!
Reviewed by Allen V on I have tinkered with this Portfolio Optimization tool, and I think it's great! It has the ability to allow you to input as many years or periods as necessary to give you a more complete picture of an optimized portfolio. You can also add as many items as you like. I use it with
part of my prudent practice in building asset allocation models for my clients' 401k plans. I trust it enough to be a sound indicator of what I'm trying to accomplish for each participant, their risk sensitivity and time horizon.
Also, if you're an information junkie like me, you know that markets run in cycles. If you've been around long enough, you've lived through them to know pretty much what to expect. That, too, is something you can factor in with this program. Thank you, to whomever built this!
Reviewed by Janaexel on I have never seen such an amazing project before. Fascinating invention for every individual trader on his own and for employed traders in financial institutions as well. Great job! This application helps everybody who wants to calculate returns and risk of their portfolio and suggest the optimal weights of assets in the existing portfolio to gain the highest returns at minimum risk. Once more: wonderful!
Reviewed by Ken C on This is a wonderful, non-complicated, cost effective tool to use if you are looking to optimize your portfolio. The output is clear and easy to understand. I give it a solid A. Would be A+ if it included automatic data download.
Reviewed by Tyler B on The portfolio optimization spreadsheet comes with all of the features necessary for smart investment decision support. I can download historical stock prices directly to a spreadsheet, feed those quotes into the optimization software, and have a mathematically optimized portfolio in minutes. While the spreadsheet cannot choose your investments for you, it provides all of the major performance indicators to assist you in smart investing. Instead of poring over pages and pages of technical data, I now use this optimization software as my easy-to-use financial assistant.
Though the spreadsheet occasionally requires tweaking from time to time, I've yet to find a similar product with the same standard of quality and affordability. I continue to use this product and recommend it to anyone with an interest in bringing the field of portfolio optimization theory into an accessible, readily available Excel tool.
Reviewed by Xelqos on I have tested for a month the portfolio optimizer. Although it might seem complicated at first, it is very easy to use. Just by going through all of the tabs we obtain the desired results and they are easy to understand. The interface is great and does not require to install any program.
The greatest asset of this program is that you can use it in conjunction with the Data Downloader to be able to accurately pick up the prices and optimize the portfolio.
The results are shown in numbers but also as charts using the covariance matrix. The only negative side would be that the greater the size of the portfolio the greater the time it takes to optimize it, but if you think about it, you are asking Excel to calculate and optimize the portfolio using 2000 simulations of the returns. It is normal.
As a conclusion, I would definitely recommend this as a buy.
Reviewed by Dan S on Used this program to better understand how to optimize my portfolio and it worked great. I am glad that I was able to find this spreadsheet that is easy to use and presents the results in a clear and concise manner.
Reviewed by Jimmy on This spreadsheet is a user friendly way of running portfolio optimizations in Excel. The calculations run reasonably quickly and the output is displayed in a visually pleasing way. It has saved me considerable development time.
A slight improvement could be made to the output display by aligning the scales on the two return distribution graphs.
Reviewed by Jason on The portfolio optimization template has helped me personally and even my clients. Reviewing and rebalancing portfolios in a professional way. Impress clients with such a sophisticated template.
Reviewed by Kenneth on I have tried several different tools while Portfolio Optimization is the easiest to use. It can setup the constraints easily.
I love this tool very much. Of course, if it could download the data automatically, it would be perfect. :D