馃攳
Excel Finance Class 52: Bond Discount Or Premium Amortization Table. - YouTube
Channel: unknown
[0]
welcome to finance an excel video number
[1]
52 and if you want to download this
[4]
workbook chapter 6 click on the link
[6]
directly below the video and scroll way
[9]
down to the Excel finance class section
[12]
in this video here we want to see how to
[14]
build an amortization table to show the
[17]
effective interest that a bond holder
[21]
would pay now in the last couple videos
[24]
we've been talking about discounts and
[26]
premiums you'll to market greater than
[29]
our coupon rate it sells at a discount
[31]
if we have yield to market less than our
[34]
coupon it sells at a premium now what we
[37]
want to see in this video is we want to
[41]
see the actual interest expense given
[45]
that we discounted our our cash flows at
[48]
this rate here our actual interest
[51]
expense should be this even though our
[53]
coupon payments or 30 and that's what
[56]
this amortization table will show us now
[60]
last chapter we talked about consumer
[63]
loans and we did an amortization table
[65]
for consumer loans these calculations
[67]
are exactly the same we just have
[70]
different labels right so I'm going to
[72]
talk about our coupon we're going to
[74]
talk about the actual effective interest
[76]
under our yield to market rate we're
[79]
going to start with a carrying balance
[81]
and we're gonna have to add some
[82]
principal in each time all right so
[85]
let's go ahead we have time zero all the
[87]
way down to 20 I actually have freeze
[91]
panes turned on here so when you scroll
[93]
down it kind of the rows are hidden or
[97]
carrying balance well we issued the bond
[99]
and we assured it at a discount so on
[102]
our books we have a liability called
[104]
bonds payable that says we owe them nine
[108]
hundred and twenty eight dollars but
[110]
wait a second I thought that I thought
[111]
that the contract said a thousand well
[114]
in this this is the carrying value we
[119]
actually do record it as a thousand and
[121]
then we have a discount o which is a
[123]
contra liability so but this is our
[125]
carrying balance and this needs to go up
[128]
over time
[129]
on the last day it's got to be exactly a
[131]
thousand so the amortization to
[133]
actually so couple things one it will
[135]
show us how the balance goes up it'll
[137]
show us our actual interest amount
[141]
according to our yield tomorrow and
[143]
we'll actually be able to calculate the
[145]
yield to market there also alright let's
[148]
go ahead and build this equals and I'm
[150]
gonna do a minus the coupon payment each
[153]
period we need to show the actual coupon
[156]
payment I'm gonna hit the f4 key to lock
[158]
it ctrl enter and then copy it down
[161]
double click all the way down now what
[165]
is the actual interest it seems like
[167]
this this is the the cash flow this is
[169]
in accordance with our contract right we
[171]
pay 3% each half-year but our actual
[175]
interest because we issued this at a
[177]
discount with a rate like this so our
[179]
interest should be a little bit more
[181]
well the way you calculate interest for
[183]
anything is just like our consumer
[186]
amortized loan you got a look at how
[188]
much was in the account for the period
[190]
this was in there for six months this on
[193]
our books that said we owed this amount
[196]
so how do you calculate interest you
[198]
always say hey however much was in the
[200]
account for the period times the period
[203]
rate there it is that's the yield
[207]
demarco divided by n and I'm gonna hit
[209]
the f4 key to lock it because we're
[211]
gonna copy this down now I'm gonna hit
[212]
tab look at that it is it's more
[217]
expensive and you would expect that
[219]
because right are our contracts at 3 but
[222]
when we issued when they discounted at
[224]
3.5 the effective interest paid is going
[226]
to be a little bit more now what's the
[229]
difference between these two I'm gonna
[231]
say that one - this one it's two dollars
[234]
and 51 cents and that amount is added so
[238]
we're going to take our balance from the
[240]
cell above plus whatever the principal
[244]
I'm spelled that wrong addition is
[250]
it probably should be like that right
[254]
now these four Mills we can copy all the
[258]
way down and guess what it will actually
[260]
show us at the very end that our balance
[262]
are carrying balance which is increasing
[264]
each pair you could see them went from 9
[266]
28 94 to 931 but when we copy this down
[269]
don't click on copy it down you'd see
[271]
that there it is on the last day of the
[274]
period we have exactly a thousand
[277]
dollars as our carrying balance you can
[281]
also see that as time went on our our
[283]
interest expense got bigger right and
[286]
why is that let's look at this it's
[288]
because the carrying balance got bigger
[290]
each period so the amount we add then to
[294]
each period gets bigger
[296]
now one other great point here is we can
[301]
actually calculate the yield to market
[303]
based on our interest paid and the
[306]
amount we owed or the carrying amount we
[308]
owed and every single time it better be
[310]
exactly equal to 3.5 well for any period
[314]
rate what is the formula it's however
[317]
much we paid in interest divided by
[319]
however much we owed 3.5 and guess what
[324]
when you double click and send it down
[325]
every single time it's going to show us
[328]
that 3.5 the effective interest rate
[332]
method for amortize a discount or
[336]
premium now watch this what's so great
[338]
about this table is we can simply change
[341]
this to a premium so now we're going to
[343]
change this to 5% now our price is
[348]
greater than our face value and we can
[351]
see that the table works perfectly this
[354]
shows as a minus right because well the
[358]
first thing to notice is that this is
[360]
smaller and you would expect that right
[362]
we issued it at a discount rate of 5 so
[365]
the effective interest has to be less we
[368]
calculate the difference and when we add
[370]
here this negative right
[372]
that means the carrying balance is going
[374]
to go down so from 1077 to 1074 and sure
[378]
enough
[379]
at the very last period exactly equal to
[383]
1000 and over here you could see the
[385]
effective interest rate so even though
[390]
you know we still we that looks like
[392]
there's different interest rates because
[395]
we looked at the cash flows discounted
[397]
them at our actual yield to market then
[400]
the effective interest is actually
[402]
exactly as seen here less under a
[406]
premium and more compared to the coupon
[413]
under a discount alright next video
[416]
we'll see you next video
Most Recent Videos:
You can go back to the homepage right here: Homepage