a

How To Calculate Loan Payments Using The PMT Function In Excel

in this video we're gonna talk about how

to use the PMT function in Excel in

order to calculate loan payments so

let's begin by zooming in so in column B

I'm going to write the annual interest

rate and then the number of the years

that we're going to pay off the loan and

then the number of payments and also the

principal or the loan amount and then

we're going to calculate the monthly

payment to basically take care of that

loan now let's go ahead and extend the

width of column B we're also going to

figure out the total costs and also the

total interest paid so let's say that we

take a loan of $20,000 to buy a car and

let's say we want to pay it off in ten

years and we have an annual interest

rate of five percent

what is our monthly payment how much do

we need to pay every month so that we

can pay off this twenty thousand dollar

loan in ten years with a five percent

annual interest rate so to figure out

the monthly payment we need to use the

PMT function but first let's write a

formula that will tell us how many

payments that we need to make in the

course of ten years since we're making

monthly payments and we know that

there's twelve months in a single year

in ten years there's going to be a

hundred twenty months but to write a

formula for that we're gonna put equal

the number of years in cell c3 which is

ten years times twelve and so 10 years

corresponds to a hundred 20 months which

we have here now to calculate the

monthly payment type in equal PMT

and then first we need to determine the

monthly interest rate right now we have

an annual interest rate of 5% so let's

take the value in situ and divided by 12

to get the monthly interest rate and

then nper that's going to be the number

of payments that we're going to apply to

this loan which is a hundred twenty

payments so that's going to be the value

found in cell c4 and then the present

value of the loan which is twenty

thousand so that's the value found in c5

the other two arguments we don't need

right now so let's close the parentheses

and so the monthly payment is going to

be two hundred and twelve dollars and

thirteen cents so if he added a negative

sign you can get rid of the negative

value and so you can make it look like

that now let's talk about the total cost

of the loan but before we do that let's

highlight this cell so I'm gonna go to

home and then let's change the

background color so let's make it yellow

because that's going to be our important

information that tells us how much you

gotta pay every month it's a knock out

that loan so we have to pay two hundred

and twelve dollars and thirteen cents

for ten years in order to pay off that

twenty thousand dollar car what really

is the total cost that were a pain to

calculate the total cost is simply the

monthly payment times the number of

payments so it's gonna be whatever value

that we have in cell C seven times and

you can put or press shift six actually

shift eight to get the multiplication

symbol so it's C seven times the value

and C 4 let's make this wider and so the

total cost that were really paying which

is to 12 times 120 that's gonna be about

$25,000 and so that's a lot

now the total interest that we're paying

is going to be the difference between

the value in cell C 8 and cell c5 so

let's type in equal C 8 minus C 5 and so

we're paying five thousand dollars in

interest five thousand four hundred

fifty-five dollars and seventy two cents

and so that's a lot over the course of

ten years now let's go ahead and

highlight this value so I'm going to

give it a red color because by taking

the loan this is the amount of money

that you're going to lose because if you

think about it which is better instant

gratification or delayed gratification

let's say if you want the cards today

you have to pay five thousand and if you

round it five hundred dollars in

interest but let's say if you save up

money and in the course of a few years

you can pay this with cash and thus you

can save this amount of money in

interest if you're going to buy the same

car and typically when you wait the

value of a car decreases so by saving up

money and waiting to buy that car you'll

probably spend less than twenty thousand

because it's gonna be worth a little

less in a few years also you'd have to

pay the interest so sometimes it makes

sense to save up and buy things rather

than taking out a loan because once you

take out a loan you got a paint interest

and so that puts you in a hole but now

let's say if we want to buy a house

let's say the house is two hundred fifty

thousand dollars and let's say you get a

6% interest rate and you want to pay it

off in 30 years so your monthly payment

is going to be fifteen hundred dollars

but notice the true cost of the loan

it's two hundred eighty nine thousand

that's how much you can be paying in

interest if you have a 6% interest rate

and so this is a huge amount to pay in

interest

now there are some things you can do to

reduce your total interest payment the

first thing that you want to get good at

is you want to learn the art of

negotiating because if you can bring

down this interest rate you can save a

lot of money so let's say if we get it

down to 5% so our interest payment or

the total interest that we're gonna

paint in the course of 30 years it went

down from two hundred eighty nine

thousand to two hundred thirty three

thousand so you're saving a good fifty

five fifty six thousand dollars right

there if you can bring down your

interest rate from six percent it's a

five percent now let's see if we can get

it down to four percent look how much

interest we're paying over thirty years

it's a lot less if you can get it down

to three percent even better 129 K

compared to two hundred eighty nine K so

it's always good to get a low fixed

interest rate now if you get like a

variable alone where the interest rate

can vary you got to be careful because

if the interest rate goes up let's say

to ten percent this is how much you're

gonna be paying in total interest over

the course of 30 years and so it's

always good to keep your interest as low

as possible that'll save you a lot of

money in the long term now let's go back

to let's say a nice 4% interest rate the

second way that you can reduce the

amount of interest that you pay over the

long run is to pay off the loan quickly

so here we have thirty years in which

we're going to paint and right now the

total interest payment is 179 K so let's

say if we get a 20 year loan instead of

a 30 year loan notice that it dropped

from 179 K to 113 K so that's a that's a

huge savings of almost sixty six

thousand dollars now let's bring it down

from 20 years to ten years

so notice that the total interest that

you'll be paying is a lot less fifty

three thousand and so by negotiated for

interest-rate try to bring it down and

also paying off the loan quickly you can

reduce the total interest that you're

gonna pay for this house now granted

your monthly payment will increase let's

say if we had a 30-year note the monthly

payment is only if you round it $1,200

if you decrease it to 20 years it goes

up to 1,500 and if you decrease it to 10

years 2500 so your monthly payment will

be huge but in the long run you're gonna

save a lot of money in interest payments

so to review if you're gonna get take a

loan always work on your negotiating

skills try to bring down the interest

rate as low as possible second try to

pay off the loan as quickly as possible

in order that you can reduce the total

interest that you're going to pay over

the lifetime of the loan and at the same

time keep in mind what your budget is

because you don't want to pay too much

money per month so you need to know what

your limitations are so hopefully this

video not only showed you how to

calculate loan payments with Excel but

perhaps it also gave you more insight

into applying for loans and how you can

save money if you're going to take that

route in the long run so thanks for

watching