馃攳
Calculate IRR in Excel (Formula, Examples) | IRR Calculation in Excel - YouTube
Channel: WallStreetMojo
[11]
hello everyone hi welcome to the channel
of Wallstreetmojo friends today we are
[15]
going to learn a to tutorial on calculating the IRR in Excel so as you
[20]
can see we here there are a couple of
things that input that is -3000
[23]
2000 and 5000 so this is a negative the rest 2 are
[27]
in positive and you put all of them so
that you can get an IRR
[31]
so basically IRR is just the value and
the guess let's begin how this thing
[36]
works see IRR in excel stands for internal
[41]
rate of return right this is as simple
as that internal rate of return IRR in
[48]
Excel is one of them is one of the
built-in function available in Microsoft
[52]
Excel IRR on Excel falls under the
category of financial functions in in
[61]
financial function in so this basically
we are talking about
[65]
the internal rate of return is the rate
of interest that is received for the
[71]
investment done so IRR in Excel
consists of a payments done
[74]
represented by the negative values and
income generated denoted by positive
[79]
values that occurs at the regular time
of interval so you can say that IRR
[84]
function in Excel returns the internal
rate of return for a series of cash
[90]
flows represented by you can say
positive and negative numbers positive
[96]
and negative numbers both this cash flow
does not have to be consistent as they
[101]
would be for an annuity
however the cash flow must this is very
[106]
important must occur at a regular
interval such as like monthly you can
[114]
say quarterly and annually so this has
to be there
[119]
the cash amount can vary for each
interval so let's learn the IRR formula
[127]
in Excel as you can see so the IRR
formula is is equal to IRR as you can see
[134]
over here the returns the internal rate
of return for a series of the cash flow
[139]
okay once you do that you get values and
guess so the range or the value this is
[145]
basically the required parameter this
thing and a range of cells that denote
[151]
the series of cash flow for which the
internal rate would be calculated now
[156]
there is another thing that is called
guess now what is this guess this is an
[160]
optional parameter as you can see see
this is compulsory but this is optional
[163]
it denotes a number that you guess is
close to the result of the internal rate
[168]
of return so if the value mentioned it
takes a default value as 0.1 or 10%
[175]
right and the open bracket over here
this to bracket they denote the optional
[181]
parameters okay so the function returns
the numerical value and it could be
[186]
positive or it can be negative so how to
calculate this IRR in Excel see the IRR
[193]
calculation in Excel can be done in two
ways that is based on worksheet and
[197]
second is by
VBA function in Excel okay VBA a
[201]
function as a WS that is worksheet
function it can be entered as a part of
[205]
the formula in a Cell just like what we
did right now of a worksheet as a VBA
[209]
function it can be used in macros this
can be used in macros specifically code
[218]
which is entered through the Microsoft
Visual Basic editor integrated into ms
[223]
excel okay refer to the IRR calculation
in you know in the template which which
[230]
is provided okay and now the first
what we are going to do we are going to
[235]
start with the IRR calculation with the
help of in work shit that is WS so IRR
[244]
calculation first example now in the IRR
calculation let's say there are number
[251]
of cash flows let's say there is -3000 then you have 2000 and then you
[257]
have 5000 so how will you calculate IRR
it is as simple as that I
[261]
'm just
[262]
calculating over here is equal to IRR
and you select all of this 3 that is the
[268]
value C5 to C7 that's it 67% is your IRR so in this IRR
[274]
example calculation this 3000 over here
this basically - 3000 denotes the
[279]
capital amount invested the subsequent
2,000 and 5,000 denotes the income so
[285]
this 2,000 and 5,000 they denote the
income so the IRR function in Excel is
[290]
calculated on the values of - 3000 2,000
and 5,000 so which we get the result as
[295]
67% let's take another example of
IRR that is the same thing but we'll
[301]
take another example now let's see in
the IRR example we'll be using 3
[307]
numbers let's say 3000 a 2,000 and 5,000
so let's try and calculate a IRR with
[315]
this numbers is equal to IRR and close
the bracket we get NUM error why do we
[320]
get NUM error so in this IRR example in
calculation the NUM is displayed as as
[326]
result because there is no single
negative value present in the set of
[331]
in this particular set there is no
single negative value so there is no
[334]
value indicating the payment but all our
incomes because those all are positive
[340]
let's take another example with a 3
different numbers let's hit 3rd that
[347]
has -3000 let's take another details - 3000 -2000
[352]
and -5000 let's take this numbers and calculate the IRR so once we
[357]
do IRR calculation close the
bracket we get again the NUM error
[363]
so in this IRR calculation all the
values over here they are negative this
[368]
indicates that there was no income
generated hence the result generated in
[371]
is NUM let's take another one more
example that is this one is equal to
[379]
let's say there are a couple of for cash
flows that is available with us the
[383]
first is -10,000 then we have 2000 let's say 5,000 4,000 and 6,000
[389]
this are the incomes and this is the
outgoing so let's calculate the IRR up
[396]
to 3rd year or let's say this this is the
IRR so we get -19% at the
[401]
end of the 3rd year let's calculate
IRR at the end of the 4th year when
[406]
is the IRR getting positive that's what
we are looking for again so it's get
[409]
it's getting positive 4% and let's calculate the final IRR if we
[414]
calculate this we get 22% so in
this IRR example calculation the total
[418]
values are -10000 that denotes the
payment done this this are the values
[422]
per year incomes earned like 2,000 5,000
4,000 and 6,000 so to calculate the
[428]
income generated at the end of the 3
years the IRR formula is applied in the
[432]
cells that have been ranging the result
over here that is -19% as the
[437]
payment amount in the initial year is
more than this to the right is more than
[443]
the income generated in the subsequent
year to calculate the income generated
[446]
in at the end of the 5 years the IRR
formula is applied on the cells ranging
[450]
okay which we get 4% C2 to C28 and C
25 sorry C25 to 28 and 25 to 29 so
[459]
the result is positive as the income
generated in the following year is
[463]
completely more than the payment amount investor in the initially as you can see
[467]
this is 11,000 and over here this is
more 17,000 which have crossed the line
[473]
now there are a couple of things that
you should take in mind while
[476]
calculating the IRR first the value of
an array it must contain at least one
[482]
minimum positive or you can say positive
and negative or negative positive and
[490]
one negative okay this is the first
criteria the value should be in a
[495]
sequential order an IRR uses the order
of the values to interpret the order of
[500]
the cash flow so please take care of
that owner entering the payment and
[503]
income values in the sequence as they
happen or you desire to make it happen
[506]
the 3rd if an error for a reference or
the argument contains text logical
[511]
values or empty cells those values are
particularly ignored 4th ms excel
[516]
uses an iterative technique calculate
to IRR with Excel so starting with
[519]
guess 4th the MS Excel uses an iterative or
[523]
technique to calculate IRR with Excel
starting with guests IRR recycle through
[527]
the calculation until the result is
accurate within the range of 0.401%
[534]
right and if the IRR cannot find
a result that works after 20 tries
[541]
then the NUM error will be returned so
if an IRR function is in Excel returns
[545]
the NUM or if the result is you can say
is expected to be then then try with
[552]
different values for 6th in most of
the cases you need you need not provide
[557]
guest value for the IRR calculation in
Excel so if the guest parameter is
[561]
skipped these software assumes it to be
0.1 or 10% so that is
[567]
the difference IRR is closely related to
NPV if you if you try and have a look
[573]
the rate of return calculated by IRR
with Excel is the interest rate
[577]
corresponding to a 0 net present
value
Most Recent Videos:
You can go back to the homepage right here: Homepage