The real power in Excel comes in its ability to perform multiple mathematical calculations
for you. One of the tools in Excel that you can use
to perform these calculations is a Data tool called What-If Analysis.
What-If analysis allows you to see the effect that different values have in formulas.
It sounds complicated, I know, but let's think about it in real-life scenarios.Maybe you've
wondered, What interest rate do I need to qualify for to have a car payment of $400
on the car I want? Let's say you need a loan to buy a new car.
You know how much money you want to borrow how long of a period you want to take to pay
off the loan, and how much money you can afford to pay each month. But what you need to know
is what interest rate I need to make my payment $400 a month.
Right now, I can see that if I didn't have interest and just broke this $20000 into 60
monthly payments, I'd be paying about $333.33 a month.
But, I need to find the interest rate that will give me a $400 a month payment.
Rather than performing all these calculations on your own, you can use the What-If analysis
tool. Before we get into that, you may be wondering
about this function you see in B5. I've already inserted a function into this
cell that will calculate this for me. This isn't part of the What-If analysis, but
let me just really quickly show you what I did.
I needed a formula that would calculate the monthly payment.
Instead of writing a big formula myself, I inserted a function.
To do this, just select the Formula tab, and click the Insert Function command.
From the dialog box that appears, select PMT (short for payment), and then click OK.
You're then asked to select cells that will give the function the needed information.
This dialog box looks really confusing, but it really is very easy.
As you select a field, a description is displayed below that basically tells you what information
and cell you need to choose. Once you've entered all the info, you'll just
click OK. Right now, I'll just close this.
If you're still not comfortable with functions, you should watch our video lesson about functions
which will give you some more information. OK, let's go on to the What-if analysis tool.
There are actually 3 types of What-If Analysis that you can use.
To see these, select the Data tab, and locate the Data Tools group.
Click the What-If Analysis command and a list with the three options appear.
All three of these are different types of what-if analysis.
One of the tools, Goal Seek, is the perfect tool to help you determine the interest rate
you must qualify for to meet your needs. Once you select Goal Seek from the menu, a
small dialog box appears. Here, we have to set a specific cell, to a
specific value, by changing another cell. In this situation, we want to set B5, our
Payment cell. It already appears in the dialog box because
the cell is selected. We want to see it at a value of $400.
Since we're making a payment that will be subtracted from our loan amount, we have to
enter that as a negative number. We'll move down to the next field.
Then, we need to set the cell that we want to change that will give us that payment amount,
so we'll select cell B4, which is the interest rate.
Left-click a cell to select it, and it will appear in the dialog box.
Now, click OK, and then OK. Now, we can see that the interest rate appears
in the cell, and we know that a 7% interest rate will give us a $400 a month payment on
a $20,000 loan that is paid off over 5 years, or 60 months.
As you can see, in Goal Seek, we gave Excel the result that we wanted a payment of $400
a month over 60 months, and it gave us an input value that would give us that result.