馃攳
Excel Finance Class 42: Consumer Amortized Loans - YouTube
Channel: unknown
[0]
welcome to excel in finance video number
[1]
42 hey if you want to download this
[4]
workbook for chapter 5 click on the link
[7]
directly below the video and then scroll
[10]
way down to the Excel finance class in
[13]
this video we got to talk about consumer
[14]
loans and an amort what are amortized
[17]
loans now amortized loans just mean that
[21]
every time you make a payment and we've
[24]
been we've seen a number of videos on
[25]
how to calculate the payment it sparked
[27]
interest in part principal now we want
[30]
to build an amortization table because
[32]
it will help us especially us uninformed
[36]
consumers it will help us understand the
[39]
painful truth about interest on
[42]
amortized loans
[43]
all right let's calculate our PMT first
[46]
we've done this like 10 times already in
[48]
this class so our rate is going to be
[50]
our period rate NPR is all the periods
[54]
our present value is going to be our
[57]
loan amount and this is a positive
[59]
because it's coming in to our pocket and
[60]
then enter now to build an amortization
[64]
table so we have to put the numbers 0 to
[67]
360 because for each payment we're going
[71]
to break apart and look how much of this
[73]
goes into the bankers pocket as interest
[77]
and how much goes to reduce our loan I'm
[80]
going to take periods here tab PMT tab
[87]
interest paid Prince tab principal
[95]
reduction and finally the balance
[98]
I'm gonna highlight these do some
[102]
borders whatever formatting you want and
[105]
wrap text okay and I'm gonna go like
[113]
that and then add some border so now I
[115]
want to show you a trick here this is a
[117]
great trick and there's all sorts of
[119]
ways and I actually have a few other
[120]
videos at YouTube to show you how to
[122]
create the number sequence zero to 360
[126]
but let's just say we're always using 30
[130]
30 years and so we always need 360
[133]
totally amazing trick watch this you got
[136]
a point you highlight a cell with a zero
[138]
point to the fill handle and then right
[143]
click that means right click and drag
[146]
notice I drag down and then drag back up
[150]
so really we drag down it up and you
[153]
point to series and then you say you
[156]
want to fill this series in columns
[159]
which means it'll fill it down there and
[161]
your step value is one and the stop
[163]
value is 360 is that not totally amazing
[167]
and then boom just like that you can
[169]
prove to yourself that it did it I'm
[171]
going to click in the cell and ctrl down
[173]
arrow sure enough control up arrow all
[178]
right now the rest of it since there's
[179]
something to the left we can create our
[181]
formulas and then double click and send
[183]
them down now nothing goes in this row
[185]
except for the balance so on the x zero
[187]
we just go that amount
[189]
now we come here
[195]
and I'm gonna make my PMT equals PMT Oh
[199]
equals this because we already did it
[201]
and guess what I'm going to convert this
[203]
for the just the look of the
[207]
amortization table to a positive so I
[209]
put a negative and then I'm gonna hit
[211]
the f4 control enter and then double
[214]
click and send it down if you don't
[216]
believe it ctrl down arrow control up
[219]
arrow all right so we pay that amount
[222]
each period now how in the world do we
[224]
figure out interest and if you figure
[226]
this out which we'll do in just a second
[228]
it will reveal why when you send in a
[232]
mortgage payment they take so much of it
[236]
in the early years as interest well just
[238]
think about this what is the logic of
[240]
this you went out and borrowed 300k it
[244]
sat in that account and you owed it for
[247]
one month and then at the end of the
[248]
month they said okay now here's your
[251]
payment but how much interest do you owe
[253]
so the equals the amount that was
[258]
sitting in the account for the whole
[259]
month times the period rate now I'm
[263]
gonna lock that one because when I copy
[266]
this formula down it's going to need to
[269]
be locked so when I go down each one of
[271]
these cells this one will always move to
[273]
the new balance because this payment
[275]
will be part interest and whatever is
[278]
left over will be the amount we reduce
[280]
in our principal and that means this
[283]
will go down each period I control enter
[287]
I'm not going to copy that down yet I'm
[291]
gonna control shift for control shift
[294]
for is the keyboard shortcut for
[296]
currency now principle reduction okay
[299]
maybe some of us you know what I have a
[301]
hard time figuring out that but the
[303]
logic is amount in the account for
[306]
whatever period times the period rate
[308]
but this one we should be able to figure
[310]
out we sent in this amount they stole
[313]
this amount as interest no no it's not
[315]
stealing if your consumer it seems like
[318]
stealing but it is contractual
[320]
extraction you signed the contract right
[323]
so if they sent this amount in we simply
[327]
go like
[328]
that we sent in the interest they
[331]
contractually can take tab 344 so I send
[336]
in 1656 and I only get to keep 344
[340]
you betcha because that balance is
[342]
sitting right there and you do owe that
[344]
interest as we've already seen there are
[347]
other types of contracts but in many
[350]
consumer loans this is the way it works
[352]
alright so what's our balance equals a
[353]
relative cell reference one above minus
[356]
whatever they was left over after the
[359]
interest all right now let's check
[363]
relative cell references escape both
[367]
relative cell references so when I copy
[368]
down that'll work and then this one has
[370]
that cell lock so watch this we can just
[372]
highlight this three different formulas
[375]
point to the fill handle and when we see
[377]
our angry rabbit are a little crosshair
[379]
double click and send it down it sends
[381]
both formulas down I'm going to control
[383]
down arrow we should have a balance of
[386]
zero at the end Wow
[389]
amortization table so now you can see we
[393]
send in this much interest interest rate
[396]
principal reduction and balance now
[397]
let's go down to the bottom you can see
[399]
that the interest is very small at the
[402]
end and then the principal amount that
[406]
it's reduced by our balance is very
[409]
large now it's kind of annoying here I
[411]
don't see my field names those labels at
[415]
the top so I'm going to control home I
[417]
actually want to highlight this whole
[420]
row row ten I don't want to freeze pain
[424]
so I go to the window where's my view
[429]
sorry that's the old the window groups
[432]
of view and then right here in the
[434]
window group freeze panes so now when I
[439]
scroll down wherever I am I can see that
[442]
whatever the numbers are and my labels
[446]
now I want to come down here and add up
[449]
all the money we put into this we didn't
[453]
have a down calculation that would be
[455]
including this also but just I'm going
[457]
to alt equals and then tab alt equals is
[460]
a keyboard shortcut for autos
[461]
so that's how much we paid in total
[464]
let's alt equals and add up all the
[466]
interest look at that that copied that
[473]
down it
[482]
I don't want that phone I'm gonna delete
[484]
it happens to be correct but what I want
[486]
to do is I want to add up this column to
[488]
see if we get the same because if this
[493]
calculates this is our calculation for
[495]
all the the principle reduction so when
[497]
I all to equal I'm gonna get 300k all
[501]
right so that's an amortized loan we can
[503]
see the each payment part of its
[505]
interest part of its principal in the
[508]
later years most of its principal
[510]
control home in the early years most of
[514]
it is interest all right a couple more
[516]
loan things in our next video all right
[518]
see you next video
Most Recent Videos:
You can go back to the homepage right here: Homepage