Let's say that I'm shopping around for a car loan,
to try to find one that I can afford.
And I'm using Excel to help me find the answer because I know that it's
great at calculating things automatically.
All I know right now is that the total loan amount
should be $20,000, and the term should be 60
months, and the highest monthly payment I can
afford is $400.
The one thing I don't know is what interest rate I
would need in order to get a $400 payment.
To find the interest rate, I'm going to use a
feature called What-If Analysis,
which allows you to fill in the blanks based on the information that you have.
This is a somewhat more advanced feature, and
you'll need to be pretty comfortable using
formulas and functions before you can really use it.
The first thing I need to do is set up a function in this cell.
So I'll go to the Formulas tab.
And I'm going to use the PMT function, which is in
the Financial category.
The PMT function calculates the payment for a loan,
based on constant payments and a constant interest rate.
Just like any function, if you need more information about how it works,
you can click Help on this Function.
But for now, you can just follow along and enter
in the same arguments that I do.
For the Interest Rate, we'll enter cell B4 divided
by 12.
And that's because the yearly interest rate gets
divided up into 12 months.
Nper is the number of payments, and in this case
that is cell B3.
Pv is the present value, which is the total amount
of the loan, and that's cell B2.
And we don't need to worry about the last two
arguments.
Since our interest rate cell is blank, Excel is
assuming that the interest rate is zero.
So this payment that it gives us is not correct.
And this is where What-If Analysis comes in.
It will allow us to say, "If the payment is $400,
then what will the value in this cell be."
Go to the Data tab, and under What-If Analysis, you'll see three options.
We're going to use Goal Seek.
"Set cell" will be the cell that contains the
monthly payment, which is B5.
And we want a $400 monthly payment.
But the PMT function actually needs to see a
negative value here.
So type -400.
It needs to be negative because the amount is
being subtracted
from the loan when you make a payment.
Where it says "By changing cell," we'll put the
blank cell that we're trying to find the value for,
which in this case is B4.
And when you click OK, it should tell you that Goal Seek found a solution.
So just click OK again.
And our answer is 7.42%,
so that's the interest rate I'm going to look for
when I go out shopping for a car loan.
You may want to practice this a few times because
it is a little bit different
from the other features that we've looked at.
But if you're in a situation where you just need
to fill in an unknown value,
it's a really great feature to use.