00:03: hey everyone this is nick diabetis
00:05: teaching you financial modeling
00:07: today we're going to be doing an
00:09: introduction to the
00:10: discounted cash flow evaluation model
00:13: this is part of our lecture segment on
00:16: the cost of capital in the gcf model
00:19: so this model
00:22: the dcf model is a way that we can
00:26: value a stock and it's certainly not the
00:29: only approach
00:30: that can be used for stock valuation
00:32: there are a number of other common ones
00:35: um some of the other common ones are
00:38: the dividend discount model which we
00:40: have briefly touched on
00:42: as well as comparable based approaches
00:45: which
00:45: uh look at similar companies and try to
00:50: uh base the valuation off of the
00:52: valuation of other companies
00:55: uh but different uh valuation models
01:00: are best applied in different settings
01:02: depending on different
01:03: features of the company the dividend
01:06: discount model
01:07: can be a perfectly valid model but
01:10: it should only be applied when the
01:13: dividends
01:14: are very stable they are growing at a
01:17: fairly constant rate
01:19: there's not really a risk of the
01:20: dividend being cut
01:22: and so this is typically only going to
01:23: be very mature companies
01:25: that have a long history of paying
01:27: dividends
01:28: and have not deviated from just
01:32: kind of constant growth in those
01:33: dividends
01:37: and then the comparable approaches
01:42: the issue there is that it's not going
01:44: to be very specific to this
01:46: company you're always just comparing
01:49: to other companies that are out there
01:51: and the approach is only going to be as
01:52: good as those comparisons can be
01:56: and oftentimes for a company or valuing
01:59: there are not
02:00: really good comparables available
02:04: so the dcf model can be applied to any
02:08: company
02:09: and is completely about the particulars
02:12: of this company
02:14: not having to look at any other
02:16: companies and it works just fine for a
02:18: company that does not pay any dividends
02:20: or their dividends have been irregular
02:24: now it is quite a bit more involved than
02:27: these other approaches
02:29: i discussed um
02:32: and that's why a lot of times these
02:36: other
02:36: types of models are still used because
02:38: they're quite a bit
02:39: easier to apply but the dcf
02:43: is kind of the most detailed evaluation
02:46: approach
02:46: and is going to work for any possible
02:49: company
02:52: and then we're also focusing on this in
02:54: the course because it's often considered
02:56: kind of a capstone
02:57: finance model basically you know anyone
03:00: that
03:01: wants to do financial modeling should
03:02: know how to build a dcf model
03:05: and i've often seen this uh as part of a
03:08: job application
03:09: process that employers about actually
03:11: request that you prepare a dcf
03:13: for the interview and so it's definitely
03:16: something that you want to know how to
03:18: do if you want to work in financial
03:20: modeling
03:21: and it covers a lot of different
03:23: concepts
03:24: and brings them all together into one
03:26: model and so
03:28: there's a lot of value in learning it
03:31: just for trying
03:32: to work through all the component pieces
03:36: so what's involved here in the dcf model
03:39: so there's two main sides of this model
03:44: the cost of capital and the free cash
03:46: flow
03:47: sides of the model so you can broadly
03:51: break it down into those two parts
03:53: there is a little bit outside of that in
03:55: determining the
03:56: enterprise value and stock value but
03:58: that's a very small part of the model
04:01: primarily the work involved is in
04:03: estimating the cost of capital
04:04: and estimating the future free cash
04:08: flows
04:10: so for the cost of capital that's the
04:12: main focus of
04:14: this lecture series and then we'll come
04:16: back in the following lecture series
04:19: to look at free cash flow estimation
04:23: the cost of capital estimation has a few
04:25: different parts
04:27: uh we can break that down into the
04:30: debt and equity side of things and then
04:34: within debt and equity you're going to
04:35: have to evaluate both the cost
04:38: or or rate on the debt or equity
04:41: whatever
04:42: the capital as well as the market value
04:45: of it so that we can see uh the
04:48: proportion of each in the capital
04:49: structure
04:50: ultimately come up with a weighted
04:52: average cost of capital or
04:54: whack that represents the entire
04:57: business
05:00: and on the free cash flow side of the
05:01: model there we will be
05:04: uh calculating the historical free cash
05:06: flows based off the
05:08: financials and we'll be projecting
05:11: the historical financial statements
05:14: into the future and then calculating the
05:18: free cash flows off of those projected
05:20: financial statements and then
05:24: all this gets put together to ultimately
05:26: come up with the enterprise
05:28: value of the company
05:31: so the dcf
05:35: uh as you may have already thought a
05:38: little bit by the name
05:39: discounted cash flow uh it's all about
05:42: just taking the present value of
05:46: future cash flows to determine the value
05:48: of this company
05:49: so i mean you'll see that in a lot of
05:52: financial models that at its core we're
05:54: just taking a present value somewhere
05:57: um but that is a very essential concept
06:00: in finance so it's not surprising
06:03: um because we can
06:06: look at any any apps at any asset that
06:09: pays cash flows
06:11: no matter whether it's a stock it's a
06:13: bond it's a derivative
06:15: uh it's a a crypto security token
06:20: whatever it is if it pays cash flows
06:23: then we can value it
06:24: by taking the present value of the
06:26: future cash flows
06:28: plain and simple anything that pays cash
06:30: flows we can value
06:31: by that approach
06:34: uh when thinking about the dividend
06:37: discount model
06:39: there we're saying well the dividends
06:41: are the cash flows
06:42: to the stockholder and so if we just
06:46: take the present value
06:48: of all the future dividends then we're
06:50: going to get the
06:52: value of the stock so that's
06:55: what the dividend discount model is
06:57: based on so also
06:58: on is just the present value of future
07:00: cash flows
07:02: and so the same thing over here on the
07:04: dcf model as well
07:06: just with the dcf model we're looking at
07:09: the
07:09: free cash flows of the company
07:13: which is representative of the company
07:16: company's operations
07:18: and so it's it's made off of the assets
07:21: debt plus equity and so ultimately we
07:25: get
07:25: uh the value of the entire business by
07:28: doing this discounted cash flow approach
07:30: and then we back out the equity or stock
07:33: value
07:34: based upon that
07:38: so we're going to be digging in this
07:40: lecture series into the cost of capital
07:43: estimation and i just quickly mentioned
07:45: before
07:46: uh that what we're trying to get to is
07:48: the weighted average cost of capital or
07:50: the whack
07:52: and within that we have two components
07:55: the cost of equity and the cost of debt
07:58: for the purpose of this lecture series
08:00: we're going to
08:01: ignore preferred stock it is generally a
08:03: very small
08:04: proportion of any company's capital
08:06: structure
08:08: and so it's not going to make much of a
08:09: difference
08:11: but you could apply similar approaches
08:13: there just if you value
08:15: the market value and the cost of uh
08:18: preferred stock then you could work that
08:20: into this same structure
08:22: we're just going to focus on the equity
08:24: in the debt so we're gonna have to
08:25: figure
08:26: out the costs of each of those
08:29: as well as the market value
08:33: of each of those to be able to put them
08:34: together into the whack
08:38: as far as the um cost of equity
08:41: we're going to look at using the capital
08:43: asset pricing model or
08:45: cap m for that approach
08:48: though there are certainly other valid
08:50: models that we could use
08:52: um and cost of debt we'll look at
08:56: a couple different approaches but the
08:58: most common approach is to look
09:00: at interest payments uh
09:03: on the balance on the income statement
09:06: to determine that
09:10: and getting into free cash flow
09:13: estimation
09:14: so there um we have to start by looking
09:17: at
09:17: historicals calculating historical
09:21: uh free cash flows and then we go
09:25: to project the future cash flows
09:28: uh which is typically done by projecting
09:31: the financial statements themselves
09:33: into the future and then calculating the
09:35: free cash flows
09:36: from those projected financials
09:40: so that's a quick overview of everything
09:42: that we're going to be doing in the dcf
09:44: model and then for the rest of this
09:46: lecture series we're going to focus
09:48: on digging into this whack
09:51: and how we can estimate the cost and
09:55: market values
09:56: of equity and debt and put that together
09:59: into the whack we're also going to look
10:02: at
10:02: this uh remaining uh
10:06: enterprise and stock value portion of
10:09: the model and see what we need to do
10:11: there as well
10:13: so thanks for listening and see you next
10:16: time

00:03: hi everyone
00:03: this is nick diabetis teaching you
00:05: financial modeling today we're going to
00:07: be talking about enterprise value
00:09: and equity value in the context of the
00:12: dcf model
00:13: this is part of our lecture series on
00:16: the discounted cash flow
00:17: valuation focusing on the cost of
00:20: capital side of the model
00:24: so we already gave a quick introduction
00:26: of the dcf
00:28: and now we are getting into
00:31: the enterprise value and equity value
00:35: portion of the model so
00:40: in that um we have to
00:43: disentangle what is enterprise value
00:45: versus equity value
00:47: so the enterprise value is the
00:50: value of the entire business and so you
00:53: can think of it as
00:55: the value of the assets in terms of
00:58: you know the basic accounting equation
01:01: assets equals liabilities plus equity
01:04: so it's the whole asset side and so that
01:06: means it's also
01:07: liabilities plus equity or you can think
01:10: of it as
01:11: the acquisition cost so if you are
01:14: someone that wants to purchase
01:15: this entire company and own it outright
01:18: this
01:18: is how much it would cost you to
01:20: purchase that
01:23: so in terms of the formula that we can
01:26: use
01:27: to define the enterprise value and
01:29: related to the equity value
01:31: we have that the enterprise value equals
01:34: equity value
01:35: plus that value minus cash um
01:40: and then what is the equity and debt
01:42: value in here so this the equity value
01:46: is just the value from the stock
01:50: side of the company
01:53: so going back to assets equals
01:56: liabilities plus equity
01:57: you got to finance your assets with
01:59: either borrowing money the liabilities
02:02: or issuing stock uh the equity side
02:06: so um a company that only issues stock
02:10: and never issues
02:11: debt their uh all their
02:14: assets are going to be financed by
02:16: equity and so all of that is going to be
02:19: an equity value
02:20: but if company has
02:24: a lot of money to finance their assets
02:27: then the debt on the balance sheet
02:31: is representing the debt value
02:34: in the company it's the value of
02:37: the bonds and loans whatever
02:41: instruments they use
02:44: to finance their assets
02:47: so we can use this same formula
02:50: to back into the equity value
02:53: we just do a little bit of algebra flip
02:56: this and we would have
02:57: equity value is equal to enterprise
02:59: value minus debt value
03:02: plus cash um and that's what you can use
03:06: in the dcf model
03:07: to back out the equity value
03:12: uh where where a lot of students
03:14: struggle
03:15: with this definition is the fact that
03:18: we're
03:19: subtracting cash here um
03:22: you might think well cash is a good
03:24: thing so shouldn't it add to the value
03:25: of a company
03:27: and in order to help understand this
03:31: i like to think about these two possible
03:33: ways that we can conceptualize the
03:35: enterprise value
03:36: asset value or the acquisition cost and
03:39: within each one
03:41: there's a way we can understand why
03:44: cash is actually being subtracted to
03:48: determine the enterprise value
03:51: so under the asset value kind of
03:53: interpretation
03:55: that the you know enterprise value is
03:57: representing
03:58: the stockholders uh value as well as
04:01: the value of the loans so
04:05: for that we can think about two
04:06: hypothetical companies which
04:08: have the exact same operations
04:12: they produce the same cash flows they
04:15: everything is the same about these
04:17: companies uh
04:18: even their capital structure and
04:20: everything
04:22: and so they have the same exact
04:24: enterprise value right now
04:27: now say that one of them uh says
04:30: that i'm gonna go issue uh
04:33: 10 million dollars worth of stock and
04:37: so they issue the stock and then get
04:38: cash in return for that stock
04:41: so now they have uh 10 million
04:44: additional dollars of equity and also
04:47: 10 million additional dollars of cash
04:50: the asset side and so the balance sheet
04:52: still balances right
04:55: but if we said that cash should be
04:58: adding to the enterprise value
04:59: well then we would be adding 10 million
05:02: for the equity value and we would again
05:04: be adding another 10 million
05:06: for the cash on the balance sheet so now
05:09: even though these two companies are
05:10: still exactly the same in terms of their
05:12: operations
05:14: one just decided to raise money and just
05:16: hold it
05:17: all of a sudden it's worth 20 million
05:18: dollars more that doesn't make sense
05:21: uh but if the cash actually offsets
05:24: the value of the equity they just raised
05:27: they get 10 million dollars of equity
05:28: but they also have 10 million dollars
05:30: more of cash
05:31: then it washes out and the two companies
05:33: still have the same value
05:35: so that makes more sense now if the
05:38: company
05:38: took that cash and deployed it into
05:40: assets that are going to create value
05:42: for the company
05:43: then it can come back into the
05:44: enterprise value but they have to
05:46: actually use that cash for something
05:47: useful
05:49: in order to increase their enterprise
05:50: value
05:53: and then the other way we can think
05:54: about this is in terms of the
05:56: acquisition
05:57: price how much someone would have to pay
06:00: to own the company outright
06:03: so in that you can think well uh in
06:06: order to
06:08: uh own the company outright they have to
06:10: buy out all the stockholders
06:12: but they also have to buy out all the
06:13: debt as well and so that's why these two
06:16: are both getting added in
06:17: but then after they bought the company
06:19: they have the cash
06:21: uh from the company now in their
06:24: possession
06:25: and so that actually reduces the cost of
06:28: acquiring the company because they have
06:30: that cash coming back to them
06:32: um so either of these ways that we can
06:36: conceptualize the enterprise value
06:38: in both ways it makes sense for cache to
06:40: come negatively
06:42: into the equation
06:46: so that's how we can um
06:50: disentangle the enterprise and equity
06:52: value
06:53: and basically as you complete the dcf
06:56: model
06:57: uh you'll take the present value of all
06:59: the free cash flows
07:00: at the discount rate of the whack and
07:03: you'll be left with an enterprise value
07:05: from that
07:07: so then you can just reverse that
07:10: formula
07:10: to back out the equity or stock value
07:13: in order to get to the stock price
07:18: so that's enterprise value and equity
07:19: value and then the
07:21: lab exercise on this um
07:24: there are two different exercises
07:27: involved here
07:29: so this first one
07:32: you have some cash flows here
07:37: and you're basically trying to figure
07:40: out what is the
07:41: enterprise value you have cash flows and
07:44: you also have a weighted average cost of
07:46: capital
07:47: um and you want to get ultimately to
07:50: both the
07:50: enterprise value as well as the stock
07:52: value
07:53: so you can reverse
07:57: this formula to get to the equity value
08:00: from the enterprise value
08:01: and then if you divide the equity value
08:03: by the number of shares outstanding
08:05: you'll be left with the stock price
08:09: so that's the uh
08:12: first exercise here and the second one
08:15: um
08:16: is similar we're also trying to give the
08:19: enterprise value
08:21: as well as the stock value um
08:24: with a slightly different situation
08:28: so that covers enterprise value and
08:31: equity value
08:32: in the dcf model thanks for listening
08:35: i'll see you next time

00:02: hey everyone
00:03: this is nick dear burtis teaching you
00:05: financial modeling today
00:06: we're going to be doing an introduction
00:08: to cost of equity estimation
00:11: this is part of our lecture segment on
00:14: the dcf model
00:16: focusing on the cost of capital side of
00:18: the model
00:20: so we already gave a quick introduction
00:23: to the dcf model
00:25: and its various parts and then we talked
00:28: through
00:28: enterprise and equity value now we're
00:31: getting
00:32: to the cost of equity estimation
00:37: so we're going to focus here on using
00:40: the
00:40: capital asset pricing model or cap m
00:43: as our model for estimating the cost of
00:46: equity
00:48: now it's definitely not the only model
00:50: we could have used
00:51: uh it does tend to be the most basic
00:55: model that can be applied
00:59: and we're just focusing on the basic
01:01: approach here
01:02: because once we learn the general
01:05: way that we can go about this then
01:09: it is not too difficult to expand into
01:12: using more complex models
01:15: so the cap-m was kind of the dominant
01:19: asset stock pricing model
01:24: from the 60s uh and going into the 70s
01:27: and then in 1976
01:31: we had the arbitrage pricing theory
01:34: come along and that really spawned a
01:37: whole new era
01:38: of models being used to
01:42: look at the returns in stocks
01:46: so that's where you'll have models like
01:49: the pharma french factor models
01:52: and many many other factor-based models
01:54: are coming out of this
01:56: arbitrage pricing theory um
01:59: and so you can definitely expand into
02:01: using those other models
02:03: like palma fringe three five factor or
02:06: whatever other factor models
02:09: you would just need to have data on
02:10: those factors and otherwise you would be
02:13: following
02:13: a similar approach to what we're going
02:15: to do here with the cap
02:17: out but with the kappa
02:21: the general idea is
02:24: that here we have an equation
02:27: where the return on the
02:31: stock is being explained by
02:34: the risk-free rate plus
02:37: a beta coefficient which represents the
02:40: covariance
02:41: of stock this stocks returns with
02:45: the market risk premium
02:48: we multiply that beta by the market risk
02:51: premium
02:51: which is the average return on the
02:54: market
02:55: minus the risk-free rate
02:58: and then we also have an error component
03:01: in here in the model representing that
03:04: this is not going to be exact we're
03:05: never
03:06: going to be able to predict stock
03:08: returns exactly
03:10: if i could do that then i would be very
03:13: rich
03:14: but no one can predict stock returns
03:16: there's always randomness
03:17: involved in them and that is picked up
03:20: by this last
03:21: term here
03:24: um and because it's random
03:28: sometimes it'll be positive sometimes
03:30: it'll be negative and so we just assume
03:31: that on average
03:32: this term is actually zero
03:37: so the approach we're going to use here
03:40: is to estimate the model on historical
03:43: data
03:44: and then assume that that
03:48: the the parameters we fit from the model
03:51: are going to apply in the future as well
03:56: and ultimately uh this ri
04:00: after we use our our parameter we
04:03: estimated from historical and now we're
04:05: calculating the future the ri is going
04:07: to be
04:08: the return on the stock or the cost of
04:11: equity for the company
04:15: those two are actually one of the same
04:18: the return on the stock and the cost of
04:21: equity
04:21: for the company you can think of
04:26: them as just being on opposite sides but
04:29: they're the same thing
04:31: um every uh
04:34: bit of money that the investor earns is
04:37: a cost
04:38: for the company and so those rates are
04:39: going to be the same
04:44: so um getting more specifically
04:48: into the estimation of this
04:51: so looking at historical data we're
04:54: going to have
04:55: historical stock prices and from those
04:58: we'll calculate historical returns
05:00: and we'll have that for our stock as
05:03: well as
05:04: for some security which represents the
05:08: market
05:08: typically the s p 500 is a common
05:12: security that people use for that
05:15: so we have historical prices on each of
05:17: those then we get historical returns
05:19: on each of those and so
05:22: uh we're gonna have the historical stock
05:25: returns coming over here
05:27: uh on the left and then uh we'll have to
05:30: use
05:31: an estimate of the risk-free rate that
05:33: can either be
05:34: uh using the historical risk-free rate
05:39: getting data on treasuries in order to
05:43: estimate that
05:43: or you can just put a static single
05:46: risk-free rate in there that you think
05:48: is representative of the time period
05:52: and then the historical market returns
05:55: uh the s p
05:55: 500 or whatever security you're using
05:57: are going to come in as this rm
06:00: so then the things that we don't know
06:02: are this veda
06:04: and this epsilon the epsilon
06:08: we said sometimes it's it's going to be
06:10: positive sometimes it's going to be
06:11: negative on average it should be zero
06:13: and so we're just going to ignore this
06:16: um
06:17: and just deal with this part of the
06:19: model
06:20: so then we have just one parameter here
06:23: that we're going to estimate and that's
06:25: our goal of
06:26: fitting this model on historical data is
06:29: then to estimate
06:30: this beta um
06:34: so we estimate that beta based off the
06:37: historical data
06:39: and then in order to predict the cost of
06:42: capital
06:42: going forward we have to make an
06:46: assumption
06:46: and that's that the beta that was there
06:49: and the historical data
06:50: is going to be the beta going forward
06:54: so if you have very strong opinions
06:56: about this you could adjust
06:59: the beta in your model if you think that
07:01: the future is going to be substantially
07:03: different than the past
07:06: data is typically thought of as a
07:09: measure
07:09: of systematic risk
07:12: because it's measuring how much this
07:15: company moves
07:16: with the overall market
07:19: so uh you know a beta of two basically
07:22: means that if the market's going up one
07:24: percent then the stock is going up two
07:25: percent
07:27: and if the market goes down one percent
07:29: then the stock is going down two percent
07:32: um and so it's kind of a multiplier on
07:34: the market's returns
07:36: making it into a piece of our overall
07:39: stocks return
07:41: now when the market goes up one percent
07:43: this stock's return even if it has a
07:44: beta two is not necessarily going to be
07:46: two percent
07:47: and that's because of this epsilon term
07:50: it could be higher or lower than that in
07:52: any given time period
07:54: but on average basically uh it's
07:56: governed by the data
07:58: that it should be moving around a
08:00: multiple
08:01: of the markets returns
08:05: um so if you
08:08: think that the company has just taken on
08:12: new opera they just started a new
08:13: business line which is going to be
08:15: substantially more risky
08:17: than their existing business you might
08:20: estimate a historical beta of one but
08:22: you might
08:23: adjust it upwards to 1.1 or 1.2 or
08:26: something like that in order to reflect
08:28: that well there's going to be additional
08:30: risk in the company going forward
08:32: compared to before
08:35: and so you don't just have to take the
08:37: historical one and use it directly but
08:40: that's
08:40: that's the most common is that you don't
08:42: have any new information on whether the
08:44: risk is going to be higher or lower
08:46: and so you just use the historical beta
08:50: um and
08:54: the way that we're going to estimate
08:56: that beta is
08:57: through a regression approach
09:00: um so you know the regression
09:04: is a classic y equals mx plus b
09:08: um and so
09:11: uh the risk-free here is the b the
09:14: intercept
09:15: um and the uh
09:19: b here is the m in the mx and the market
09:22: risk premium
09:23: is the x so this is the exact format
09:26: of a regression equation and so we can
09:29: fit
09:30: a regression on these data in order to
09:33: determine the beta so that's
09:37: what we're going to look at in the next
09:39: two videos
09:40: accomplishing that in both python
09:43: and excel so thanks for listening and
09:46: i'll see you next time

00:04: hey everyone this is nick diabetis
00:06: teaching you financial modeling
00:07: today we're going to be talking about
00:09: estimating the cost of equity
00:11: in python using historical stock returns
00:15: this is part of our lecture segment on
00:16: the dcf model focusing on the cost of
00:19: capital
00:20: side of the model so we've already
00:24: given a general introduction of the dcf
00:26: model
00:27: and its various parts we talked through
00:29: the enterprise value and stock value
00:31: portion
00:32: and we also gave an introduction on
00:35: estimating the cost of equity in general
00:37: kind of the conceptual framework behind
00:39: it
00:40: so make sure that at least you've seen
00:42: that
00:43: general cost of equity introduction
00:45: before coming into
00:47: estimating this cost of equity in python
00:52: so we can go ahead and go over to
00:56: the example jupiter notebook for this
00:58: all of these
00:59: uh materials are there on the course
01:02: site as well
01:05: so uh here's the
01:08: duper notebook focused on estimating
01:11: the cost of equity so here as explained
01:15: in the prior video
01:16: we are using the cap model to
01:19: [Music]
01:20: estimate this future cost of equity
01:25: and to do that we're going to fit the
01:27: model with historical data
01:29: and then use the fitted model to then
01:33: predict the future
01:36: so the first thing that we're going to
01:40: do
01:40: is read in the data that we need to use
01:43: so
01:44: i provided this price data file
01:48: which has the price on the market
01:52: portfolio that could be something like
01:54: an s p 500
01:56: or russell 2000 or
01:59: some kind of index which captures a
02:02: large portion
02:03: of the market and then we have the price
02:06: of the asset or stock that we're
02:09: interested
02:09: in determining the cost for
02:14: and we have a 100
02:18: uh and one different prices here
02:22: for each of the two assets
02:26: so now that we've loaded in this data
02:28: into the data frame
02:30: now we can go about calculating the
02:32: returns because we have prices
02:34: but we need to work with returns in the
02:36: model
02:37: and so the return on a stock or any
02:41: asset
02:41: is just the percentage change in its
02:43: price new minus old overall
02:46: um and thankfully in pandas we don't
02:49: even need to know that
02:51: the formula for percentage change so
02:53: hopefully
02:54: you do know that um but we have this
02:57: percent change method
02:58: on the data frame so you just do dot
03:02: percent change and that's going to
03:05: do that calculation for you on every row
03:08: of every column so that's
03:10: really nice when you just have a data
03:12: frame full of prices you can convert it
03:13: into a data frame
03:15: full of returns with just a single
03:17: command
03:21: and you'll notice that the first row
03:23: here has these
03:24: nans so nan means not a number
03:27: it is the representation for a missing
03:30: value
03:31: in penis this is just saying that there
03:33: was no way to calculate
03:35: the these values because the percentage
03:38: change is new minus
03:39: old over old and for the first row we
03:42: don't have any old we only
03:44: we don't have any prior price to look at
03:46: to calculate the return and so there's
03:48: no
03:48: return for the first period
03:55: um but we do get the returns going all
03:59: the way
04:00: to the end of the data set just fine
04:06: so in the cap model
04:10: we have this market risk premium portion
04:12: here the return
04:14: on the market minus the risk-free rate
04:18: and so we're going to need to calculate
04:19: that in order to
04:21: run the regression because we can think
04:23: of
04:24: a simple linear regression in this
04:26: format
04:28: and so here the uh rate on the market
04:32: minus the risk free is becoming the x
04:35: and this equation so we need to just
04:37: calculate that first
04:38: so that we can use that as the x
04:42: so we're just gonna assume here that the
04:45: risk-free rate is three percent
04:47: uh another approach here is to actually
04:50: collect data on
04:51: treasuries and use historical treasury
04:54: rates
04:54: as the risk free rates and then it could
04:56: vary in each period
04:58: uh but for simplicity here i'm just
05:00: going to use three percent
05:03: which is quite high in 2020 but
05:06: more have more normal rate in normal
05:09: times
05:10: um so to
05:14: come up with that uh market returns
05:16: minus first free well we just take the
05:18: the market market portfolio return
05:21: column
05:22: and subtract the risk free and we can
05:24: save that into a new column
05:26: in the data frame and now we have this
05:29: market risk premium
05:31: column which is just the
05:34: market return minus the risk free and be
05:38: careful about your units here this
05:39: is a percentage and so it should be in
05:41: decimal format
05:46: so now we have the setup that we need in
05:49: order to go
05:50: and run the capem on the historical data
05:54: through an ols regression
05:57: so to run a regression in python we can
06:00: use the stats models package
06:03: so here is kind of the standard
06:06: convention
06:06: for the import so most code samples you
06:10: see online are doing this
06:12: import sas models dot api as sm and then
06:15: we use
06:15: sm dot for everything
06:18: um and it's very important that you
06:22: uh include the constant in this
06:25: regression
06:26: with stats models you do have to
06:28: explicitly tell it to include the
06:29: constant
06:31: um because if you look
06:34: at the format of the cap-m the risk-free
06:38: rate here
06:39: is the interceptor constant and so if we
06:42: estimated it without
06:44: then we would not be appropriately
06:47: estimating the cap
06:48: m and so we need to have that intercept
06:52: um and so we add that here to the market
06:56: risk premium
06:57: and the y is going to be the price of
07:00: the returns on the stock
07:03: and then we can estimate the model um
07:06: but lotus as was already there
07:09: uh we have this missing data error
07:13: coming up that
07:16: exogenous which means the x variables
07:20: contains either infinity or missing
07:23: values which of course we do have
07:26: missing values we saw that before
07:29: the first row has missing values
07:33: and stats models cannot directly just
07:36: work
07:36: with those missing values so what we
07:39: have to do
07:40: is drop those missing values so this
07:44: drop n a command
07:46: on a data frame is going to remove
07:50: any rows which have missing values
07:54: and there are options on it you can say
07:57: uh
07:58: default is if there's anything missing
08:00: in the row it will drop it
08:01: you can also do how equals all and then
08:04: it's
08:04: uh only if everything is missing will it
08:06: drop it
08:08: but here we do want any because which is
08:10: the default
08:11: because uh any missing value
08:14: is going to mess up the estimation so
08:18: after dropping and you can see that the
08:21: first row here
08:23: is what the second row was before
08:27: we have just eliminated this first row
08:30: because it was all just
08:31: missing values so and then we saved that
08:35: back
08:36: into the returns data frame you do have
08:39: to
08:40: assign it back otherwise it's not going
08:41: to take effect
08:43: in the original data um
08:47: so now that we have removed those
08:50: missings let's go ahead and try
08:52: this regression again and this time it
08:54: works just fine
08:56: so we see all the standard uh regression
09:00: summary output all the fit statistics
09:03: and then here is what we're really
09:05: interested in
09:07: the coefficient on the market risk
09:09: premium
09:10: is the beta which was that main
09:13: parameter that we're trying to estimate
09:14: through this approach
09:16: and we see here that the beta is 0.8338
09:19: so this is uh generally less risky
09:23: than the overall market in terms of
09:25: systematic risk
09:28: um so we're going to want to use that
09:32: beta in a calculation
09:34: and in stats models we have this uh
09:37: results.params which is a series
09:40: and a series of the coefficients so
09:42: we've got
09:43: a constant and you'll notice that this
09:45: estimated to be
09:46: uh very close to our three percent
09:50: risk-free rate so that's a really good
09:52: sign that it came up
09:53: as three percent it should not be too
09:55: far from
09:56: uh your risk-free rate and then
10:00: uh we've got the beta here as well so we
10:02: can pull the mrp
10:05: uh coefficient out of that which is the
10:07: beta
10:08: so then just saving that into a variable
10:10: now we've got beta into a variable and
10:12: can use it
10:13: to estimate the future
10:16: market return very sorry to estimate the
10:19: uh cost of equity going forward
10:22: um so again the capital formula now
10:25: we've estimated it and gotten the
10:26: historical
10:27: beta and we're going to assume that beta
10:29: applies in the future as
10:30: well and so now we want to plug that
10:34: beta end
10:35: along with the projected risk-free rate
10:38: going forward and the projected market
10:40: return
10:41: going forward to ultimately come up with
10:44: the cost
10:45: or rate of return on this stock
10:49: so we know the beta and we can just
10:52: assume that the risk free is going to be
10:53: the same as it was
10:55: so then what we do still need to get is
10:58: the market return to plug in here
11:00: so we can just take an average of all
11:03: the historical
11:04: returns and use that as an estimate for
11:07: the future
11:09: now you can definitely adjust this that
11:11: would be kind of like the baseline case
11:13: to just
11:13: take it as it was but then if you think
11:17: a recession is coming up or we've just
11:20: entered a recession
11:21: then it could be lower than this you
11:23: could adjust it downward
11:25: if we're an expansion economy uh then
11:28: you could adjust it upward
11:30: and certainly with uh some kind of uh
11:34: sensitivity analysis or scenario
11:36: analysis or monte carlo simulation you
11:38: can vary
11:39: these kinds of things to understand
11:42: what's going to happen to the cost of
11:43: equity cost of capital ultimate value of
11:46: the stock
11:47: when we change our assumptions about
11:50: what's going to happen in the future
11:54: but here we're just going to use the
11:55: historical assume that that is going to
11:57: apply in the future
12:01: so then we can go ahead and estimate the
12:04: model to get the cost of equity
12:07: going forward so all we do is just plug
12:10: everything into the left hand
12:11: or right hand side of the model again
12:14: assuming this
12:15: epsilon is zero uh so we're going to
12:18: plug the risk free
12:19: plus the beta that we estimated times
12:22: the market return
12:24: that we estimated as the average of
12:25: historical minus that risk-free again
12:29: and then we calculate that and we get
12:32: the overall cost of equity that we can
12:36: go
12:36: and then use in calculating the whack
12:39: which will ultimately let us do the
12:42: discounted cash flow
12:43: valuation of a stock
12:49: so that's what's involved and estimating
12:52: the cost of equity
12:54: and python using the cap m and then if
12:57: you wanted to use a different model
12:58: other than the cap
12:59: m such as maybe like a power fringe
13:02: three factor model the only thing that
13:06: would change here
13:07: is when you uh you would have to collect
13:11: data on whatever factors you want to use
13:14: and then you would uh include those
13:17: in the regression as well as other x
13:20: variables
13:21: and you would also estimate the
13:22: coefficients on those
13:25: and then you would also have estimates
13:27: of those factors
13:28: going forward which would probably be
13:30: based off of
13:33: um averages of the factors historically
13:36: and you could adjust those and
13:39: then you would just plug everything in
13:41: here at the end so the
13:43: approach is going to apply no matter
13:46: which
13:46: type of model you're using you just need
13:49: to change a couple of steps a little bit
13:52: to apply it
13:52: with different stock pricing models
13:57: so that's everything involved here and
14:01: estimating this in python
14:03: in the next video we're going to come
14:04: back and cover handling it in
14:06: excel so thanks for listening and see
14:09: you next time

00:02: hey everyone
00:03: this is nick diabetis teaching you
00:04: financial modeling today we're going to
00:06: be talking about
00:07: estimating the cost of equity in excel
00:10: this is part of our lecture segment on
00:12: the dcf model focusing on the cost of
00:15: capital
00:15: side of the model so
00:19: we went through this uh intro on the dcf
00:22: model and talked about its various parts
00:24: we discussed enterprise and equity value
00:28: we discussed the general approach to
00:31: cost of
00:31: equity estimation and we also looked at
00:34: how to carry that out in python
00:36: as well so now we're coming to now
00:40: estimating that in excel
00:43: so we can jump over to the
00:46: excel workbook
00:50: so here we have the prices
00:53: on the market portfolio as well as the
00:57: prices on the asset
00:59: and we have the risk free rate as well
01:03: so the first thing that we want to do is
01:05: in order to fit the cap model we need to
01:07: be using returns
01:08: and not prices so we want to calculate
01:11: the returns
01:12: on the market and on the asset so
01:16: um add the market return
01:20: and asset return columns
01:25: and i'm going to start from the second
01:27: row because you can't
01:29: calculate a return on a single value
01:32: it has to be using this value and the
01:35: prior value
01:37: so a return is simply just percentage
01:41: change
01:41: in the stock price and so it's going to
01:44: be
01:46: new minus
01:49: old over old
01:53: i messed up my parentheses there make
01:55: sure you have parentheses around this
01:57: hop
01:58: um so that the order of operations is
02:01: maintained
02:04: and then this should work to calculate
02:05: the return we should get like
02:08: 17 or sorry 11
02:11: 17 yep um
02:17: then should be able to just uh complete
02:19: that down
02:22: that is still working properly and also
02:24: drag that over
02:26: that it's also working properly so now
02:30: we have the returns on both the market
02:32: and the asset
02:36: so then uh in the cap m formula
02:40: we have this return on the market minus
02:43: the risk-free rate the market risk
02:44: premium
02:45: that we need to use to estimate it so we
02:48: can calculate the market risk premium
02:52: as the return on the market
02:55: minus the risk-free rate and fix that
02:58: risk-free rate so that it can go all the
03:01: way down and still reference the same
03:03: rate
03:05: and there now we see this is working
03:08: appropriately
03:11: so now we have the market risk premium
03:15: so now we can go ahead and
03:18: estimate the regression which is going
03:21: to fit the
03:22: historical cap m model so
03:27: we can go to the data tab and
03:31: over to data analysis that is going to
03:34: be the area
03:35: um where we can find the regression
03:39: if you don't see this over here if
03:41: there's no analysis section at all
03:43: you need to enable the data analysis
03:45: tool pack
03:47: in order to do that it's just file
03:50: options
03:52: add-ins manage excel add-ins
03:56: and then you want to check this analysis
03:58: tool pack
04:00: and then it should show up over here
04:02: it's included on all excel
04:04: it just only shows up if you enable it
04:08: so then we can use this data analysis
04:10: and we want to pick regression
04:11: out of this list the regression
04:16: and the y is going to be
04:20: the market risk premium
04:24: or sorry the y is going to be the asset
04:26: prices
04:30: and the x is going to be the market risk
04:34: premium
04:37: and we can put this on a new worksheet
04:40: and here i do not have any labels
04:42: because we had this gap i started from
04:44: here
04:45: um so now we're ready to estimate it
04:49: then we see this uh new sheet open up
04:52: uh we can call this the cap m regression
04:58: and
05:01: we see the beta here as the coefficient
05:05: on the x
05:06: variable so we can see that it's the
05:08: same
05:09: as the one that we had estimated in
05:11: python as well
05:14: and i'm just going to bring that back
05:16: over to this sheet
05:18: for our calculation of
05:21: the cost of capital going forward
05:27: so now we have the beta
05:31: now in order to estimate the future
05:35: cost of capital we also need to know the
05:38: market return
05:40: that we're going to plug into the model
05:42: so
05:43: we can
05:46: calculate the market return as the
05:49: average
05:50: of the historical market returns
05:53: so simply just taking an average
05:57: of the market return column
06:00: will be enough to give a good estimate
06:02: for the market return
06:05: uh now you can always adjust this as
06:09: well as the beta
06:10: based on your expectations about the
06:13: future
06:14: and i spoke about this concept more in
06:17: the prior two videos going over the
06:19: overview of cost of equity and on the
06:21: python side
06:23: um but taking the historical average
06:27: uh is basically our baseline kind of
06:30: estimate for the future and it can be
06:32: adjusted if you think that the future
06:34: is going to be substantially different
06:36: from the past
06:40: so now we have all the component pieces
06:42: that we need to be able to estimate the
06:44: cost of equity
06:45: going forward
06:49: so in order to get the cost of equity
06:51: remember that in the capital formula
06:53: it's the
06:53: first free rate plus the
06:57: beta times the
07:00: market risk premium and the market risk
07:02: premium is the market return
07:05: minus the risk free rate
07:08: so that's all that we have to do to get
07:11: an estimate
07:12: of the cost of equity in
07:15: excel calculate the returns calculate
07:17: the markers premium
07:19: run the regression of the asset returns
07:22: on
07:23: the market risk premium take the beta
07:26: from that
07:27: calculate the historical market returns
07:28: and put it all together
07:30: into the cost of equity estimate for the
07:32: future
07:35: so then in order to wrap up
07:39: this portion of the lecture on cost of
07:42: equity
07:43: uh we also have a lab exercise here and
07:46: so there's some price data on the course
07:49: site you can go ahead and download that
07:52: consumer history rate of two percent you
07:54: want to come up with the beta
07:56: and cost of equity um
08:00: and then i'm also getting into this uh
08:03: you should be thinking about adjusting
08:04: these things if necessary
08:06: so imagine that it's going to recession
08:08: in the future and so the market returns
08:10: gonna be three percent lower
08:12: what would be the new cost of equity
08:15: um and for this exercise feel free to
08:18: use
08:18: either excel or python whatever you
08:21: would prefer
08:23: and the answers are provided for you
08:26: here as well
08:30: so that's everything involved in excel
08:34: cost of equity estimation and the lab
08:36: exercise on this material
08:38: so thanks for listening and i'll see you
08:40: next time

00:03: hey everyone
00:04: this is nick dear burtis teaching you
00:06: financial modeling
00:07: today we're going to be learning about
00:09: how to calculate the market value of
00:11: equity and this is part of our lecture
00:14: series
00:15: on the discounted cash flow valuation
00:17: model focusing
00:18: on the cost of capital portion of the
00:21: model
00:23: so we have already discussed
00:26: the overview of the dcf model and
00:30: how to work with enterprise value and
00:32: equity value
00:34: and then we also estimated the cost of
00:36: equity so now we're coming to
00:39: estimating the market value of equity
00:41: because ultimately to
00:43: create the whack we're going to need the
00:46: cost and market values
00:47: of both equity and debt
00:51: so coming to the market value of equity
00:55: we are going to need this basically in
00:58: order to
00:59: create the weights and the whack formula
01:03: so the weights are taking
01:06: the market value of equity and dividing
01:08: it by the total capital that would be
01:10: the equity weight
01:11: and the debt weight would be the market
01:12: value of debt divided by the total
01:14: capital
01:15: and the total capital is just the sum of
01:18: the market values of equity and debt
01:21: as long as we're ignoring preferred
01:23: stock which
01:24: for the purposes of this lecture we are
01:26: and it tends to be a very minor portion
01:28: of the capital structure
01:32: so if your company is publicly traded
01:35: it is very straightforward to calculate
01:39: the market cap or market value
01:42: of the company because it's already
01:46: publicly available information you just
01:49: look in the market since it's publicly
01:52: traded
01:53: and there you'll be able to see the
01:55: number of shares outstanding
01:57: as well as the current share price and
01:59: when you multiply these two things
02:00: together you get the market
02:01: capitalization
02:03: which is the same as the market value of
02:07: equity
02:08: so that's all it takes for a publicly
02:11: traded company
02:14: now if your company is not publicly
02:17: traded
02:17: that's where this can become a
02:20: challenging exercise
02:22: so if your company
02:26: is not publicly traded but they are
02:29: well established and have
02:32: competitors which are fairly comparable
02:36: a lot of people will use comparable
02:37: based approaches
02:39: to calculate the market value of equity
02:42: basically look at the competitors which
02:44: uh you would find one that is publicly
02:47: traded or
02:47: hopefully multiple that are publicly
02:49: traded and look at the market value of
02:52: those companies
02:53: and then compare the company that you
02:56: ultimately want to get the market value
02:57: for
02:58: to these companies along different
03:00: dimensions
03:01: to try and back into the market value of
03:04: this company
03:05: for example if a competitor has 10
03:08: billion dollars of market value
03:11: and they have a 10 market share
03:15: and the structure of this company is
03:17: very similar to the one that you're
03:18: trying to value
03:20: and the one that you're trying to value
03:22: has a five percent market share
03:25: and you see that the profit margins are
03:27: similar across the two companies
03:29: then it might be reasonable to conclude
03:31: that the value of
03:33: the company that you're trying to value
03:35: the market value of equity is half
03:38: of the market value of this other
03:40: company so 5 billion
03:42: so these are the kinds of thought
03:44: exercises that
03:46: people go through to be able to come up
03:48: with the market value for
03:50: non-public companies uh by looking at
03:54: comparables
03:56: sometimes you're not even gonna have
03:58: comparables
03:59: especially for very early stage
04:00: companies
04:02: they might be coming up with some new
04:05: technology
04:06: that nobody else has created before how
04:08: do you come up with a comparable for
04:10: that you can't
04:12: and so for those companies
04:15: luckily most of the time those early
04:17: stage companies are not even going to
04:19: have
04:19: debt in the capital structure they're
04:21: going to be 100
04:22: equity uh financed and so to come up
04:26: with their whack it's 100
04:27: equity you only need to consider the
04:29: cost of equity you don't even
04:30: need the market value of equity to
04:32: calculate the whack
04:34: so hopefully that's the case but if your
04:37: company
04:38: is private has no comparables
04:41: and also has debt in the capital
04:44: structure
04:45: then all you can really go off of is
04:48: trying to look at the
04:51: the business and trying to estimate
04:54: the value based on the present value of
04:58: future cash flows
05:00: which of course is quite a bit more
05:02: involved than these other approaches
05:05: but again it's pretty rare that you need
05:08: to do that because these early stage
05:10: companies where you don't have
05:11: comparables
05:11: typically do not carry debt and so you
05:14: don't need to go through this exercise
05:17: but as far as this class is concerned
05:19: and probably most of the time that
05:20: you're going to be
05:22: calculating the whack for a company it's
05:24: going to be a public company
05:25: and in that case just use the market cap
05:28: as
05:29: the market value of equity and you're
05:31: done
05:33: so that covers market value of equity in
05:36: the context of calculating rack
05:38: so thanks for listening and see you next
05:42: time

00:02: hey everyone
00:03: this is nick dear burtis teaching you
00:04: financial modeling
00:06: today i'm going to be talking about
00:08: estimating the cost of debt
00:11: and this is part of our lecture series
00:13: on the discounted cash flow valuation
00:15: model focusing
00:16: on the cost of capital side of the model
00:20: so we have just finished talking about
00:24: the equity
00:25: side of the cost of capital um and now
00:28: we're headed
00:29: into the debt side starting with the
00:32: cost of debt
00:33: and in the following video we'll talk
00:34: about the market value of debt
00:38: so for estimating the cost of debt
00:42: what we're trying to do is determine
00:46: what is the uh percentage
00:49: uh interest rate basically that would
00:52: have to be
00:53: offered on one dollar of additional debt
00:57: issued right now so this
01:00: is the marginal cost of debt that we're
01:03: trying to estimate
01:05: not the historical because we're
01:07: concerned
01:08: about how things are currently the risk
01:12: with the company
01:13: could have changed that changed the cost
01:15: of debt
01:16: and we're considered about the current
01:18: state of the company and so that's why
01:20: we're looking at the marginal
01:22: or cost of raising one additional dollar
01:26: instead of just thinking about the
01:28: historical
01:31: and in estimating this we will discuss
01:34: here two general approaches the
01:37: financial statements approach
01:39: and the market rate of bonds approach
01:43: and each the two approaches require
01:46: different information and so you may be
01:49: forced into one or the other
01:52: but we'll look at both of the approaches
01:55: and
01:56: uh generally the market rate of bonds
01:59: approach
02:00: would be a better approach to take
02:02: because
02:03: that is more directly targeting the
02:06: marginal
02:07: or forward-looking rate of interest
02:10: rather than the financial statements
02:12: approach which
02:14: is only considering the historical
02:18: so the market rate of bonds approach
02:21: basically
02:22: we're going to look at bonds which are
02:25: currently outstanding for the company
02:27: and use the rate on those to determine
02:30: the cost of debt
02:33: whereas with the financial statements
02:34: approach we're going to look at the
02:36: balance sheet
02:37: and so that's always going to be a
02:39: historical look
02:41: at the cost of debt which is not going
02:43: to be able to adjust
02:45: for any recent changes in the cost of
02:48: debt
02:48: since the last balance sheet period so
02:51: if this is a very stable company
02:54: the cost of debt is not changing much at
02:56: all
02:57: over time then the financial statements
02:59: approach
03:01: can get very close to the marginal cost
03:04: of debt
03:05: but in general the market rate of bonds
03:07: approach
03:08: is going to be a better approach but it
03:11: does
03:11: require that information on currently
03:14: outstanding
03:14: bonds for the company and their prices
03:20: so looking more specifically at the
03:22: financial statements
03:23: approach so again we're using the
03:27: balance sheet data
03:28: in order to calculate the cost of debt
03:31: based on the historical information
03:34: and here more specifically
03:37: we are going to take uh well
03:41: interest expense from the income
03:42: statement so it's income statement and
03:43: balance sheet
03:46: so interest expense from the income
03:48: statement and total debt
03:50: from the balance sheet and
03:53: we just have a simple formula then to
03:56: estimate the cost of debt
03:57: which is just that interest expense
04:00: divided by
04:01: the total debt just saying that well if
04:04: you borrowed a thousand dollars
04:06: and you paid a hundred dollars in
04:08: interest
04:09: 100 over a thousand you paid 10 interest
04:13: on the debt that you hold
04:16: now um there's definitely flexibility
04:21: in this measure as far as
04:24: what you count in the total debt
04:29: most commonly people uh just take
04:31: short-term debt and long-term debt and
04:33: add them together to get the total debt
04:37: and often a total debt line item will be
04:40: provided
04:40: on the balance sheet and you can use
04:42: that
04:44: but it's it's you know up for debate and
04:46: discussion whether
04:48: other other sources of debt such as
04:52: trade credit or operating leases
04:56: or things like that should be considered
04:59: in here
04:59: as well but generally most people
05:02: consider uh just the short-term and
05:05: long-term debt
05:07: as the total debt for this calculation
05:12: and you would do this for the most
05:15: recent data available
05:16: and use that as the cost because
05:20: the most recent should be the most uh
05:24: appropriate to estimate the future
05:27: now if you calculate it over time
05:30: and this most recent period is very
05:33: different than the prior periods
05:36: uh then you're gonna have to investigate
05:38: further into the company as to why
05:40: that's the case
05:41: and make a call on is this relevant to
05:44: the future or should i use uh some of
05:48: the older historical periods in order to
05:50: estimate that so
05:52: this can take additional research to
05:54: understand
05:57: which you know how you should
05:59: extrapolate into the future based on
06:01: that historical information
06:04: but as the general approach you would
06:06: just take the most recent data
06:07: available and use that
06:11: so then moving on to the market rates of
06:14: bonds approach
06:16: so again we're trying to get at what is
06:19: the marginal cost of debt or the cost
06:21: to raise new debt and so if we're
06:25: looking at
06:26: what's currently out there in the market
06:28: and how
06:29: those market bonds prices have adjusted
06:33: in response to the changing risk of the
06:36: company
06:37: that's going to give us a good picture
06:39: of the cost of debt today
06:41: for the company
06:44: so the way that we look at that with the
06:47: currently outstanding bonds
06:49: is that we look at the yield of maturity
06:52: on those bonds
06:54: so the yield of maturity is a measure
06:57: of the rate of return on investing in a
07:00: bond
07:02: and it can also be thought as of
07:05: as the company's uh cost to issue a new
07:09: bond
07:10: if they would issue a new bond uh it is
07:14: very typical for companies to issue
07:16: bonds at par
07:18: which means that the coupon rate
07:21: on the bond is equal to the yield
07:25: to maturity and so the interest rate
07:28: that the company is paying
07:30: is equal to that yield current yield of
07:32: maturity
07:34: um now when we look at the historical
07:36: bonds
07:38: the company is going to pay whatever the
07:40: original interest rate was on that bond
07:42: that's not going to change throughout
07:43: the life of the bond
07:44: but the yield does change because the
07:47: price of the bond changes
07:49: and that yield tells us currently what
07:52: is the cost of debt
07:53: for the company
07:58: so we can think of
08:01: the sealed maturity as both
08:04: the rate of return required rate of
08:07: return
08:08: for the investor in the bond as well as
08:11: the cost
08:12: of that bond for the company now again
08:15: looking at a historical basis they're
08:17: actually going to pay the contracted
08:18: rate of
08:19: interest on the bond the coupon rate um
08:23: but the yield to maturity is going to
08:25: adjust as risk on the company changes
08:28: and when the company issues a new bond
08:30: they're going to have to
08:31: issue the coupon rate matching with that
08:34: yield of maturity
08:35: so that's why we can think of it as the
08:37: cost for the company
08:40: as well as the required rate of return
08:43: for the investor
08:46: and to calculate the yield of maturity
08:48: it's simply just the internal rate of
08:50: return or irr
08:51: of the bond um
08:55: and considering that uh
08:58: bonds have very defined payment
09:01: structures with regular payments
09:04: you can use the
09:07: like you know five keys on the financial
09:09: calculator kind of approach
09:11: uh which you have uh also the
09:14: rate function in excel and numpy's
09:18: rate function in python so if you just
09:21: pass
09:22: uh the appropriate other inputs
09:26: uh you know the the time remaining on
09:29: the bond
09:30: the coupon payment uh the price of the
09:33: bond
09:33: the part value put all these things into
09:36: the rate function
09:37: in either excel or python and you'll be
09:40: able to get the yield of maturity
09:42: on the bond and then you can use that
09:45: yield of maturity
09:47: as the cost of debt now
09:51: in the following video we'll talk a
09:52: little bit about seniority and how
09:55: a company can actually have multiple
09:57: different costs of debt
09:59: but as far as we're focusing on this
10:02: course we'll just kind of simplify that
10:03: away
10:04: and just talk about it generally but not
10:07: handle it in the models
10:08: for this class and just use a single
10:11: cost of debt
10:12: for our models to keep them relatively
10:16: simple
10:19: one other thing that we have to consider
10:21: with the
10:22: cost of debt is that
10:25: in our tax system and in many tax
10:27: systems around the world
10:29: debt is tax deductible
10:33: that means that the company is
10:36: going to pay income tax
10:40: on the earnings before tax
10:43: and the interest has already been taken
10:45: out
10:46: or we get to that line item so
10:50: by paying interest you actually reduce
10:54: your income as it's being considered for
10:57: taxes
10:58: and so that reduces your tax payment as
11:01: well
11:04: so uh the way that we can
11:08: kind of think through this think about
11:10: two companies
11:11: which are exactly the same in terms of
11:14: their
11:15: operations and they bring in the same
11:17: revenues
11:18: same cost structure everything
11:21: one of these companies is
11:25: completely equity finance they issued
11:27: stock
11:28: in order to fund their operations
11:31: and then have just kept it growing by
11:33: the retained earnings in the business
11:36: and then the other one has 50
11:40: debt in its capital structure so it took
11:42: out
11:43: bank loans or bonds or
11:47: lines of credit or whatever in order to
11:51: finance half of their assets
11:54: and the other half is that same stock
11:57: issuance as the other company
12:00: so going down the income statement
12:03: they're both going to have the exact
12:04: same ebit so that's
12:07: uh basically the operating income
12:10: after we take the revenues take out all
12:12: the costs of operations
12:14: we're left with the ebit and so those
12:18: two numbers are going to be exactly the
12:19: same
12:20: across the two companies but then
12:24: uh for this 50 debt company
12:27: now they have an interest payment coming
12:29: out to service that debt
12:32: and so that uh ebt which is the next
12:35: line item we just subtract interest and
12:37: then get to ebt
12:39: uh that's going to be low for the debt
12:42: firm
12:43: and higher or the equity firm
12:47: and so now the equity firm is going to
12:49: pay a larger tax bill
12:52: than the debt firm
12:56: and therefore the
12:59: equity firm is going to have a lower
13:01: value than
13:03: the debt finance firm because the
13:06: operations are 100
13:08: the same but one of them had to pay more
13:10: taxes
13:11: and so that company is less valuable
13:16: and what this means going into our cost
13:20: of debt calculations is that
13:24: we can factor in this tax savings
13:27: into the cost of debt we can instead of
13:31: just taking
13:32: whatever the rate of interest is then
13:33: considering that the cost of debt
13:36: we can say well we know we're going to
13:37: get this tax credit because of the debt
13:41: and so we can take that out within the
13:44: cost of debt
13:45: in order to work that into the weighted
13:47: average cost of capital
13:50: so all we have to do there is multiply
13:54: the pre-tax cost of debt whatever rate
13:57: of
13:57: interest there is on the debt securities
14:01: and just multiply that by one minus the
14:04: tax rate
14:05: and that's going to tell us what the
14:07: after-tax cost of debt
14:09: is for the company
14:12: and that way we'll be taking into
14:15: account the fact that
14:16: companies with more debt all else equal
14:20: are going to be more valuable because
14:22: they actually have a lower cost
14:24: of capital due to the tax savings
14:28: so just make sure that when you go to
14:31: include the cost of debt
14:33: into the wac that you multiply that
14:35: pre-tax cost
14:36: by one minus the tax rate to adjust it
14:40: for that tax effect
14:44: so that covers cost of debt
14:47: and in the lab exercise on it we have
14:51: two different levels here
14:52: so the first level um
14:55: we just have some chemical manufacturer
14:58: and we have
14:59: details about a market bond
15:02: that they have outstanding and
15:07: we're trying to get the pre and post tax
15:10: cost of debt for this company
15:16: and then in level two i'm trying to get
15:20: you towards
15:21: uh doing more and more on your own
15:24: so for level two i'm gonna have you go
15:27: to
15:28: stockrow.com that is a good source
15:31: for uh financial statement information
15:35: go there and get walmart's financials
15:39: and then calculate the cost of debt
15:40: actually using those financial
15:42: statements in the financial statement
15:44: approach
15:46: and you're not going to just have a tax
15:49: rate there you're going to also have to
15:51: calculate
15:52: the effective tax rate uh using
15:56: the uh the income taxes that were paid
16:00: and the earnings before taxes
16:04: so and then you can feel free to
16:07: complete
16:08: either of these exercises in either
16:10: python or excel
16:13: so that covers cost of debt and
16:18: we will look in a future video about how
16:20: that works into
16:22: the weighted average cost of capital and
16:25: into the dcf model so thanks for
16:28: listening
16:28: and see you next time

00:03: hey everyone
00:04: nick dear burtis here teaching you
00:06: financial modeling
00:07: today we're going to be doing an
00:09: introduction to
00:10: the market value of debt how to think
00:13: about it
00:14: and how to calculate it and this is part
00:17: of
00:17: our lecture segment on the discounted
00:20: cash flow valuation model
00:22: focusing on the cost of capital portion
00:24: of the model
00:26: so we already talked about
00:30: the cost of debt in prior video
00:33: and now we are getting into the market
00:36: value
00:37: of debt because we need the costs and
00:40: market values
00:41: of each of equity and debt to ultimately
00:44: be able to calculate
00:45: the weighted average cost of capital or
00:47: whack
00:50: so
00:53: after a bond is issued its value can
00:56: change
00:57: over time and it almost certainly will
00:59: change over time
01:01: um and this is not a debt course
01:04: and so i'm not going to go into a ton of
01:06: detail here
01:07: um so definitely reference some
01:11: additional debt materials
01:12: if you haven't learned about any of
01:14: these concepts before
01:18: uh when we think about the value of
01:22: debt be it bond bank loan
01:26: whatever else we can determine its value
01:29: just like any other financial asset any
01:32: asset that pays cash flows
01:35: we can always just take the present
01:37: value of
01:38: those future cash flows and that will
01:40: get us the value of the asset
01:43: so for a bond it's going to be the
01:46: regular interest or coupon payments and
01:49: then the final
01:51: uh principal plus coupon payment in the
01:53: last period
01:55: that we use to determine the value of
01:57: the bond
02:00: but remember that when we take the
02:02: present value of something
02:04: it's both the cash flows as well as the
02:06: discount rate
02:08: that affect the value
02:11: and with bonds the
02:15: payments the interest coupon payments
02:18: are contracted at beginning
02:21: of the life of the asset and remain in
02:25: place
02:26: for the entire life of the asset a bond
02:29: is just a contract
02:31: which says that the company is going to
02:33: pay
02:35: a certain amount of interest in return
02:37: for a loan
02:38: and then they're going to return the
02:40: money at the end
02:42: um and so
02:46: the value of the bonds can change over
02:49: time
02:50: but the interest payments cannot change
02:54: and so that means that what must be
02:56: changing is the discount rate
02:59: because those are the only two
03:00: components of the present value equation
03:07: so why would the discount rate change
03:09: for a company
03:11: well a discount rate or a company
03:14: is supposed to be reflecting the amount
03:17: of risk
03:18: in that company and specifically
03:22: we're looking at the rate on the debt so
03:24: specifically the riskiness
03:25: of the company's debt
03:29: so a lot of things can affect that and
03:31: for most companies it does fluctuate
03:33: quite a bit
03:34: over time um
03:38: some things that could affect that
03:39: include taking on additional debt
03:42: if you have a lot of debt already in
03:44: your capital structure you've got to be
03:45: making
03:46: all these interest payments it becomes
03:48: more risky to issue additional debt and
03:50: have an even higher interest payment
03:53: could be starting a new project
03:56: if this project especially if it's
03:59: something
04:00: new that the company is trying to
04:01: innovate
04:03: that is not comparable to their existing
04:05: operations
04:07: it could be substantially more risky or
04:09: less risky
04:10: than the existing operations and that's
04:13: going to change the overall
04:15: mix of risk in the company and therefore
04:17: the cost of debt
04:20: or even just having a bad year can
04:22: change
04:24: the cost of debt or discount rate for
04:27: the company
04:29: because they have to make those payments
04:31: on their interest
04:33: and that's what debt investors are
04:35: concerned about
04:36: if all of a sudden they're less
04:37: profitable this year and they're only
04:40: barely able to make that interest
04:41: payment that's
04:44: a very negative signal or going forward
04:47: in their ability to pay the future
04:49: interest payments and so that raises the
04:51: discount rate
04:56: so why should we care about all of this
05:00: let's walk through an example
05:04: so a company issues a bond it's a
05:06: three-year bond
05:07: 10 coupon and
05:11: as is typical for essentially every bond
05:15: the bond is going to be issued at par
05:17: and that means
05:18: that uh the principal the value of the
05:22: principal
05:23: is equal to
05:26: the price at which the bond is being
05:29: issued
05:32: um companies always just set it up this
05:35: way that's kind of the standard
05:37: in the industry is to do this so that
05:39: the price of the bond when it's issued
05:41: is a thousand
05:42: the same as the
05:46: face value par value principal value
05:49: a thousand dollars
05:52: and when we have a stream of cash flows
05:57: like this
05:58: uh 10 per year and then getting the
06:01: interest
06:02: plus the principal back at the end
06:05: uh then there must the discount rate
06:09: must also be equal to the interest rate
06:11: in order for the bond to be priced at
06:14: par
06:16: so when it's issued everything is in
06:19: sync the coupon rate
06:21: is equal to the discount rate
06:26: and the price of the bond is equal to
06:29: the face value of the bond
06:33: so a year passes by and now
06:36: the company becomes substantially more
06:38: risky they had a bad financial year
06:41: maybe one of the one of the other
06:42: situations we talked about they issued
06:44: additional debt
06:46: et cetera but now
06:49: in order to issue new bonds investors
06:52: are requiring that the company offer 15
06:55: in order for people to be interested in
06:58: buying these bonds
07:02: so because of that now we see that the
07:04: existing bond
07:06: that they had outstanding the three year
07:07: which was originally priced at a
07:09: thousand
07:10: is now priced at 9 18.71
07:15: and that's reflecting that if someone
07:17: buys a new bond that the company issues
07:20: they would earn a 15 rate of interest
07:23: but if they buy this existing bond they
07:25: would only earn a 10
07:27: rate of interest less than the rate
07:30: which would now be offered by the
07:31: company
07:32: and so they should not be willing to pay
07:33: as much for that
07:35: when a new bond would cost a thousand
07:37: dollars
07:41: and we calculate the irr on that bond
07:44: which is the yield of maturity
07:47: and that would be 15
07:51: so the 15
07:54: yield that we have on this existing bond
07:58: with its new market price matches up
08:01: with the cost of debt for the company
08:05: what it would cost to issue a new bond
08:09: but if we look at the book value of the
08:13: debt
08:14: the book value would still be at a
08:16: thousand the book value just stays
08:18: at whatever it was issued at
08:22: and so if you were to
08:26: try to calculate the um
08:30: yield of maturity or cost of debt based
08:32: on that thinking about it
08:33: as being priced at a thousand then you
08:35: would get 10 which is
08:36: far away from the actual new
08:40: cost of debt for the company
08:43: you would also say that
08:46: debt has a greater proportion in this
08:49: capital structure
08:50: than is actually the case because it has
08:52: actually decreased
08:53: in value so we can end up with quite
08:57: interact
08:58: inaccurate estimates if we
09:01: focus completely on the historical
09:04: rather than
09:05: looking at the market
09:09: so how do you go about calculating this
09:12: market value of debt so we'll discuss
09:16: three different approaches here
09:17: and similarly to the cost of debt it's
09:20: going to depend on what data you have
09:22: available
09:23: which approach that you can use and some
09:25: are going to be more accurate than
09:26: others
09:29: so first approach
09:32: we're discussing here is the pure
09:35: financial
09:36: statement approach um and for that
09:39: you're just taking the book value of
09:41: debt and using that as the market value
09:43: of debt
09:44: and sometimes that's all you have enough
09:47: data to do
09:48: and so you just kind of have to settle
09:50: with that
09:52: but it's always preferable to be able to
09:54: look at
09:55: what's going on in the market currently
09:58: to be able to
10:00: come up with the market value of debt
10:02: rather than just
10:03: looking at the historical because of the
10:05: issues that we just talked through
10:09: so then um
10:13: if you have the cost of debt
10:18: and it's actually a market cost of debt
10:21: from the market uh bond approach
10:25: and if you know on average
10:29: the maturity of the company's debt
10:33: then you can basically model
10:36: all of the company's debt as one big
10:39: hypothetical bond
10:41: and use that kind of evaluation approach
10:45: to come up
10:46: with the market value of debt
10:51: and that's going to be more accurate
10:53: than the financial statement approach
10:55: because it does
10:56: consider what's going on in the market
10:58: currently
11:00: but there are a number of assumptions we
11:02: have to make here and
11:03: so um it's not as accurate as the last
11:07: possible approach
11:09: which is you have information on all of
11:13: the debt that the company
11:15: has and the current uh
11:18: details of all of those
11:21: you can calculate the market value of
11:24: each individual debt instrument
11:26: and sum them up to get the total market
11:29: value of debt
11:30: so this is going to be the most accurate
11:33: assuming that
11:34: you can collect all of the data on this
11:38: um if you are missing substantial
11:41: portions of
11:42: the instruments or if you can't get it
11:44: at all
11:45: then it's not going to be an accurate or
11:49: feasible approach
11:51: but if you have that information it's
11:53: going to be the most accurate
11:56: so those are the three approaches we're
11:58: discussing in this course and we're
12:00: going to
12:02: look at how to implement each of them in
12:04: the following video
12:07: but before we wrap up i do want to just
12:10: talk a little bit about the seniority
12:13: of debt
12:16: and we're not going to handle it
12:20: within the examples in this class
12:23: but it is something that you want to
12:24: think about
12:26: if you go to build a more serious
12:30: debt side of a dcf model for wind
12:34: you need to be really accurate in the
12:36: model
12:38: so seniority is the
12:41: concept that uh
12:44: when the company goes bankrupt or if the
12:47: company goes bankrupt
12:49: and they're liquidating the assets
12:52: there is an order in which the different
12:56: debt holders
12:57: get paid off during this process
13:01: so debt holders get paid before
13:04: equity holders uh essentially always
13:09: you pay off all the debt holders and
13:10: then if there's anything left it will go
13:12: to the stockholders but
13:16: uh oftentimes there's not even enough
13:19: money after liquidating the assets to
13:20: pay all of the debt holders
13:23: and so some debt holders are going to
13:25: get paid and others are not and the
13:27: seniority
13:28: is what determines that so seniority
13:32: is just about this payoff order
13:35: a senior loan is going to get paid off
13:38: first
13:39: and then if there's still money left
13:41: over then the junior
13:43: loans are going to get paid off
13:47: and uh there are different levels of
13:51: seniority uh it's often just referred to
13:54: as just
13:54: senior or junior but a company can have
13:58: as many different levels
13:59: of seniority as they want it's just
14:02: however they write the
14:04: bond contracts
14:08: so the seniority is important
14:11: because
14:14: lenders have to consider this bankruptcy
14:17: recovery scenario
14:19: when they are setting the rates that
14:21: they would be willing to lend at
14:24: and if you have a senior loan then in
14:26: this recovery process
14:28: there's a higher chance that you're
14:30: going to get more money out of the
14:32: process
14:32: and with the junior loan there's a lower
14:35: chance that you're going to get money
14:37: or how much or you might not even get
14:39: paid at all
14:42: so the junior loans have to command a
14:45: higher interest rate
14:47: in order to compensate for the fact that
14:49: they're not going to get paid
14:51: at all or as much in the
14:55: recovery bankruptcy process
14:59: um and when
15:02: thinking about the cost and market value
15:05: of debt in this class
15:07: we're not we're not working with the
15:09: seniority we're just assuming
15:11: everything has the same seniority
15:14: but in a more detailed model you would
15:16: want to find the different levels of
15:18: seniority
15:19: in this specific company's debt
15:22: structure
15:23: and treat them separately so here you
15:26: know say there's three levels a senior a
15:28: junior
15:29: and and kind of a medium level of
15:31: seniority
15:32: then you would just take these as three
15:34: categories and you would find the costs
15:37: of debt within each of these categories
15:40: and keep them separate in
15:41: the analysis so it's not going to be a
15:45: big change
15:46: beyond what we're doing in this class
15:49: but it does complicate matters a little
15:52: bit and so that's why we're not
15:54: handling that here but it will be easy
15:56: to extend into that
15:57: after you understand the basic process
16:02: so that's how we can think about
16:05: calculating
16:06: the market value of debt and we'll come
16:09: back in the following video
16:10: to look at examples of calculating this
16:14: so thanks for listening and see you next
16:17: time

00:03: hey everyone
00:04: nick dear burtis here teaching you
00:05: financial modeling
00:07: today we're going to be looking at how
00:09: to calculate the market value of debt
00:12: in python using three different
00:14: approaches
00:15: this is part of our lecture segment on
00:18: the discounted cash flow
00:19: valuation model focusing on the cost of
00:22: capital portion
00:23: of the model so
00:27: we in the last video talked through the
00:31: uh three different approaches for
00:33: calculating the market value of debt
00:37: the financial statement approach
00:39: assuming the book value is equal to the
00:40: market value
00:42: the hypothetical bond approach and the
00:45: market value of individual instruments
00:47: approach
00:48: so we'll look at each of these
00:52: and take a look at that last video if
00:56: you want additional detail on all of
00:57: these
01:00: but for here we are going into the
01:03: python example
01:04: of how to do this um and
01:07: the completed example is here on the
01:10: course site
01:11: as well so let's jump over to the
01:15: jupiter notebook
01:17: and again this is split up into three
01:21: sections based on the three different
01:23: approaches so i'll start here by just
01:26: defining some inputs that we're going to
01:29: use throughout
01:30: the total book debt interest expense and
01:33: cost of that
01:35: so you can assume that
01:38: these would be loaded in from financial
01:41: statements
01:43: and um the cost of debt
01:46: was calculated previously in some other
01:49: exercise using the
01:53: financial statement approach or the
01:55: market
01:56: value of a bond approach so we already
01:59: have the cost of debt
02:00: and we got this other info from the
02:02: financial statements
02:04: so now going into the first approach
02:06: which is if all you have is the
02:08: financial statements
02:10: you don't have anything on market
02:12: currently outstanding market debt
02:14: this is all you can do and that's just
02:17: that
02:17: we just assume the uh book value of debt
02:21: the total
02:22: book debt from the balance sheet is
02:25: equal to the market value
02:26: and so we're done so it's really okay
02:29: it's not even a calculation we're just
02:30: using
02:31: the historical as uh the market
02:36: and sometimes that's all you can do
02:39: but we can definitely do better than
02:41: that if we have the data available
02:45: so next is going to
02:49: the hypothetical bond approach
02:52: um so there for the hypothetical bond
02:54: approach we need to also know what is
02:56: the average maturity
02:58: of outstanding bonds and that may be
03:01: difficult to determine
03:05: so that can be a reason that
03:08: you may not be able to go to this
03:09: approach
03:12: but if you do have that then we can
03:14: basically model
03:15: all the company's debt as a single bond
03:18: and
03:18: find the value that way so let's just
03:21: say
03:23: we were able to determine that the
03:24: average maturity on
03:26: the outstanding bonds is uh
03:30: five years and this would be a weighted
03:34: average maturity by the way uh looking
03:36: at the different
03:37: uh values of each of the bonds
03:41: so we want to create a hypothetical bond
03:44: that represents all the company's debt
03:47: so the first thing that we need to do
03:50: is come up with the coupon rate
03:53: that would be paid on this hypothetical
03:55: bond
03:57: which we can get that from the financial
04:00: statements
04:02: we can take the interest expense divided
04:05: by the total book debt
04:07: and take that as the coupon rate on the
04:10: bond
04:11: so with our existing numbers that puts
04:13: the coupon rate at six percent
04:17: so now that we have the coupon rate we
04:19: can create
04:21: the cash flows of the bond and just take
04:23: the present value of those cash flows
04:26: so the coupon raymond the principal
04:30: uh of this hypothetical bond is
04:33: the uh entire debt for the company the
04:36: booked at
04:37: um and the
04:40: coupon payment is just that rate we just
04:42: calculated multiplied by the principal
04:46: and the cash flows are going to be
04:49: that coupon payment um
04:52: for as many periods
04:56: up until the final period
04:59: which then we have uh the principal
05:02: plus the coupon payment
05:06: so calculating that uh with this
05:09: five-year hypothetical bond then we have
05:11: 60 000
05:12: cash flows for the coupon payments
05:15: followed by
05:16: a 1 million 60 000 payment on the end
05:23: so now that we have the cash flows and
05:26: we can use the
05:27: cost of debt which we have calculated
05:29: separately
05:30: as the discount rate then we can
05:33: determine the market value of debt
05:36: and make sure with numpy that you
05:38: include a zero at the beginning of the
05:40: cash flows because
05:41: it always assumes that it's getting
05:42: period zero first and this would be
05:45: period one
05:48: um and through that we get the market
05:51: value of debt
05:52: so here we're getting 920
05:56: 000 as the estimate of the cost or
05:59: market value of debt
06:02: and you don't necessarily have to go
06:05: through this
06:06: approach of creating uh
06:09: the cash flows directly um
06:12: and also if the
06:15: maturity is not a whole number which it
06:18: almost certainly won't be then this
06:20: direct approach won't work this was more
06:24: uh to just kind of emphasize that
06:27: uh theoretically this is what's going on
06:29: here we're just lining up the
06:31: hypothetical bond payments and taking
06:32: the press
06:33: value but there is a formula which
06:36: allows us to do that in one step
06:40: so it's given here
06:43: and then you could you know copy paste
06:46: this formula into your model
06:49: to complete the calculation
06:52: um and that is basically using the
06:56: annuity formula and then adding on
07:01: the final payment as a separate term
07:07: so then with that approach we get the
07:09: exact same
07:11: market value as we did with lining up
07:14: the cash flows
07:15: but this will also work for maturities
07:18: that are not whole numbers
07:23: and we can create a function which
07:27: does all of that so you can definitely
07:29: feel free to
07:30: uh copy paste this function into your
07:33: model
07:35: um and that is going to be taking the
07:37: average maturity cost of that
07:39: total book debt and interest expense and
07:41: giving you
07:42: the market value of the debt by this
07:44: approach
07:46: so then once we have the function we can
07:49: pass in
07:50: whatever numbers different uh average
07:53: maturities
07:54: we could do a sensitivity analysis or
07:56: other extensions etc
08:02: so then coming to the
08:05: last approach here valuing the
08:07: individual
08:08: debt instruments so it's basically the
08:12: same as what we just did but instead of
08:15: creating one hypothetical bond that
08:17: represents the entire company's debt
08:20: we just do this uh value bond valuation
08:23: process with each of the individual
08:25: bonds that the company holds
08:27: and then sum up the values of each of
08:29: those to get the total market value
08:33: so let me load in
08:37: some data and this is just some
08:40: example data of just three different
08:42: bonds
08:43: um with some
08:47: maturities and coupons principle
08:52: so um in order to
08:56: use this approach we do
08:59: have to make sure that we are getting a
09:01: good sample
09:02: of the company's uh bonds we should have
09:06: nearly all of the company's debt
09:08: instruments
09:10: for this approach to be valid
09:13: so the way we can check that is we can
09:15: sum up the principal
09:17: on all the debt instruments that we have
09:20: and that should be close to the
09:22: company's total book debt
09:25: the one that you get from the balance
09:27: sheet
09:29: so these two things are close and they
09:30: don't have to line up exactly they
09:32: almost
09:33: never will line up exactly because there
09:36: you know could have been additional
09:38: bonds issued or retired since the last
09:40: balance sheet period
09:42: um but as long as they're close i would
09:45: say within
09:46: a few percent of each other then
09:50: this approach is going to be valid and
09:54: if you're further than fro then rather
09:56: than a couple percent
09:57: you could do some kind of adjustment
09:59: approach if you have 10 percent
10:02: if you have 90 of the bonds uh
10:06: outstanding then you could just you know
10:10: multiply the ending market value that
10:11: you get by
10:12: 1.11 repeating to uh
10:16: to kind of normalize that but in general
10:19: i would say just
10:19: use this approach if you have data on
10:22: nearly all of the bonds
10:24: outstanding
10:27: so in this case they match up exactly
10:30: because it's a simple example and so
10:32: we're going to move forward
10:35: so then it's going to be the same
10:39: approach that we just looked at
10:43: lining up the cash flows and taking the
10:45: present value of those cash flows
10:48: and then we can
10:51: get the market value of any bond
10:55: and you may want to use the function we
10:59: looked at earlier
11:00: instead because that will be able to
11:02: handle the
11:04: decimal maturities as well but this is
11:07: just kind of
11:08: for sake of example i think this is
11:10: easier to conceptualize
11:11: what's going on
11:16: so now we have a function
11:19: which can get us the market value of a
11:21: bond
11:22: and we have a data frame full of bonds
11:25: how do we put these two things together
11:27: apply this a function to each of the
11:30: rows in the data frame
11:32: to get the market value of each of the
11:34: bonds that we have information on
11:37: so dot apply on the data frame
11:40: is what's going to be able to do that
11:42: for us
11:43: so i'm just going to briefly here
11:45: introduce dot apply
11:48: and dot apply is just letting you
11:53: take a function which works on
11:56: one row or one piece or one cell
11:59: one part of the data frame and you want
12:01: to apply it across the data frame so
12:03: that's why it's called apply
12:07: um and to help understand
12:10: what's going on when we call apply
12:14: um so here i'm doing df.apply
12:17: and i'm passing this understand apply
12:20: function
12:23: um and
12:26: what we will see here um is we see a
12:29: print statement for each time the
12:30: function is called
12:32: then uh we will see what is being passed
12:36: into this function got values
12:39: and we can see it's a series which is
12:42: there
12:44: which represents the entire row of data
12:48: we can see how this these numbers match
12:51: up with the first row
12:53: in the data frame
12:56: um and you can see that it's a
13:00: series and then
13:04: with a series we can pull out elements
13:08: just like with a data frame
13:11: we put the name of the
13:15: value that we're trying to pull out and
13:18: it will be able to pull it out so then
13:19: the next
13:20: is value of principal and you can see it
13:22: is getting this
13:24: uh 30 000 or you know whatever is
13:26: corresponding to the row
13:30: and this is just bring some extra lines
13:32: to separate so we can see
13:34: that this function got called three
13:35: times it got called once
13:38: for each row
13:41: and so this is exactly what we need to
13:43: be able to
13:44: apply this market value calculation
13:47: across
13:48: all of the different rows in the data
13:50: frame which represent different bonds
13:53: so if we give it a function which is
13:56: able to
13:56: take this series of data
14:00: and return back the market value of the
14:02: bond based on that data
14:05: then we can pass that function to apply
14:08: and get the market value of all the
14:10: bonds at once
14:15: so um and then the one other thing that
14:18: you'll see
14:19: in here is that i have axis equals one
14:22: that means we want to work on the rows
14:24: of the data frame
14:25: uh if we put access equal to zero or
14:29: that's the default
14:30: same thing will happen when i don't put
14:31: any axis
14:33: it works on the columns of the data
14:36: frame
14:37: and so that's why you see um
14:40: got values series of things that's being
14:42: passed to the function this time we got
14:45: the entire principal column rather than
14:48: the first row of the data frame
14:50: so this axis equals one is important to
14:53: be able to work on each row
14:55: at once but if you wanted to do some
14:58: kind of calculation that worked on each
14:59: column at once then you wouldn't want to
15:01: pass this axis equals one
15:06: so now that we understand dot apply
15:10: on the data frame now we can use that
15:13: in the context of calculating the market
15:16: value of these bonds
15:18: so um
15:22: we already have the md bond function as
15:24: we saw previously which can
15:26: get us the market value of the bond
15:29: based on these details
15:33: so now in order to use it with applying
15:36: remember that apply has to be able to
15:38: take the series
15:39: as its first argument and so we
15:42: we wrap this mv bond function call
15:46: inside another function which is
15:48: constructed specifically for using with
15:51: apply on the data frame and so that
15:53: function is going to take the series as
15:55: the first argument
15:57: and in order to do this calculation
16:00: we don't see anywhere in this data frame
16:03: the cost of debt
16:04: and we just have a single cost of data
16:06: that we're going to want to use in all
16:07: the calculations
16:08: so we're going to add that here as an
16:10: additional argument
16:11: and that can be passed when we call
16:13: apply
16:15: bypassing it by the keyword name
16:19: so any additional arguments of the
16:21: function can be passed this way and then
16:22: it's going to get the same cost of debt
16:25: for each row in the data frame
16:29: so the only other thing that we're doing
16:32: here is we're just pulling out
16:34: each of those individual values from the
16:36: series so just like we had seen back
16:38: here
16:38: where we were able to pull the principle
16:40: out of the series
16:42: and use it uh we're just doing the same
16:45: thing here
16:45: with the principle the coupon rate and
16:47: the maturity
16:49: um and then now that we have all those
16:51: individual numbers
16:52: we call the mv bond function passing
16:55: those numbers
16:55: as well as the cost of debt which is
16:57: separately passed to the function
17:00: and then it's able to return the market
17:03: value of the bond
17:05: so we call this and we see the market
17:08: value of the bond
17:09: of each bond and so that lines up
17:13: with the values in the data frame you
17:14: can see the ones with higher principal
17:16: i also have higher market values um
17:20: and so this is staying aligned with each
17:23: row and data frame
17:25: so to kind of wrap this up we can assign
17:28: that back
17:29: into the data frame so i'm just doing
17:31: the same
17:32: apply call over here but assigning that
17:36: back into the value
17:37: column of the data frame so then when we
17:40: look at it
17:41: then we have value come here as an
17:43: additional column
17:44: which now we have the market value of
17:47: each one of these bonds
17:49: and it doesn't matter how many roads are
17:51: in this data frame
17:52: here it was three could have been three
17:54: thousand everything would have been
17:55: exactly the same as far as the code that
17:58: you're writing
17:58: so this is a really powerful approach to
18:01: be able to
18:02: calculate the market value of as many
18:04: bonds
18:05: as you would like
18:08: and then we
18:11: ultimately want to take the sum of
18:15: all these values to get the total market
18:18: value of debt
18:19: for the company and that wraps up
18:24: the individual bonds approach
18:27: to calculating the market value of debt
18:31: but there is one additional thing to
18:33: consider here which is often you're
18:35: going to have a maturity date
18:37: and not a number of years
18:41: of maturity and even if you did have a
18:44: number of years of maturity
18:46: um you know usually it's going to be
18:49: decimal and so we're not going to want
18:50: to use this
18:51: um you know lining up the cash flows
18:54: kind of approach
18:57: so um here i'm
19:00: i'm showing the same function that we
19:02: had from before the annuity
19:04: approach function which uses this long
19:06: formula
19:08: and i created another apply function
19:10: which uses that
19:12: so um in general i would recommend to go
19:14: with this
19:15: uh rather than the prior approach where
19:17: the prior approach was
19:19: really just trying to help you
19:21: understand the calculation that we're
19:23: just
19:23: lining up with cash flows and taking the
19:24: present value but this just does that
19:27: same thing with a simple formula
19:29: um so we'll see when we
19:33: apply that this new function which uses
19:36: the annuity approach
19:38: on the same data frame we get the exact
19:40: same uh
19:42: values that we got with the other
19:44: calculation
19:45: but this approach will be able to work
19:47: for decimal maturities as well
19:50: which once you're just working with
19:51: material maturity dates
19:53: you're almost always going to have a
19:55: decimal
19:56: uh maturity uh something other than a
19:59: whole
19:59: integer
20:03: so let's drop um the
20:06: maturity years and the value
20:09: from this data frame um so now we're
20:13: left with just working with the date
20:16: um and we can check the data types
20:21: of the uh columns in the data frame
20:25: through this dot d types
20:27: because we need to make sure that pandas
20:29: has correctly classified this
20:31: as a date column before we can do any
20:33: date operations with it
20:35: so we see that maturity date has the
20:37: date time
20:38: type and so it has correctly classified
20:41: it
20:43: whereas the coupon is a floating point
20:45: number and the principal
20:47: is an integer now
20:51: um where this usually goes wrong is if
20:53: you have
20:55: uh some rows which are not dates like
20:57: maybe it's a dash or
20:59: a blank or you know
21:03: a year instead of a date or these kinds
21:06: of things
21:06: are going to mess it up and it won't be
21:08: able to detect that as dates
21:12: but if everything is a date it will
21:14: probably load in fine but you should
21:15: definitely
21:16: check regardless but presume that it
21:19: didn't come in as a date
21:21: and then we did whatever cleanup was
21:24: necessary to remove these
21:26: values which are not dates uh
21:29: then we can convert it to a date with
21:32: this
21:33: uh pandas two daytime method
21:36: so we just assign back into that same
21:38: column
21:39: converting that column into a date time
21:44: and after you do this then you'll
21:46: definitely be left with
21:47: daytime now it could error out
21:50: if there's something in the column which
21:53: can't be converted to date time
21:55: which there probably is if it wasn't
21:57: loaded as a date time automatically
22:00: so you would want to add code which
22:02: cleans that up and then
22:04: do the state time conversion
22:09: so then we want to
22:13: uh basically calculate that
22:16: number of years of maturity using the
22:19: date
22:20: um so if we look at
22:24: what happens when we pull out a single
22:26: date we have this time stamp
22:29: so that is the way that panus represents
22:32: dates is with this timestamp data type
22:37: so we ultimately want to say well
22:41: how long is remaining on this bond so
22:42: that's the difference between the
22:44: maturity date
22:45: and today's date so how do we get
22:47: today's date
22:48: in the code without hard coding it such
22:50: that we'd have to update it every time
22:52: we ran the code
22:53: well we have the date time module built
22:55: into python
22:56: this is there in every python
22:58: installation
23:00: and within that you can do
23:03: datetime.datetime.today
23:05: and that is going to give you today's
23:07: date
23:08: and time so
23:11: you can see the date and time here it
23:15: all
23:15: matches up with my system date and time
23:20: and then we can do math but those dates
23:23: just fine
23:24: so if we just subtract uh
23:28: the date uh which we got from the data
23:31: frame
23:32: we subtract today from that date we get
23:36: 360 days as the resulting
23:39: time difference or time delta so this is
23:43: another
23:44: data type in the date time library built
23:48: into python
23:49: which represents the difference between
23:51: two dates
23:55: but we need to convert this
23:58: time delta into a number of years
24:03: now there are some packages out there uh
24:06: which make this process really easy
24:09: uh like date you tell is one of those
24:12: packages
24:13: and then you'll just have like a dot
24:15: seconds or dot
24:17: years kind of attribute
24:20: um takes a little bit extra effort with
24:23: the uh just built-in libraries
24:26: but it's not too bad what we have to do
24:29: is
24:30: convert it to a number of seconds so the
24:33: dot total seconds method
24:35: will convert this time delta into a
24:38: floating point number
24:40: of how many seconds are in the
24:41: difference
24:43: and then you can calculate how many
24:45: seconds are in a year or whatever
24:47: time or whatever length of period you're
24:50: using in your analysis
24:54: so here this is 31 million
24:57: seconds in a year and then you just
25:01: divide the seconds
25:02: by the number of seconds per year to get
25:05: the years elapsed
25:08: so and then even you don't necessarily
25:11: have to do that all yourself you can
25:13: just grab
25:14: this function from the example which
25:17: does all it for you
25:18: so it takes whatever date and it's going
25:20: to grab today's date
25:22: take the difference take the total
25:24: seconds of that difference and turn that
25:26: into years
25:27: so you just call this function on a date
25:30: and it's going to tell you how many
25:32: years
25:32: until that date
25:36: and remember that when we use a function
25:39: with apply
25:40: that it needs to be able to take the
25:41: series the row
25:43: series as the first argument so we just
25:46: make one more function for the purposes
25:48: of apply which
25:50: just wraps calling this
25:53: function which converts the date into a
25:55: number of years from today
25:58: and all this function does is it takes
25:59: the series it gets the date out of the
26:01: series
26:02: and returns the number of years
26:05: and here i made it an optional argument
26:07: of the name of this date column
26:11: so that if your column is called
26:12: maturity space parentheses date
26:15: then it will just work otherwise you can
26:18: pass whatever the date column is
26:23: and it will work with whatever your date
26:25: column is called without you having to
26:26: modify the function
26:29: um but here the name is maturity date
26:33: in the data frame and so this just
26:35: directly worked
26:37: and so that got us the years to maturity
26:40: for each of these assets
26:43: so then we can just assign that back
26:45: into the data frame
26:47: and we'll have a number of years of
26:48: maturity for each of the bonds
26:53: then we can just do apply again with our
26:57: annuity approach apply function and that
27:01: will get you
27:01: get us the value of these bonds as well
27:06: and we simply just sum those at
27:09: each of these values in order to get
27:12: the total market value of debt for the
27:14: company
27:17: so that's a full overview of how to
27:20: calculate
27:21: the market value of debt in python
27:24: so thanks for listening and
27:27: see you next time

00:03: hey everyone
00:03: nick dierbert is here teaching you
00:05: financial modeling today we're going to
00:07: be talking about
00:08: calculating the weighted average cost of
00:11: capital or
00:12: whack and this is part of our lecture
00:15: segment on the discounted cash flow
00:17: valuation model and focusing on the cost
00:21: of capital portion of the model
00:24: so this is our last video in this
00:27: lecture segment
00:28: we already covered an intro to the dcf
00:31: enterprise and equity value uh cost and
00:35: market value of equity
00:36: cost and market value of debt now we are
00:39: just
00:39: coming to put it all together as the end
00:43: of the cost of capital portion
00:45: of the model to calculate the whack
00:48: which will become the discount rate
00:50: we use in the dcf model
00:54: so if you have calculated a weighted
00:58: average before
00:59: then this should feel very familiar
01:03: to you because all it is is just a
01:05: weighted average
01:06: and it's just a weighted average of the
01:09: different
01:10: costs of capital and
01:13: weighted by the percentage of
01:16: that source of capital in the overall
01:19: capital structure
01:22: so this is just focusing on
01:25: debt and equity you could have a third
01:27: term in here for preferred stock
01:29: as well or additional terms if you had
01:31: additional um
01:34: you know different uh you know maybe you
01:37: have different seniorities of debt and
01:39: you want to include those as separate
01:40: components
01:41: with different costs of debt there are
01:44: different ways to
01:45: specify this equation but this is kind
01:47: of the general
01:48: format you just take each
01:53: cost of capital and multiply it by
01:57: the weight of that capital in the
01:59: capital structure
02:00: [Music]
02:01: and for any debt components you're going
02:03: to want to make sure that you use
02:04: the after tax cost of debt so that's
02:07: taking the pre-tax
02:09: and multiplying by one minus the tax
02:11: rate
02:14: um so this puts together everything that
02:17: we have
02:18: calculated so far we've already
02:19: calculated the cost of equity we've
02:21: calculated the cost of debt
02:23: calculated the tax rate so there is one
02:26: small calculation left to do before we
02:28: get to the whack itself
02:30: which is then calculating the weights of
02:32: debt and equity
02:34: so the weights of debt and equity very
02:36: simple it's just
02:38: uh the market value of that type of
02:41: capital
02:42: divided by the total capital
02:46: so uh here in this uh
02:49: form of the equation where we just have
02:51: equity and debt and one type of each
02:54: then it would be the market value of
02:56: debt divided by
02:58: the market value or sorry market value
03:01: of equity
03:02: divided by the market value of equity
03:04: plus the market value of debt
03:06: and then over here it would be the
03:07: market value of debt divided by
03:10: market value of equity plus the market
03:12: value of debt
03:14: if you had additional components of the
03:17: capital structure
03:18: then those would get added into the
03:20: denominator of each of those weights
03:22: as well the market values you know all
03:25: your different market values for the
03:26: different components are the denominator
03:28: when you sum them up and the market
03:30: value of each component
03:32: is the numerator in calculating the
03:34: weight
03:35: so you should have something between
03:37: zero and one for the weights
03:39: on each of these and then you just
03:43: uh then we have all the components into
03:45: this formula so you just put them
03:47: together
03:47: and that ultimately gets you the whack
03:52: so and then to kind of visualize
03:55: the effect of those weights
03:58: i just made a quick graphic here which
04:01: shows
04:01: two companies which have the exact same
04:05: cost
04:05: of equity in debt um
04:09: pre-tax and post-tax uh cost of debt
04:12: the same equity cost is the same but the
04:15: first company
04:17: is uh evenly split between equity and
04:20: debt
04:20: and the second company is weighted more
04:23: towards equity
04:24: so you can see the resulting whack is
04:26: substantially lower quite a bit lower
04:29: for the 50 50 company than it is
04:32: for the mostly equity company and this
04:35: is generally the case
04:36: generally the cost of debt are lower
04:39: than the cost of equity since
04:40: debt is paid off first in the event of
04:43: bankruptcy
04:44: um and so the more that a company is
04:48: shifted towards
04:49: debt then the lower the overall cost of
04:52: capital
04:53: will be um
04:56: so it's definitely important to consider
04:59: not only the
05:00: rates on the uh sources of capital but
05:04: also
05:05: the weights that they have within the
05:07: overall capital structure
05:10: so that wraps up the calculation
05:13: of whack we're going to come back
05:16: in the next lecture segment to focus on
05:20: the free cash flow side of the dcf
05:25: model so we have handled this part and
05:28: we've handled this part
05:29: and we're just going to focus on the
05:31: free cash flow side of the model
05:33: in the entire next lecture series and at
05:36: the end of that we'll also
05:37: put the whole thing together into one
05:40: cohesive model
05:42: so thanks for listening and see you next
05:46: time