All language subtitles for 1. Goal Seek and the PMT Function

In this section of the course, we're going to explore Excel's what-if analysis utilities.

And if you've never heard of these before, you're going to find them on the data tab over in the forecast

group.

It's this little dropdown here that you want.

So there are three different what-if analysis tools.

We have scenario manager, go seek and data tables, and we're going to be looking at each one of these

in this section.

I'm going to start out in this lesson by taking a look at goal seek.

So what exactly is goal seek?

What if we take a look at the screen?

Tap it says.

Find the right input for the value that you want and the way that I kind of see go seek is it's it's

kind of a way of working backwards from an end point to a start point.

So let me show you what I mean.

Now the example that we're going to use here is the example of a loan payments.

And in order to work out this loan payment, we're actually going to use another formula that I haven't

showed yet in this course.

And that is the PMT formula.

And Pantene stands for payment and it allows you to calculate monthly payments on a loan.

So we have our loan information just here.

So this might be a loan that I'm taking out from the bank so that I can buy a new car.

I'm borrowing the money over the term of 60 months at a rate of 3.5 percent per annum.

I want the PMT formula will do is it will work out what my monthly payment is going to be with all of

these pieces of criteria.

So let's take a look at that function.

First of all, we're going to type in equals p m t now.

The PM T function has five arguments, with the last two being optional arguments.

Now we're not actually going to use the last two in this calculation because we don't need them.

So the first thing we need here is the rate.

So the rate is what I have up here in Selby.

Three.

Now the thing you have to remember with the PM T calculation is that it's going to work out monthly

payments.

So every value that you select for the formula also needs to be monthly.

And currently, the rate that I've got selected in Selby three is the rate per annum.

So it's 3.5 five percent for the year.

So what I need to do is I need to make that a monthly rate by dividing it by 12.

The next argument is NPR, and that just means the number of periods or the number of payments.

So I'm going to be making my payments at the end of every month over 60 months.

Now again, if I had that in the cell as five years, I would then need to do five multiplied by 12

to get the months.

And then the final argument that we need here is Peavey the present value.

And this is really the amount of the loan.

So that is simply B five going to close off the bracket and hence and it's going to tell us how much

we're going to be paying each month.

Now one thing you'll notice here is that it's showing as a negative value because the way that PMT looks

at this is that it's coming out of your account, so you are down 436 pounds sixty.

Now, if you wanted to make this a positive value for any calculations that you're doing, you can simply

put a minus symbol in front of the loan amounts, and that will give you a positive value instead.

So now I have my final calculation I'm going to be paying 436 sixty a month now.

Maybe I look at this and I think to myself, Well, you know what, actually, I can spare £500 a month,

and if I can spare £500 a month, maybe that means I can borrow more money.

Or maybe it means that I can decrease the number of months that I'm paying this loan over.

This is where Goal C comes in because by using Goal C, we can say, right, I'm going to pay £500 a

month.

Tell me how much extra I could borrow or how many months I could reduce the term by.

So we're starting from our target and working backwards.

So I'm going to select the payment.

So let's go to what if analysis and goal seek.

So let's remove everything from here.

We want to first select asset sell.

So for me, this is going to be sell basics and I want to set this sell to 500 because that's how much

I can afford.

And then I need to specify the sell that I want to change.

So it might be that I want to borrow more money, or it might be that I want to reduce the term.

Now, in this case, I want to see how much more money I can borrow if I'm paying £500 a month.

So I'm going to choose Sell, be five.

Let's click on.

Okay?

And would you take a look at that?

If I'm paying £500 a month, I could borrow twenty seven thousand four hundred and eighty five pounds,

so quite a bit more than previously.

And that is the idea behind goal.

Seek.

You tell it your target and then it kind of works back and recalculate for you.