a

How to build an Amortization table in EXCEL (Fast and easy) Less than 5 minutes

hi guys welcome to I hate math group

today I'm going to show you how to build

an amortization table in Excel from

scratch so let's do it so I have the

amount borrow which in this case is

going to be $100,000 the periods are

going to be the following well let's say

that this is 30 years but I want to do

this monthly so I'm going to multiply

this by 12 because you have 360 periods

the rate is going to be let's say that

they charge me 13% but I'm going to put

it monthly so this is point 13 divided

by 12 and finally I'm going to have the

payment which is going to be the

following equals PMT parentheses and

then Excel is going to ask me hey what

is the rate well the rate is going to be

this guy coma the period is going to be

360 and the present value is 100,000 put

a negative in the front because if not

Excel will give you a negative value as

your payment comma zero because as a

future value and then just close your

parentheses this is going to be your

payment let's do it now like your month

is number one the beginning balance is

actually going to be $100,000 so I can

go ahead and just type the payment is

going to be this guy right here now how

do I calculate the interest the interest

is calculated in the following way you

do equals the beginning balance times

the rate this is how much you're going

to be paying on interest every month but

remember this amount is going to change

all the time how do I do the principal

well this is how much you make on your

payment minus the interest that you pay

that's going to be your principal and

your ending balance is going to be the

beginning balance - what you pay on your

principal now let's do months - the

beginning balance is actually going to

be the ending balance of the first month

the payment is actually going to be the

same but you know what this becomes very

tedious to write it over and over so I'm

going to go ahead and do the following

equals and then click on c4 but actually

you can lock this cell by doing a dollar

sign lettered dollar sign this is

basically going to block the cell and

it's going to repeat all the way to the

360 periods

well the interests remember is going to

be the beginning which is in this case

this but my rate will always be the same

well do you know what I'm going to do

I'm going to go ahead and log the rate

again I do dollar sign and then dollar

sign between the letters because look in

Excel b7 b8 that's going to change for

the interest so the rate is always going

to be the same now the principal again

how do I calculate my payment

my minoz my interest and the ending

balance is finally going to be the

beginning balance minus the principal

once you build the second R row go ahead

and shade it and go all the way down to

360 360 and you can see on the right

side I have the numbers all the way 360

and almost done I pass it 360 guala I

just build my amortization table as you

can see here the principal look what's

happening is actually going up and the

interest is actually going down little

by little I can see that my ending

balance is going all the way

oops I passed it to zero now how do I

know this table is correct well look

what I'm going to do here this is how

much you pay for your principal if you

do the sum of all the payments that you

may all the way to one you should get

the $100,000 which I did and then if you

want to figure it out the interest pay

all over the lawn just just do the sum

of all the interest payments and don't

cry a dictator

the table because it's usually a lot but

do you notice how town value works so

this is basically how much money you pay

during the loan and this is how much you

repay back and that's it how easy was

that

please don't forget to watch our other

videos also remember you can Skype with

us and also thanks so much for learning