馃攳
Excel Finance Class 76: Investment Criteria: MIRR - Modified Internal Rate of Return - YouTube
Channel: unknown
[0]
welcome to finance an excel video number
[2]
76 if you want to download this workbook
[5]
for chapter 8 click on the link directly
[7]
below the video and scroll down to the
[9]
Excel finance class series in this video
[12]
we've been talking about the internal
[14]
rate of return and we saw that when we
[16]
had non-conventional cash flows which
[18]
just means the sign changes more than
[22]
one time so a conventional cash flow is
[26]
negative in time zero and everything
[28]
else is positive here we can clearly see
[30]
it the sign changes one 200 more than
[33]
one time we have a non-conventional cash
[36]
flow we saw that IRR we have trouble
[39]
with calculating an IRR so in this video
[43]
we'll see the modified IRR the big
[47]
caveat though is we have our cash flows
[51]
from those cash flows who determine
[53]
internal rate of return as soon as you
[56]
modify these to try and coax an internal
[61]
rate of return out of it you've actually
[63]
modified the cash flows the original
[65]
cash flows aren't there and so it's kind
[69]
of something totally different so here's
[73]
the meaning totally different meaning if
[76]
they're not the original cash flows you
[77]
really can't find the IRR nevertheless
[81]
people like percentages so much that
[84]
they actually do modify them even though
[87]
that they know it's not really the
[89]
internal rate of return thus it's called
[92]
a modified internal rate of return the
[94]
idea is simple if you can only have one
[96]
sign change if we could somehow change
[99]
this negative to a positive then we
[105]
wouldn't have that problem we could use
[106]
IRR there is a number of different
[109]
approaches the discounting approach the
[111]
reinvestment approach in the combination
[113]
approach which is actually what the M
[117]
IRR function uses that's a function in
[121]
Excel but we'll look at each one the
[125]
first one discounting approach well this
[127]
is negative right well why don't we just
[129]
discount it back to x zero right and
[132]
then we'll have a
[133]
bigger negative cash flow here a
[135]
positive and a zero so we'll see how to
[138]
do this we need to take that number
[145]
right there because that's the cash flow
[148]
at time zero and then add subtract
[153]
because this will give us a let's see if
[160]
we discount this with our regular
[162]
formula it will work just fine so I'm
[165]
going to add and I'm going to take this
[167]
number
[168]
oops oh I put an equal sign so I'm going
[170]
to add so really we want to subtract
[172]
this from this negative but plus of this
[174]
negative will give us a subtraction okay
[178]
but now we need to discount this so I'm
[180]
going to divide by one plus our discount
[184]
rate here raised to the two so two
[188]
periods now this will work just fine
[191]
the that exponent in terms of our excel
[194]
formula that exponent will get
[196]
calculated first before this division
[199]
then the division then the plus all
[201]
right that's just that amount discounted
[205]
back and then added to that so now we
[208]
have one negative we can go equals this
[210]
there's our positive and put a zero here
[212]
and we can calculate our equals IRR now
[217]
notice the cool thing about this is we
[220]
have even though it's coaxing the
[222]
original cash flows are not there so
[224]
it's something totally different than
[225]
the concept of IRR we have eliminated
[228]
the problem of more than one sign change
[232]
okay another one another approach is
[234]
reinvestment approach and by the way if
[237]
you know the discount rate why not just
[238]
use NPV the reason why is people like
[241]
percentages reinvestment approach the
[246]
problem with this is we're going to
[247]
assume that this cash flow right sorry
[251]
this cash flow right here we're going to
[252]
assume we invest it and then bring it
[256]
out to here and then the positive future
[258]
value that will counteract that so that
[260]
will be positive this will be negative
[262]
once the cast comes into the firm it may
[264]
not be reinvested at I
[267]
our and once the cast comes in it can be
[271]
used for whatever you want right the
[273]
cast comes in it could be used on any
[275]
other project so we're assuming
[279]
something here that may not be true and
[282]
so that brings problems to this
[284]
nevertheless people do this I'm gonna
[286]
get this cash flow right there put a
[289]
zero right there and now here we're
[291]
gonna say equals get our - amount at
[295]
time two and then we're going to add to
[298]
it this amount times and then it needs
[304]
to go one period forward so I'm going to
[306]
add the interest and that's it no
[310]
raising it to any power because it's
[311]
just one period so now what we have oh
[314]
look at that only one sign change right
[317]
so we can equals IRR and we get fourteen
[325]
point two so those are pretty close now
[327]
the combination approach is simply take
[331]
all the cash flows and this is a small
[333]
example here take all the cash flows and
[336]
bring all the negatives back two times
[338]
zero and all the positives to the last
[340]
period I'm gonna say that actually I can
[346]
steal this formula right here this is
[349]
getting all the negative cash flows back
[351]
two times zero so what I did was I
[354]
copied it in edit mode so the cell
[356]
references wouldn't move if I just
[357]
copied the cell and pasted it it would
[359]
not work okay we got a zero here and now
[365]
we're going to take this
[371]
cash flow time one positive we're
[373]
getting all we bring all the positives
[375]
out to the last period and then we're
[380]
going to times one plus this just one
[384]
period so bull now we can take the I or
[388]
R notice we're using the IRR function
[389]
but really get don't get confuse we're
[391]
getting a modified internal rate of
[393]
return so fourteen point six five now
[396]
there is an M IRR function and what's
[399]
great about this actually is you can
[401]
have two different rates the required
[404]
rate of return and the reinvestment rate
[407]
the values these ones right here only
[412]
one sign change comma the finance rate
[418]
that's going to be our required rate of
[419]
return reinvestment or rate if it was
[423]
different in our example here it's not
[425]
you can put that in and we get fourteen
[430]
sixteen fourteen sixty five and this did
[432]
it the same way that we did this now you
[435]
can go look at the algorithm for this in
[437]
help but there's another way to do this
[438]
too and we touch on the idea of
[442]
geometric mean later in this class in my
[447]
statistics class series I have some
[451]
extensive videos on geometric mean but
[453]
the idea is you take the ending amount
[457]
divided by and I'm gonna have to put a
[459]
negative here because I want it positive
[462]
ending amount divided by beginning
[465]
amount and we're gonna have to put
[466]
parentheses around this and take the
[468]
root of this and the root is going to be
[471]
one the root is going to be one but we
[477]
don't have a root function R I don't
[479]
know one so we take 1 divided by however
[482]
many periods and this is a great way of
[484]
getting a geometric mean which is a way
[489]
an average for specifically for
[492]
percentages or proportions okay and then
[495]
we subtract one
[499]
you know I think we could do I didn't
[502]
plan to do those things we could use the
[503]
Joe no no the geo mean will not give it
[507]
to us the geo mean is for when you have
[509]
all the the individual percentages plus
[512]
one but nevertheless we get the same
[514]
answer there using our geo mean and if
[519]
we increase the decimals here you can
[521]
see boom exactly the same okay so that's
[524]
a little bit about modified internal
[526]
rate of return remember the big caveat
[528]
is we don't we're not using the original
[531]
cash flows so once you deviate from that
[534]
you are doing something completely
[536]
different but people do it and the
[538]
reason why is people it's easy to
[540]
communicate percentages but don't get
[542]
confused it's not the internal rate of
[544]
return alright see you next video
Most Recent Videos:
You can go back to the homepage right here: Homepage