How to find Interest & Principal payments on a Loan in Excel

for this tutorial I'm going to show you

how to calculate the interest payments

for a loan but more specifically I'm

going to show you how to calculate what

part of that payment is principal and

what part is just pure interest now in

another tutorial I explained how to get

the actual monthly payments and so I'm

not going to explain that now just the

principal in the interest so let me

change the term of the loan to 30 years

at six point five percent now what I

have here is the amount of the loan the

yearly interest rate the term of the

loan in years and then over here I have

the monthly interest rate calculated

from the yearly and what your monthly

payments going to be I'm assuming it's a

fixed rate loan so the next thing I want

to figure out is what your principal

payment will be and what your interest

payment will be now these are very

simply the P PMT and I PMT functions so

what I'm gonna do is right here

recalculate month one so the interest

payment we already know what that is

let's go ahead and get the principal

all we're gonna do is equal PP MT open

parentheses the next we're gonna run

want the rate and the rate is going to

be the monthly rate the period is going

to be the month that we are currently in

so we're going to click right here the

number of periods is going to be the

term of the loan times 12 for 12 months

and the present value is going to be the

amount of the loan close the parentheses

hit enter and you notice it's 234 30

exactly what it is here one thing to

note it is negative because you're

paying it out so to make that a positive

value just so it is easier to read I

guess all you do is double click and

before the principal payment function

put a negative sign or a minus sign hit

enter and then it looks normal now to

calculate the interest payment it's

almost exactly the same equals I PMT

open parenthesis so interest payment

your rate is going to be the monthly

rate the period is going to be the

period your and over here and I'm

clicking this one because over here I

have all the months listed so that's how

I did it down here now the number of

periods is going to be the term of the

loan times 12 months so 30 years 12

months per year and the present value

will be the amount of the loan closed

parentheses hit enter

go ahead and put the minus sign in front

of the function and you get it so the

interest payments going to be a little

over $1,300 for your first payment and

you're only taking 234 dollars out of

your principal or I mean yeah out of

what you the total loan

so it's really interesting to actually

see how much of what you're paying goes

towards paying off the ending balance

depending on the actual loan another

neat thing you can do I'm gonna go ahead

and delete this is to figure out exactly

how much interest you're going to be

paying and to do that let's go ahead and

total interest tab equals sum and I'm

going to go ahead and highlight my

interest column

now you see I have a line for every

month here all the way down to the very

end of the loan see at the end you only

pay eight bucks on the interest so let's

see how much you pay total for the one

loan you pay three hundred seven

thousand eight hundred ninety nine

dollars and five cents total interest

anyway that's how loans work and that's

how you calculate how much you pay for

principal and interest and if you want

to grab this spreadsheet so you can

follow along or just use it for yourself

because there's not really any point in

making it when you can just get it very

quickly go ahead and go to the website

teach Excel comm and you can grab this

spreadsheet and many other tutorials and

lots of other stuff so that's it