What Is XIRR? XIRR Vs IRR Vs CAGR | XIRR In Excel Explained By CA Rachana Ranade

in one of the previous videos which had foster on CAGR I received a lot of requests that please make a separate video on IRR and also try to compare the relationship between CAGR and ex-ira well your wish is my command so I thought of making a separate video on ex-ira [Music] hi guys Sierra channel Dominator here and I welcome you all to my new video on exciter if you remember in the previous video on cg i had given you an example wherein I had compared the percentage who returns by comparing the initial investment with the final return see here there's a very basic peculiarity the basic big reality is that inflow should be only at one point of time and your final outflow or your final return should also be only at one point of time but what if there is something like an S IP what happens in a nice IP is there a single investment is it a one-time investment or are there multiple investments throughout the year answer is there are multiple investments throughout the month so whenever I talked about cjgr I guess I'd given two examples in the video both examples one thing was in common there's only one investment and there is only one return okay but if you want to calculate a return over a period of time when you invest on a monthly basis or wherein you invest on a quarterly basis then CAGR does not work for you what works for you is x ir a-- okay frankly speaking whenever we learn any course like chartered accountancy or for any finance course for that matter there's nothing like X IRR and you might be shocked there's only a IRR and what is IRR it's internal rate of return to simplify things what is IRR it is nothing but the rate at which the business is able to generate its returns right so just as an example if I say IRR for my business is 12% then what does it mean if I invest 100 rupees in my business by the end of the year I'll be able to generate 112 rupees that is a simple meaning of IRR then why this X IRR comes into picture Microsoft Excel while developing various formulas financial formulas they re phrased or renamed the IRR as xir and nothing else so if you ask me what is the difference between IRR and exile what is my answer there's no difference irr is what we call it academically and if you want to calculate the same thing IRR in IRR in Excel it is called as x ir I hope IRR versus X IRS both are same ex-ira is the terminology for ms-excel okay let me give you an example example is that if I say you have to give me thousand rupees for a month please this is just an example okay you need not pay me anything you just have to pay thousand rupees per month to me do this for let us say thirteen months one year one month okay and I promise that at the end of this timeframe I'll give you fourteen thousand five hundred rupees okay so tell me how much you have paid me you have paid me thirteen thousand rupees and how much I have promised to pay it's 14,000 find another piece tell me is it worth investing money in this game or it's not worth investing money in this game difficult to answer yes quickly tell me is CAGR going to help you yes or no answer is no why because there's no lump sum investment I have I'm repeating this point CAGR is useful only and only when there is a lump sum investment when is xir are going to be useful it is going to be useful when there is a periodic investment part by part month by month quarter-on-quarter you are going to invest money that's where x ir a-- is going to play a big role the question is how do we calculate that okay so for that what I've done is in Excel I'm going to show you a very very very simple way of calculating it what I've done I've taken this data let us say SI p date and amount SI p date is starting from 10th of January 2018 and you are doing this for 13 months I told you so you are doing it till 17th of February 2019 okay or let us say 17th of January 2019 so Jan is coming twice that's the period of 13 months and what happens is on 17th of February that is after one month in the 14th month I am going to give you a return promise return of 14,500 the answer is that if I calculate an absolute return what is the absolute return absolutely nothing but like whatever we were taught in third standard selling price minus cos price is equal to profit and then profit divided by cos price in 200 is your profit percentage remember your good old 300 days yes that's how we calculate profit percentage a similar variation of that instead of doing two steps what is the simple formula simple formula is basically just take a total of this this total is nothing but what 13,000 what is thirteen thousand thirteen thousand years nothing but cos price there's the money that you have invested 13,000 right you can also use a normal sum formula and I'm sure everyone knows this in Excel if I'm just dragging this down I can see here what is it some 13,000 right tell me how much is the reading Redemption value dream is what I'm going to get it's 14,500 if I were to calculate an absolute return absolute return means what this is what I had invested this is what I've got how do I calculate it it's very simple is equal to this divided by this minus 1 this is a shortcut for first calculating the profit then profit divided by cos price in 200 this is simple formula okay if I say enter here you can see 11.5 for I'm just going to take it into percentages and I'm going to increase to zeros so you can see it 11.5 for I just increase one more zero here okay so now the 11.5 for figure matches but tell me is the 11.5 for a correct return that I am calculating answer is no why no let us understand this this 1,000 was invested for how many months this 1,000 was invested from 10 1 2018 till till 17 to 2019 so this was invested for almost a timeframe of 14 months okay this 1000 was invested almost for a timeframe of 30 months this 1,000 was invested almost for a timeframe of 12 months I hope you were understanding this one one month I'm going down and in short the last investment that I made this 1,000 on 17th of January this was invested only for one month okay now if I were to calculate the effective return so basically how would have to go about this I have to calculate this 1000 which is invested for 14 months then this 1,000 which was invested for 30 months so on so forth till this 1,000 which was invested only for one month if you try to do this on paper with the help of a pen and a Cal see that want to consume a lot of time I was going to be a simple way out simple way out is X IRR how do use that formula doesn't to be discussed in the immediate next section and the weight of entire two seconds is over let's understand how to calculate X I are using Excel simple is equal to X I okay open the bracket now the moment you open the bracket Excel helps you show how you're going to use the formula can you see it values tell me what are the values values are 1000 per month and the last Redemption value is what 14,500 so what I do is just drag this these are all my values for which I have to calculate X error then I just put a comma now what have to be what has to be done I have to put in the dates dates are from what what from 10th of June 2018 till 17th of February 2019 ok a comma if I put a comma then it asks me what is your guess yes is basically if you want to put any guess like 8% 9% whatever but you can just leave this out even if you don't press anything Excel by itself takes a guess of 10% don't go into that it's ok you can just close the bracket here right away and just press Enter ok the moment you want to put this see I got the same exile of 19.8% means 1 on an average if I average out the investments the first thing that I would I was telling you first 1000 invested for 14 months so on so forth till the last investment which was investor only for 1 month if I'm averaging it out on an average I bought a return off 19.8% visibie 11.4% which was the absolute rate of return can I say exile is giving me a better idea of my returns answer is absolutely yes I hope you have understood how x ir x ir a-- needs to be calculated okay be very careful on how you input the dates okay I'll give you a simple example for this instead of putting this dashes instead of putting it in a date format if I do this sep 10 2014 10 to 10 you is that the format in which date is written is very important if you do a wrong format your xir will not be calculated so I'll give you a simple example instead of 10 - 0 1 - 2018 had I written 10 1 2080 with dots in between let's have a look at this column ok xir column and I'm pressing enter boom gone it doesn't recognize this as a proper date so it has to be in which format 10 - 0 1 - 2018 moment or do this everything else is sorted out and you get the value it's very important how you input the date that is very crucial other not be able to calculate the amount of x ir a-- i hope this point is very clear always remember this it has to be DD - mmm - why why why why why okay yes now that you have understood my entire explanation I must read out some point from my PPT because I have taken a lot of efforts to drive this PPT and I must read it out otherwise I'll feel bad why I did all these things right so listen X IRL is nothing but modified C CAGR Y modified CAG CAG R is only single-input single-output you're over a period of time it is used to calculate return over the holding period when the investments are multiple or in varied frequency we have discussed this it considers the cash inflows and outflows during the investment period and compares with the final receivable lump sum amount it gives importance from the time and period of investment so that's why I was telling you about the date and the amount of investment instead of telling this step by step because it's like this big I've already taught you how to calculate X I I just quickly kept for you first of all important mmm dd YY YY format number one number two Y minus 1000 + y this is plus fourteen thousand hundred always remember thumb rule whenever you are talking about outflows outflows are always written in negative it's money going out now so one your balance is decreasing so always remember any outflow has to be accompanied with a minus sign whereas any inflow will always be written in a positive sign so that's why 14,500 number three to be remember how do we calculate xir we just say is equal to X IRR and then we give the range for values , give the range for dips see that's how we calculated it very easy now right now let's talk about a case where there could be multiple payouts means one assume that I'm investing ten thousand rupees instead of per month now let us take per your okay and let's see if xir works out for a year lien investment instead of a monthly investment so assume that I'm investing ten thousand in year one ten thousand year to year 10,000 in year three and year four I feel that I should take out a little bit of my money okay your if I again I start investing you're on your I start investing let's say around nine or ten zero again I feel I should take a payout and maybe at the end of the tenure I do a full withdrawal now say xir a formula work here let's take this okay so whatever I did multiple payout means nothing but remove from removing money by selling a part of the asset just before the complete sale of asset it's just like taking out a portion of the accrued gain okay same thing I was talking about okay just forget it let's go to the Exile formula so what I've done is just take this out two thousand one two three four until 2015 okay so in this case I've said you're one how much do I invest ten thousand year to ten thousand or three thousand you're for what have I done have I invested five thousand or every drawn five of them I have withdrawn five thousand how did you understand because there's no minus sign here if I had invested it they should have been accompanied by a minus sign any outflow is a minus sign any employees a positive sign again investment investment outflow outflow flow whatever this thirty thousand again is a sale of investment that's why it's backed by a positive sign rather than a negative sign and what happens towards the end it's to like fifty thousand positive okay so what do I mean what has happened in 2015 I've liquidated my entire investment yet okay so how do we calculate x ir a-- it's ready so jitney so same thing formalized thing whatever is the scenario all payouts and only one pain or pay or paying payout pain combination formula is same how do you do that same thing is equal to x ira all these amounts comma entire date easy yes okay we almost at the end of this discussion till now we had discussed only one scenario where the investment amount is constant okay so for example in the first example I took s IP monthly of one thousand in the second example I took a yearly investment but ten thousand only would there be a change if instead of a constant amount if the amount varies so for example what I've taken here is again a yearly investment from 2006 to 2009 teen and here if you check this out I have varied the investment what businessman it can be difficult to maintain an exact amount of s IP so they may say this time I had a good profit I'll invest more maybe next year I had a comparatively lesser profits I'll invest less okay so let's say 15,000 15,000 then again directly thirty five thousand then I get 15:15 followed by directly 50,000 okay all these are negatives so come in out flow towards the end of 2019 six lakh twenty thousand is the inflow how to calculate excel say okay three scenarios again how to calculate exactly same is equal to X error blue is the values and read other dates very easy okay so let me just quickly recall whatever we have done we have done three scenarios till now fourth one is the last one first scenario we did monthly si P scenario okay scenario number two we'll discuss that instead of monthly investment it was a yearly investment wearing we talked about a mixture of inflows as well as outflows number three we said that the amount could be uneven okay so third about third case study was a combination of uneven amounts as well as inflows and outflows mixed together number four scenario we'll wrap it up we'll mix it up so what could be the scenario in the fourth scenario let's say the amounts are also uneven and their dates are also never so how could this work out to be so let's just take this out assume that this is your case study where the investment date is 16 August 2008 then 17th December 2008 directly so nothing in September October November then directly 21st join you 2010 so on so forth if you can see the month is also ignore at random date is also random amounts are super random there's no you know specific flow there are some inflows there are some outflows I'm sorry there are some inflows there are some outflows and towards end 21st January 2020 I finally had an inflow of one like 50,000 okay now I just thought of going into the shoes of mr. Amitabh Bachchan and I thought of playing Kaun Banega Crorepati with you but don't forget done say you're not going to get one crore rupees okay in fact I'm giving you a homework okay so what are your fou do is open excel you have to type out this okay that'll take not more than two to three minutes by that out then what you have to do is tell me which is the correct answer first one is eight point four to nine point nine six seven point four three and whatever asana mom is says is correct okay so just forget about the fourth one tell me about your answer first second and third what I'm going to do is I'm not going to tell you the answer right away I'm going to tell you the right answer by pinning my comment in the same video but I'm going to pin the comment after two days so that I get to know whether you are able to understand whatever I'm sharing with you so try this out by the way how to calculate it remember that Trista he's socially so finally same thing is equal to X L values dates exactly same thing is to be done be careful with the dates and I hope you should get this well that's it from my side for this video I hope you have understood very clearly what is the difference between X IRR and CAGR if you are liking my videos consider subscribing to my channel hit the like if you want to if you have really liked the content I hope you are aware about my memberships you can check out the join button which now appears on my channel and I hope you are understanding whatever I am teaching I'm going to understand it by way of your comments which you want to put in this video don't forget to put your comment don't forget to put your answer don't forget to read my pinned comment which is going to come up in the same video after two days so that's it from my side Jane bye bye [Music]

Channel: CA Rachana Phadke Ranade

Views: 579,346

Keywords: CA Rachana Ranade, Stock market, what is xirr, mutual funds, xirr explained, xirr vs irr, cagr vs xirr, sip calculator, xirr in mutual fund, what is xirr formula, xirr function in excel, what is xirr in mutual fund, what is xirr and absolute return, what is xirr function in excel, what is xirr and cagr, XIRR Vs IRR Vs CAGR, XIRR In Excel Explained, XIRR In Excel, xirr, cagr, irr explained, irr explained in simple terms, irr explained youtube, compound interest

Id: Azi4VQhlQFw

Length: 17min 18sec (1038 seconds)

Published: Sun Feb 02 2020