🔍
IRR: Best way to calculate SIP returns | IRR vs XIRR vs CAGR | Money-Minded Mandeep - YouTube
Channel: unknown
[0]
This is my tax-saving mutual fund in which
I started investing just 5 months ago
[6]
I have invested a total of ₹1.57 Lakh in it
and its current value is near ₹1.67 Lakh
[15]
So, this is a 6.72% profit
But if I switch it to XIRR
[22]
Then my XIRR is 47.98%
And that is right
[35]
Hey Guys! Money - Minded Mandeep here
Welcome back to the channel
[39]
I uploaded my last video about CAGR
which proves that lot of people
[44]
Don't know that how to calculate the
return accurately on their investment
[48]
But all those people are
investing for a long time
[53]
This proves that you are not
making informed decisions
[58]
That's why many insurance agents (not
all) can show you the wrong return
[66]
And they can prove to you that why
should you invest in their plans
[70]
If you don't know calculating the return accurately
[73]
Then you will end up locking your lot
of money for years in wrong instruments
[78]
In the CAGR video, I told you that
if ₹900 becomes ₹2000 in 6 years
[83]
Then how will we calculate its CAGR
Then I also proved that
[86]
There is no compounding in the
stock market and mutual funds
[90]
But that doesn't mean that you
avoid investing in those options
[93]
Your investment will definitely grow
But it will fluctuate up and down many times
[98]
Then it will finally reach the pick
after many ups and downs
[102]
The best way to create wealth is to hold
your investment for a long time as possible
[109]
You can decrease your risk as you
increase your investment period
[113]
In part 1, I explained about CAGR
You can watch if you haven't yet
[118]
But the problem is that we invest
many times during our lifetime
[123]
This is not like if I invested 10 Lakh
once then I will never invest again
[127]
And will check its CAGR only at
retirement. But we invest periodically
[134]
Most of you have a periodic SIP
which is monthly in most cases
[139]
A fixed amount is deducted from your bank
account at a fixed date of the month
[142]
And the corresponding mutual funds
units are bought by that amount
[144]
Let's take an example.
Suppose I am investing for a year
[147]
I will invest ₹10,000 in every
first day of the month
[151]
So, I invested ₹10,000 on 1 January,
₹10,000 on 1 February,
[154]
And I followed the same pattern till December
[159]
I will redeem my investment on
31 December whatever value it has
[164]
I invested a total of 1.20 Lakh and suppose
its value will be 1.5 Lakh on 31 Dec
[172]
If I ask you about its return. You
can't repeat the same mistake again
[179]
(30,000/1,20,000) * 100 = 25%
[183]
But this is the absolute return
[187]
It indicates the total profit on the total investment
[192]
But we have to consider 1 more
important point to calculate the return
[195]
And that is time.
If you observe the above example closely
[199]
The entire money was not invested for 12 months.
[202]
Only ₹10,000 which I invested on
1 Jan was invested for entire 1 year
[207]
₹10,000 which I invested on 1 Feb
was invested for just 11 months
[211]
Next ₹10,000 was invested
for 10 months and so on
[214]
₹10,000 which I invested on 1 Dec
was invested for just 1 month
[219]
How to calculate the returns
of such complex data
[223]
Because absolute return can't indicate
that this investment was profitable or not
[227]
We also can't use CAGR because that
requires only initial and final value
[234]
CAGR concept fails when we
are investing multiple times
[242]
IRR (Internal Rate of Return)
will be helpful in such cases
[247]
Its simple definition is that
when you invest multiple times
[252]
Then the annualized return of
that entire journey is known as IRR
[257]
There is another concept XIRR
(Extended Internal Rate of Return)
[262]
There is not much difference
in these concepts
[264]
There is a little difference while applying
the formula in MS Excel or Google Sheet
[271]
If you have studied capital budgeting
if you have a commerce background
[275]
Then probably you know its technical version
[278]
Such a percentage rate that makes
your future cashflow's present value 0
[283]
But let's go to the EXCEL
[287]
I will explain that how can you
calculate IRR on your own investment
[294]
You should keep a point in
mind about IRR and CAGR both
[301]
These 2 rates are annual always.
And this is definitely very important
[306]
You might be shocked
when I will explain it
[309]
Let's take an example of monthly SIP
[312]
I am investing ₹1K on every months' first date
[316]
I have entered that with - Sign. Why?
[319]
Because ₹1,000 is going out of my pocket
[322]
I will enter with - Sign when I will invest
[325]
I will enter with + Sign when I will redeem
[329]
I entered -1000 on 01-01-2020
[332]
exactly same for all months
[335]
I entered -1000 on 1 Dec
[340]
I will redeem the entire money on 1 Jan 2021
[347]
Which value is 13000 at that time
[350]
My total investment is ₹12K
[352]
Total profit is ₹1K
[354]
The absolute return is 8.33%
[357]
But that is not a right method
[360]
As all installments were not
invested for the entire 1 year
[365]
Only 1st SIP was invested for a full 1 year
[368]
2nd SIP was invested for only 11 months
[372]
I have to calculate XIRR
[376]
Its formula is quite simple
[379]
You have to type =xirr(
[381]
Then select the values column
[385]
I selected all the transactions
[390]
Then I will enter comma (,)
[393]
Then I have to select all the dates
[398]
I will close the bracket after dates selection
[400]
Then multiplied by 100. WHY?
[404]
I want to get the values in percentage
[409]
XIRR is 15.7% as I press enter
[415]
I can compare different investment products
[419]
And can decide that which is best
[423]
We can get wrong results if we
compare based on absolute return
[427]
Please enter all the dates in a set format
[433]
I typed 1/1/2020 here and Google
Sheets recognized it as I entered
[441]
I can see the calendar as I double click on it
[445]
I can change the date from here
But if you double click on the date
[450]
And the calendar doesn't open. It means
that is not recognizing it as date
[454]
That is recognizing our
date as a random number
[456]
We will not get the correct value if we
enter random number in our XIRR formula
[462]
if I replace this slash with a dot
then it will not consider it as date
[468]
And my IRR formula will be invalid
[471]
This was all about in the case of monthly SIP
[474]
But what if you invest money every year
[476]
Suppose you have an Endowment
plan or Guaranteed Income plan
[481]
In which you pay an annual premium.
I have made a dedicated video on it.
[487]
That plan looks like this. You invest
a lumpsum amount every year
[491]
Or should I say you pay a premium.
[495]
Suppose you invest ₹1 Lakh on
the 1st January of every year
[500]
I entered it as -1 Lakh. You
continue it for the next 7 years.
[503]
Then you get nothing for the next 8 years.
[506]
And you will get ₹12 Lakh in last
year. So I entered it as +
[513]
I will calculate its IRR using the same formula.
[517]
So I typed = IRR(
And selected all the values
[521]
Then comma and I will select all these
dates. Then bracket close * 100
[527]
I will get the IRR of 4.56%
[531]
The full form of XIRR is
extended internal rate of return
[536]
The second one is IRR
This is the same concept as XIRR
[541]
But MS Excel and Google Sheet
treat them both differently
[544]
I just told you XIRR.
If I want to calculate its IRR
[547]
Then I will type in the formula -
IRR and I will select all the values again
[555]
But I will not select dates this time.
I will close the bracket
[560]
I will not multiply it by 100 and
I will get the IRR which is 5%
[565]
If you have seen my last video
and the intro of this video
[570]
As I told you that considering the
time is very important while investing
[574]
Like how much time our investment
is taking to generate returns
[579]
If I didn't mention time in this formula
then how this is showing me 5%
[585]
This is assuming that all the entered
data was invested annually
[591]
Without a date, this assumed that
all the figures like investment, redemption
[600]
Are annual not daily or monthly or quarterly
[606]
If your investment is annual
then IRR is useful for you
[611]
But I recommend you to always use
XIRR and mention the dates manually
[616]
Actually, I am also not sure that
which dates are assumed by this
[619]
Is it assuming the 1st day of
the year or 1st day of the year
[624]
As there is a little difference
between 4.56% and 5%
[627]
Its another drawback is that you
can't keep any cell empty
[631]
I have entered zeros here as I didn't
get any money in these years
[636]
But entering these zeros
is not important in XIRR
[641]
But if I don't enter zeros in the
case of IRR then it will assume
[646]
That I got that ₹ 16Lakh in
8th year instead of 16th year
[651]
So, It will change IRR from 5% to 14%
[655]
IRR Strictly demands only annual numbers
[659]
And if you are not investing or
redemption any amount in any year
[663]
Then you have to enter 0 in all those cells
then only you will get an accurate calculation
[667]
But in the case of XIRR,
As I have entered dates
[671]
So, if any raw is empty then it
will not consider it anything
[675]
And I will get the accurate number
on basis of my amount at last
[681]
You have learned monthly
and annual investment
[685]
But in most cases, we
don't invest like this
[688]
Like I have a monthly SIP but I invest
more money in it if the market is down
[693]
Sometimes, I invest in it on the
other dates from SIP dates
[697]
Then how will we calculate
XIRR in these cases
[700]
I have a different example
for those cases
[703]
Dates are annual here which can be
monthly also but the technique is same
[707]
But my amount of investments are irregular
[712]
I invested ₹1 Lakh in the 1st year
Then ₹1 thousand in the next year
[715]
I invested 18K in the next year.
I invested 0 in 2025
[720]
I withdraw ₹10,000 in 2027
[726]
So, in the XIRR, I can exactly enter
my investments and withdrawals
[733]
And I will get the right answer
[735]
I invested 20,000 in 2029 and
withdrawed 40,000 in 2032
[742]
And I finally withdrew all my
amount in 2035 which is 20 Lakh
[746]
We will use the same method
to calculate its XIRR
[750]
=XIRR Bracket open
I will select all the amounts
[755]
I also selected all the dates after the
entring comma and multiplied by 100
[759]
So, my IRR is 14.13%.
[763]
Although this was periodic so you
can treat it as monthly or annually
[768]
But what if you don't have a
specific strategy to system
[772]
You invest randomly or try to time the
market but you should avoid that
[778]
But suppose you have invested multiple
times in a year and that is not periodic
[783]
You have invested a random amount
on random dates irregularly
[786]
Then how will we calculate its IRR?
The method is exactly the same
[792]
You have to enter your exact
date on which you invested
[796]
And enter your amounts
exactly as you invested
[800]
If you have withdrawn any amount then
also enter that as I entered ₹50K and 20K
[806]
Suppose you have an insurance
policy in which you paid the premium
[811]
And you also got some money back
Then how to calculate?
[814]
So, you have to find here that is your
net cash flow inflow or outflow
[817]
Write it with + sign if it is inflow
and with - sign in outflow
[822]
Then you will get the accurate
XIRR when you will apply the formula
[830]
If you are investing in a
mutual fund through any App
[835]
Then you don't need an excel
sheet to calculate IRR
[840]
Let me tell you one important
point here before going ahead
[845]
We have formed a "Jagruk Investor
Community" where you can interact
[848]
With like-minded people and
can learn a lot about investing
[853]
You don't need to pay any charge to join it
[856]
If you have opened a Demat account
using our link in ZERODHA or UPSTOX
[860]
Then you are eligible for it.
But still, if you have not joined it
[864]
As many people told that
they have not joined it yet
[868]
The link of Labour Law
Advisor App is given below
[870]
If you login into that app
using the same email id
[873]
You will easily find there an
option to join this community
[877]
If you don't have a Demat account yet
then you can start your investing journey
[880]
I am giving you the link to India's best
broker ZERODHA which has
[883]
Many features Like you can apply
online for SGB in just 2 minutes
[889]
So, check out the link in the description
[890]
My 2nd point is that when you will visit
labourlawadvisor.in/calculators
[895]
A SIP calculator is there where you
can do your investment planning
[900]
How much money you are investing
and what return you are expecting
[904]
You can also enter your time horizon
Then you can know that
[908]
How much money you will
have if things go as planned
[912]
A pro tip is here for you. If you are
investing in mutual fund or stocks
[915]
Then we like to keep the rate
of return like 15% or 20% or 25%
[918]
But calculate it for 12-15% which
is believable and reasonable
[922]
As I told you at starting. No matter
which App you are using to invest
[928]
You will get the option of
IRR and XIRR in all apps
[930]
For example, this is at the absolute
rate in Kuvera App by default
[934]
But you can switch it to XIRR and
can watch all percentage figures
[938]
But how this app can show me XIRR
as I have not withdrawn anything yet
[944]
Then what does this XIRR
in the app indicated?
[948]
That number is based on an
assumption and changes daily
[952]
The assumption is that if you
withdraw all your money today
[957]
Then what will the XIRR on your
investment till date from starting
[960]
You can verify it. Let me
explain it with my example
[965]
This is my ELSS mutual fund in which
I have made only 4 transactions yet
[970]
Let me write transaction dates
and transaction amounts here
[975]
Let me write the fund value in
today's date which is 1.66 Lakh
[980]
I will get exactly 47.98% as App
when I apply the IRR formula
[989]
You may think that the total
invested amount is 1.56 Lakh
[993]
The profit is only ₹10K. So, it is
only a 6.2% absolute return ideally
[999]
Then why my IRR is 47.98%?
As I have not earned this much.
[1004]
Please always remember that IRR
is always annualized return
[1009]
This ₹10K is not earned in 12 months
but earned in a very short time
[1015]
I will annually earn a 47% return
if I earn with the same flow
[1020]
So, XIRR always calculate annual returns
[1024]
Thus you learned about XIRR
Please comment your views
[1029]
Please press the like button
[1032]
So that Youtube can recommend
this useful video to others also
[1037]
There is a question for you
Please try to answer
[1040]
If I invested 1 thousand rupees
which grew to ₹1500 in 6 months
[1048]
Then what is my IRR?
Write your answer in the comments
[1051]
Let's meet with a new video
By...
[1056]
{Outro Music]
Most Recent Videos:
You can go back to the homepage right here: Homepage