WEBVTT
Kind: captions
Language: en

00:00:11.530 --> 00:00:13.870
Let's say that I'm shopping around for a car
loan,

00:00:13.870 --> 00:00:16.250
to try to find one that I can afford.

00:00:16.250 --> 00:00:19.180
And I'm using Excel to help me find the answer
because I know that it's

00:00:19.180 --> 00:00:22.849
great at calculating
things automatically.

00:00:22.849 --> 00:00:25.140
All I know right now is that the total loan
amount

00:00:25.140 --> 00:00:29.739
should be $20,000, and the term should be
60

00:00:29.739 --> 00:00:31.849
months, and the highest monthly payment I
can

00:00:31.849 --> 00:00:35.329
afford is $400.

00:00:35.329 --> 00:00:37.910
The one thing I don't know is what interest
rate I

00:00:37.910 --> 00:00:41.620
would need in order to get a $400 payment.

00:00:41.620 --> 00:00:43.329
To find the interest rate, I'm going to use
a

00:00:43.329 --> 00:00:46.170
feature called What-If Analysis,

00:00:46.170 --> 00:00:51.079
which allows you to fill in the blanks
based on the information that you have.

00:00:51.079 --> 00:00:53.379
This is a somewhat more advanced feature,
and

00:00:53.379 --> 00:00:54.960
you'll need to be pretty comfortable using

00:00:54.960 --> 00:00:59.659
formulas and functions before you can
really use it.

00:00:59.659 --> 00:01:03.949
The first thing I need to do is set up a function
in this cell.

00:01:03.949 --> 00:01:07.490
So I'll go to the Formulas tab.

00:01:07.490 --> 00:01:10.110
And I'm going to use the PMT function, which
is in

00:01:10.110 --> 00:01:17.110
the Financial category.

00:01:25.040 --> 00:01:28.340
The PMT function calculates the payment for
a loan,

00:01:28.340 --> 00:01:33.990
based on constant payments and a constant
interest rate.

00:01:33.990 --> 00:01:37.530
Just like any function, if you need more
information about how it works,

00:01:37.530 --> 00:01:40.049
you can click Help
on this Function.

00:01:40.049 --> 00:01:42.159
But for now, you can just follow along and
enter

00:01:42.159 --> 00:01:47.180
in the same arguments that I do.

00:01:47.180 --> 00:01:50.649
For the Interest Rate, we'll enter cell B4
divided

00:01:50.649 --> 00:01:52.740
by 12.

00:01:52.740 --> 00:01:54.570
And that's because the yearly interest rate
gets

00:01:54.570 --> 00:01:58.579
divided up into 12 months.

00:01:58.579 --> 00:02:02.679
Nper is the number of payments, and in this
case

00:02:02.679 --> 00:02:06.950
that is cell B3.

00:02:06.950 --> 00:02:12.910
Pv is the present value, which is the total
amount

00:02:12.910 --> 00:02:18.010
of the loan, and that's cell B2.

00:02:18.010 --> 00:02:19.530
And we don't need to worry about the last
two

00:02:19.530 --> 00:02:24.040
arguments.

00:02:24.040 --> 00:02:26.510
Since our interest rate cell is blank, Excel
is

00:02:26.510 --> 00:02:29.040
assuming that the interest rate is zero.

00:02:29.040 --> 00:02:32.120
So this payment that it gives us is not correct.

00:02:32.120 --> 00:02:34.810
And this is where What-If Analysis comes in.

00:02:34.810 --> 00:02:38.930
It will allow us to say, "If the payment is
$400,

00:02:38.930 --> 00:02:45.120
then what will the value in this cell be."

00:02:45.120 --> 00:02:52.120
Go to the Data tab, and under What-If Analysis,
you'll see three options.

00:02:52.230 --> 00:02:57.340
We're going to use Goal Seek.

00:02:57.340 --> 00:03:04.340
"Set cell" will be the cell that contains
the

00:03:06.500 --> 00:03:11.560
monthly payment, which is B5.

00:03:11.560 --> 00:03:14.150
And we want a $400 monthly payment.

00:03:14.150 --> 00:03:16.640
But the PMT function actually needs to see
a

00:03:16.640 --> 00:03:18.390
negative value here.

00:03:18.390 --> 00:03:21.099
So type -400.

00:03:21.099 --> 00:03:22.920
It needs to be negative because the amount
is

00:03:22.920 --> 00:03:23.980
being subtracted

00:03:23.980 --> 00:03:27.950
from the loan when you make a payment.

00:03:27.950 --> 00:03:30.629
Where it says "By changing cell," we'll put
the

00:03:30.629 --> 00:03:33.000
blank cell that we're
trying to find the value for,

00:03:33.000 --> 00:03:38.329
which in this case is B4.

00:03:38.329 --> 00:03:44.849
And when you click OK, it should tell you
that Goal Seek found a solution.

00:03:44.849 --> 00:03:48.019
So just click OK again.

00:03:48.019 --> 00:03:51.560
And our answer is 7.42%,

00:03:51.560 --> 00:03:53.560
so that's the interest rate I'm going to look
for

00:03:53.560 --> 00:03:57.439
when I go out shopping for a car loan.

00:03:57.439 --> 00:03:59.730
You may want to practice this a few times
because

00:03:59.730 --> 00:04:00.799
it is a little bit different

00:04:00.799 --> 00:04:03.129
from the other
features that we've looked at.

00:04:03.129 --> 00:04:04.989
But if you're in a situation where you just
need

00:04:04.989 --> 00:04:06.459
to fill in an unknown value,

00:04:06.459 --> 00:04:13.459
it's a really great feature 
to use.