Investment Portfolio Allocation–Excel Solver

Excel is great desktop business analytics software; we can use it to analyze many different business problems.  And quite often, Excel has multiple ways to solve the problems; we can choose the one that we are more comfortable.  For example, I used Solver Foundation to determine the investment portfolio allocation in one of my previous blog,  I am an IT consultant so I like Solver Foundation because I can use code to define the problem.  However, coding may be too remote for many business users.  Don’t worry, there is another approach that I am going to show you and this time, it would be more end user friendly.

Excel Solver

Excel Solver is nothing new and it was introduced to Excel 3.0 in 1991.  However, it was one of the best kept Excel secrets; i.e. not a lot of Excel users know about it.  If you want to find out how you can enable it in Excel 2010, please refer to my blog Excel 2010 – Solver.

Let’s review the original worksheet.  It is a very simple worksheet and we have the historical returns, cross correlation and covariants of various investment vehicles.

If we want to use Excel Solver, then we have to define most of the problem in Excel,  So I am going to add couple more columns beside Mean return for Allocation% and Expected Returns for each allocation.  Expected return is equal to Allocation% * Mean Return.

I need another row under the covariants table and let’s call it Expected Risk.

Expected Risk of Stocks is calculated as =SUMPRODUCT(C25:C30, $K$7:$K$7:$K$12)*K7; where C25:C30 and $K$7:$K$7:$K$12 are the covariants of Stocks with other investment vehicles and Allocation% respectively.

Out last step is to add the objective, the total expected risk, =SUM(C31:H31).

Now we can launch the Solver.

• Click Data.
• Select Solver.

Then the Solver Parameters dialogue would be displayed.

• Set Set Objective: to $I$31 (total expected risk)
• Change To: to Min;; we want to minimize the expected risk..
• Set By Changing Variable Cells: to $K$7:$K$12 (Allocation %)

Then we need to add couple constraints:

• Total Allocation % ($K$13) should be added up to 100%

• Expected Return ($L$13) >= Required Return ($C$33)

Now, we have defined the problem and we are ready to solve it.

• Click the Solve button.

Instantly, Solver found a solution.  Let’s click the OK button and examine the results.

Here are the results:

And if we compare the results with the ones that were generated by Solver Foundation, they are the same.

Conclusion

Both approaches empower us to solve sophisticated mathematical models without being a mathematician.  And we have a choice.  I am a coder and I like to go through the problem line by line’; so I prefer Solver Foundation.  But if you are a heavy Excel user, then you may want to use Excel Solver.  There are other pros and cons of both approaches but I would leave it to another blog.

I often ask myself how much do I know Excel; Excel is like a treasure island with so many useful features that are hidden from us.  What is your most favorite Excel feature?

Andrew Chan helps you to make better and faster decisions!