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.

image

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.

image

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

image

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).

image

Now we can launch the Solver.

  • Click Data.
  • Select Solver.

image

Then the Solver Parameters dialogue would be displayed.

image

  • 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 %)

image

Then we need to add couple constraints:

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

image

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

image

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

  • Click the Solve button.

image

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

image

Here are the results:

image

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

image

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!

 

 

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

One Response to Investment Portfolio Allocation–Excel Solver

  1. Math James says:

    There are just so many ways to learn math! Appreciate the post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: