I apologize for not having time the other night to post a brief tutorial on how to use Excel Solver to find asset allocation efficient frontiers.
***EDIT: This tutorial has been updated for the simplified percentage-only file versions. Please read below for further details.
1.) INSTALL MICROSOFT EXCEL SOLVER ADD-IN
Go to Tools --> Add-Ins --> select "Solver Add-In"
2.) DOWNLOAD AND OPEN POSTED "EFFICIENT FRONTIER" VERSIONS OF FILES.
***NOTE: Target cell, changing cells, and constraints are dependent on the time horizon and asset allocation options you would like to run. Please use the appropriate file as required.
3.) ON PORTFOLIO WORKSHEET, UPDATE INPUTS
-Allocation (int) - input your preferred allocation; efficient frontier search will begin from this allocation (can reduce run time if it's closer to feasibility)
-initial investment - $10k currently
-max ratio for allocation (int) - 10:1 max ratio currently
-max risk (std dev) - 11 currently (similar to couch potato portfolio)
4.) ON PORTFOLIO WORKSHEET, OPEN EXCEL SOLVER
Go to Tools --> Solver --> Solve
Target Cell - Specifies the target cell that you want to set to a certain value or that you want to maximize or minimize. This cell must contain a formula.
***Target Cell currently set to returns via "Your Portfolio Results"
Equal to - Specifies whether you want the target cell to be maximized, minimized, or set to a specific value. If you want a specific value, type it in the box.
***Target Cell currently set to MAXIMIZE return via "Your Portfolio Results"
By Changing Cells -
Specifies the cells that can be adjusted until the constraints in the problem are satisfied and the cell in the Set Target Cell box reaches its target. The adjustable cells must be related directly or indirectly to the target cell.
***Changing cells set to Allocation (int). Percentage allocations prevent simulation runs due to error messages. I was forced to remove the ERROR message built into the calculations when it deviated from 100% due to the Newtonian search function and the way it seeks to meet the objective.
Subject to the Constraints - Lists the current restrictions on the problem.
max ratio for allocation (int) - the largest asset class or subasset class is allowed to be X times that of the smallest. The smaller this number is, the faster your run will be and the likelier it will be to find a viable solution.
max risk (std dev) - this is the largest risk you are willing to accept. Excel Solver will find the asset allocation efficient frontier that maximizes return while meeting your risk requirement. As you reduce the standard deviation that you will accept, this will drive a more conservative asset allocation or allocation with lower correlations.
integers - inputs are required to be integers in order to speed up the Solver Newtonian search iterations.
postive - inputs are required to be greater than 0. All asset allocations must be long; no shorts allowed. (No academics with leveraged risk free rates allowed
5.) REVIEW RESULTS. REPEAT STEPS 3 & 4 AS REQUIRED TO BUILD YOUR EFFICIENT FRONTIER ASSET ALLOCATIONS ACROSS DIFFERING RISK LEVELS.
I think that's all. Please post comments and I'll try to answer them ASAP.
I look forward to discussing the results with other Diehards. Obviously, efficient frontiers are static and are only as good as the input data. However, they may give us some directional indications which we should discuss.