Excel 2010 -Solver

Solver is not something new to Microsoft Excel; it was first introduced to Excel 3.0 on an OEM basis in 1991, i.e. 20 years ago.  However, I noticed it was one of the best kept secrets in Excel so I am going to formally introduce it to you as a NEW feature.

What’s solver?  It is a what-if analysis tool that you can use to do planning, budgeting, projection and optimization.  It consists of mainly 4 components:

• Objective – you specify your goal in a cell

• Variable – cells that solver would keep adjusting in order to reach the objective

• Constraints – there may be constraints to your problem

• Method – How do you want to solve the problem

Solver is not enabled when you first install Excel, you must first enable the Solver Add-In. To Enable Solver

• Click the File Menu and choose Options and you would see Excel Options dialogue box.

• Select Add-Ins from the left sidebar and then hit the GO button next to Manage Excel Add-ins at the bottom.

• Check the Solver Add-in option and Click the OK button.

• Now you would see the Solver group under the Data tab.

Example – Use Solver to find how much you need to contribute for your retirement You are 30 years old and earn $60,000 a year.  Your goal is to retire at age 65 and able to receive a retirement income that is equivalent to 70% of your salary at age 65. Assumptions:

• Your salary and retirement income will increase 3% a year,
• Interest rate is 10% until 55 and then 5% afterward

Question: What is the minimum contribution percentage that you must deposit to your retirement saving account? I prepared the following Excel worksheet.

I don’t know the retirement saving percentage, so I just put 0% (I1). How to setup Solver? You can start Solver by clicking on the Solver icon on Data tab.

The Solver dialogue would be shown.

• The objective cell is $I$1 and you want to Minimize it.
• Changing Variable Cell is also $I$1.
• Make sure the balance for Age 65 – 85 is >= 0, so you setup the constraints to be $F$38:$F57 >= 0.
• Choose Simplex LP Solving Method.

After I click the Solve button, you would see the Solver Results dialogue.  Click the OK button.

Here is the result.  You have to save at least 12.19% of your salary into your retirement saving.  You can verify the result by checking the balance at Age 85. It is $0.00.

This is a very simple model. I only spent a few minutes to formulate the worksheet; but you can see it took me no time to setup the Solver. Financial planners / insurance brokers have software to do planning for their customers; but there would be situation that their software doesn’t know how to handle. If you know Microsoft Excel Solver, you would never run into such situation.

If you need to build a more sophisticated model, you can contact me at chan_a@algconsultings.com.  I am an Associate of Society of Actuaries (ASA) with extensive financial model experience.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!