Using Excel's Solver

Last updated Dec 9, 2005.

Working with Solver

Not long ago, we did an update on Scenarios and Goal Seek, tools in Excel that use variables for calculating values that make certain scenarios work or to reach certain results. Solver is a more sophisticated version of these tools. In fact, the Solver dialog box is like Scenarios and Goals on steroids.

To use Solver, you need to install it as an Add-In in Excel 2003. As such, it needs to be loaded under Tools > Add-Ins.

Figure 799

With Solver added, it appears on its own in the Tools menu.

To reiterate from last week’s essay, this cell represents the result of a mortgage based on a certain payment, rate of interest and term, to be offset by a rental of the property based on a certain rental rate per week for a number of weeks. Other factors to be considered as variables are property taxes and condo management fees. For the purposes of my current example, I want to break even, which means I set my Target cell to 0.

Figure 800

In the changing cells, I put in references by using CTRL + click to select each of those which represent my variables:

• Payment
• Rate of interest
• Term
• Rental rate per week
• Number of weeks
• Property taxes
• Condo management fees

When I click Solve: hurray! Solver found a solution!

Figure 801

I can either keep the Solver Solution or Restore Original values and start over.

Hey, I'd like to live in Hawaii for three months for free, so I select Answer, select Save Scenario, and click OK. Solver generates Answer Report 1 into a new Worksheet. Let's have a look.

Figure 802

Well, there's good news and bad news. It will work with the values I have, but the interest rate needs to be below 6%. In today's market, with a fixed 30 year mortgage, that may be tough to find.

So I remove the Interest Rate reference cell from my Changing Cells, and Add a Constraint setting it to be > or = 6.

Figure 803

Let's take a look at the solution.

Figure 804

Solver tried to tweak the untweakable. It finagled my property taxes and condo fees, and told me I'd have to rent the condo for – uh oh – 2,000 weeks. Not good.

I go back and let Solver guess my values, but this time I constrain the value of the number of weeks to 40 or fewer. This still leaves me with 12 weeks a year in Hawaii for free.

The first solution again reduces my interest rate, so what I do is simply remove it from the change parameters and set it at a healthy 6.25%.

Solver finds another solution, tweaking my figures to get me down to break even.

Figure 805

It is still trying to tweak my maintenance fee, so I have to constrain it and my property taxes to reasonable ranges. But, using this tool along with changes in my spreadsheet, I can begin to see ways that I might be able to make this work.

Just to clarify, things can go wrong in Solver. You can get errors for which there simply is no solution with the parameters you've plugged in.

You can also get some creepy data errors. If you do, you need to select Restore Original Values and hit OK, and hope that your formulas and data come back!

This brings up another point. Back up your spreadsheet before using Solver, or, for that matter, before using Scenarios and Goal Seek. In addition, don't save the Solver version until and unless you are absolutely sure that its findings and reports are those that you want to maintain.

At a minimum, keep copies of the previous versions handy so that you can go back and massage the data. Particularly if you are not the final decision maker as to whether the solution is practical or do-able; keep those backups handy.

Now, if I can only change it to live in Hawaii for half the year...

Solver in the Real World

If you happened to read my favorite book lists or some other updates about books I find useful, you know that I particularly admire writers who merge the teaching of technology with practical real world applications.

Since I went through the Solver scenario on personal finances, my editor sent me Peter Aitken's Manage Your Money and Investments with Microsoft Excel, a happy coincidence. Better yet, it's also available on Safari.

This is an excellent example of a book that teaches technology the right way: by applying it to significant real-world tasks. Whether you want to actually solve some real life financial issues, or learn more about Excel and its functions (pun intended), this book covers both bases. As someone who uses Excel on an irregular basis, I find that examples like this drive home points that make me "get" concepts that I ordinarily may flounder with.

The book also has a CD with templates that are referenced in the text (so you don't have to type in a lot of numbers) to help cover tasks like:

• Planning for retirement
• Saving for college
• Gauging Net Worth
• Comparing Mutual Funds
• Evaluate mortgages and loans
• Manage and analyze credit card issues

Sure, I hear you saying, you could just use Quicken to accomplish the same tasks; but you're an Office user, and going through the process Aitken describes will teach you more than you'll ever learn about Excel by reading a manual.

Not only that, besides being useful and educational, the book may even save you some money.