While the long list of Excel functions is one of the most enticing features of a Microsoft spreadsheet application, there are a few underutilized gems that enhance those features. One often overlooked tool is what-if analysis.
Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is a powerful target lookup function that allows you to work backward from the function and determine the input required to get the desired result from a formula in a cell. Read on to learn how to use the What-If Analysis Purpose Search Tool in Excel.
Example of the Goal Seek Tool from Excel
Suppose you want to take out a mortgage to buy a house and you are concerned about how the interest rate on the loan will affect your annual payments. The mortgage is $ 100,000 and you will pay it back over 30 years.
Using the PMT function in Excel, you can easily determine what the annual payments would be if the interest rate was 0%. The table will most likely look something like this:
Cell A2 is the annual interest rate, cell B2 is the length of the loan in years, and cell C2 is the amount of the mortgage loan. Formula in D2:
– /
= PMT (A2; B2; C2)
and represents the annual payments of a 30-year $ 100,000 mortgage loan at 0% interest. Note that the number in D2 is negative because Excel assumes that payments represent negative cash flow from your financial position.
Unfortunately, no mortgage lender is going to lend you $ 100,000 at 0% interest. Let’s say you figure it out and find out that you can afford to pay $ 6,000 a year on your mortgage. Now you are wondering what is the highest interest rate you can borrow on a loan to ensure you don’t pay more than $ 6,000 a year.
Many people in this situation will simply start typing numbers into A2 until the number in D2 reaches about $ 6,000. However, you can get Excel to do all the work for you with the What-If Analysis Purpose Search Tool. Basically, you will force Excel to work backwards from the result in D4 until it reaches an interest rate that corresponds to your maximum payout of $ 6,000.
To get started, click the Data tab on the ribbon and find the What-If Analysis button in the Data Tools section. Click the “What if” button and select “Find Destination” from the menu.
Excel opens a small window and only asks for three variables. The Set Cell variable must be the cell containing the formula. In our example, this is D2. The variable “Value” is the value you want the cell in D2 to be at the end of the analysis.
For us, this is -6000. Remember that Excel treats payments as negative cash flow. The On Cell Change variable is the interest rate Excel will find for you so that a $ 100,000 mortgage will cost you only $ 6,000 per year. So, use cell A2.
Click OK, and you might notice that Excel flashes a bunch of numbers in the corresponding cells until the iterations finally converge on the final number. In our case, cell A2 should now read about 4.31%.
This analysis shows that in order not to spend more than $ 6,000 a year on a 30-year $ 100,000 mortgage, you need to collateralize the loan at no more than 4.31%. If you want to continue with what-if analysis, you can try different combinations of numbers and variables to explore the options you have when trying to get a good mortgage interest rate.
The What-If Analysis Target Search Tool in Excel is a powerful addition to the various functions and formulas found in a typical spreadsheet. By working backward from the results of a formula in a cell, you can more clearly examine the various variables in your calculations.
–