馃攳
Excel Finance Class 63: Stock Valuation with Dividend Growth Model - YouTube
Channel: unknown
[0]
we're gonna finance an excel video
[1]
number 63 hey if you want to download
[3]
this excel workbook for chapter 7 or the
[6]
PDFs for chapter 7 click on the link
[9]
directly below the video and then scroll
[10]
way down to the finance excel section
[13]
alright
[14]
oh this video last video we did a
[17]
constant dividend so preferred stocks we
[20]
knew what the dividend was being we
[21]
dividend would be based on the contract
[24]
and so we developed a model to price or
[28]
figure out the value of the stock in
[30]
this video here we want to look at the
[33]
dividend growth model and here we have
[36]
the Assumption than dividends are
[37]
expected to grow at a constant percent
[41]
per period now again this model is not
[44]
for all stocks but it actually does give
[49]
some good valuations for companies that
[53]
have predictable stocks and in our first
[56]
video and the next couple ones after
[58]
that we talked about how the price right
[60]
now is the discounted future dividend so
[65]
here's dividend one dividend two
[68]
discounted back to periods dividend
[69]
three discounted back three periods on
[73]
out into infinity now if you do a little
[75]
algebra on this you get this and
[78]
actually you can reduce it down to this
[82]
so what this formula says and this will
[84]
be our dividend growth model to value
[86]
stock at time 0 it's the dividend just
[90]
paid times 1 plus the assumed constant
[93]
growth rate divided by rate discount
[97]
rate minus the growth rate and guess
[101]
what this top part right here the
[104]
dividend right now times 1 plus the
[106]
growth rate that gives us dividend one
[110]
period out from now right so the model
[113]
can be reduced down to this so if you
[115]
have dividend times zero then you do
[116]
this one if you have dividend at time
[118]
one then you use this one we will also
[121]
use this an extrapolation from this in a
[126]
later video
[127]
but let's see how this basic dividend
[130]
growth model works let's go over to
[131]
excel
[132]
we are our goal is at price time zero to
[136]
calculate the price we know that the
[138]
dividend just paid is 50 cents well we
[140]
can easily calculate our next dividend
[143]
right if it's got a growth rate of 2%
[145]
I say equals hey that times 1 plus
[148]
remember we've seen this so many times
[151]
in this class when you see this 1 plus
[153]
or even like in our last chapter we saw
[156]
1 minus the 1 oak always represents the
[159]
full amount that's the 50 cents and the
[161]
plus or minus represents the
[163]
proportional change all right there's
[167]
our dividend one period out from now so
[170]
to calculate the price now or estimate
[174]
these are all models that are used to
[176]
estimate we take that dividend divided
[178]
by R big R minus little G and so three
[185]
dollars and ninety two cents if we sell
[187]
see the stock selling for less than this
[190]
we're gonna buy it if it's more than
[191]
this we're not gonna buy it now
[193]
oftentimes you don't want to waste real
[195]
estate to do this so all in one formula
[197]
you would say dividend current dividend
[202]
times one plus our growth rate that will
[205]
be the denominator sorry that will be
[209]
the numerator the top of the fraction
[210]
divided by R in parentheses big our
[215]
discount rate minus our constant growth
[220]
and then we'll get the same thing all
[222]
right let's go look at another example
[223]
here now this example oh look we're
[226]
already given the dividend at time one
[232]
we know what the next dividend is gonna
[234]
be so it's basically done for us we take
[237]
that the hard parts done we take that
[238]
divided by R minus G now what happens if
[244]
R was bigger this I know there's a few
[246]
assumptions here for this model to work
[249]
you know you actually have to have a
[250]
reliable growth rate right constant
[254]
pattern of dividends being paid and r
[257]
can't be bigger than your growth rate
[260]
and we'll see later how to deal with
[262]
that in some situations but for this
[265]
model here
[266]
our has to be greater than this if it
[269]
wasn't then this goes to infinity so
[272]
there it is the stock should be $13.33
[277]
all right and next video will actually
[280]
see what happens we'll graph what
[282]
happens to this model as our changes and
[285]
as G changes and then see what happens
[288]
to the price all right see you next
[290]
video
Most Recent Videos:
You can go back to the homepage right here: Homepage