What Is XIRR? XIRR Vs IRR Vs CAGR | XIRR In Excel Explained By CA Rachana Ranade
HTML-код
- Опубликовано: 29 сен 2024
- This lecture on #XIRR explains the use of #XIRRinExcel and the difference of #XIRRvsCAGR.
XIRR in Mutual funds is essential for calculating returns on investment. Learn what the XIRR formula and how XIRR calculation is done.
You can get my Stock Market courses on www.rachanaran... It’s an opportunity to learn 65+ concepts relating to Basics of Stock Market in 11 sessions! Feel free to WhatsApp on +91 9022196678 if you have any purchase-related queries.
Android App: bit.ly/CARRAnd...
iOS App: bit.ly/CARRiOSApp
Basics of Stock Market Course: course.rachana...
--------------------------------------------------------------------------------------------------------------------------------
Join my RUclips Memberships: bit.ly/JoinCAR...
-----------------------------------------------------------------------------------------------------------------------------------
✔️ To Invest in Cryptocurrencies try WazirX► link.rachanara...
--------------------------------------------------------------------------------------------------------------
✔️ If you want to create your demat account then please click here for assistance:
forms.gle/ddA7...
--------------------------------------------------------------------------------------------------------------
Insure your life and health
✔️ Life Insurance► bit.ly/3tYenqr
✔️Term Insurance►bit.ly/3ynVssD
______________________________________________________________________
To invest in themes based portfolios, check out the following Smallcases
✔️ Top 100 stock ► link.smallcase...
✔️ All-Weather Investing ► link.smallcase...
_____________________________________________________________________
Facebook► bit.ly/CARRFac...
Telegram ► t.me/CARachana...
Instagram► bit.ly/CARRIns...
Twitter► bit.ly/CARRTwi...
Linkedin► bit.ly/CARRLin...
------------------------------------------------------------------------------------------------------------------------
Playlist:
Basics of Stock Market For Beginners: link.rachanara...
Books to read: link.rachanara...
As promised, I am posting the correct answer of the question asked at the end of this video.... and.... The correct answer is 8.42%.
Thanks for this video and especially such a detailed manner. I understood. I was one of the best video I had seen on your channel.
This video is one of the reasons why to subscribe and press bell icon. And conversation in English is a 🍒 on the cake.
Ma'am i have a small request..
Can you upload a video on How to calculate a fair value of a companies stock? Or Different ways of calculating the fair value.
@@rohansarda2696 no use of this thing cuz companies in a bull market will always exceed the fair value and trade at discount in bear market. Also fair value will be different for different ways. Above was my experience not theory or speculation.
@@gauravchoudhary4218 I know this bro.. But just for knowledge purpose..
You are very good CA knowledge sharing to everyone from basic level 💰💰💰nice video keep it going 👩👩
Superb way of teaching this is amazing DON Rachana
Yesterday tried to calculate xirr but not succeeded but your video gave a very good perspective of the concept. Going to use it now.
Thanks Mam.
Hope you keep us enlightened like this.
Your way of explaining is simply amazing.
simply amazing...difficult concepts are explained in very simple and easy way
V Good explaination. Thank you
Madam I have two doubts
1. Whats d difference between xirr and recurring deposit formula.
For me both exactly looks same except the uniformity in inputs
2. Does all SIP's in stock market have defined return value at the end of maturity period?
If not, we would not know our maturity value at maturity period.
Then at this scenario how this xirr helps...
(May be I wouldnt have enough knowledge as you madam but with defined clarity of explanation, i understood the concept so a doubt popped up)
I understand XIRR gives return for holding period. Can you make a video on how to calculate annualized returns for uneven investments, withdrawals and time periods?
8.42 is the correct answer
Hi which are the best one to invest with SIP.. What is u r opinion about axis blue chip and hdfc 100 fund?
During a BE final year project calculation for my brother, theory behind XIRR, was not available, however your explanation, gave a clear understanding of it, thank you..
Humble request, If you could help us with calculations with their chronology pertaining to the project would be helpful in performing the calculations in the project.
IRR and XIRR in excel are different functions. IRR does not require date of cash flow, XIRR does
Mam, RD return calculation and XIRR return calculation is same?
Answer - XIRR= 8.42
Thanx mam
IRR calculated in excel is not XIRR. Both are different concepts all together. How can such gross mistake happen ma'am?
Mam... your 4th option is current...😊😊😊
Make video on xirr vs mirr
Is IRR SIMILAR TO ROCE?
Financial knowledge+Sense of humor+ Speaking ability+ Confidence+ Expression+...=Rachana Madam
And beauty all in one...
So true
+ बदमाशी वाली मुस्कान 🙃
Nothing but "Beauty With Brain" 😀
Don't agree. The explanation is unstructured, childish and not targeted for adults.
I always love your style of presentation - i can imagine the amount of hard work you put in last 15-20 years thru your teaching career. The sense of humor doesn't develop overnight. As usual, great content and to the point explanation. Love you mam from Norway
The Norway part was to show off
The main difference between Excel XIRR and IRR functions is this: IRR assumes that all the periods in a series of cash flows are equal. You use this function to find the internal rate of return for periodic cash flows such as monthly, quarterly or annual. XIRR allows you to assign a date to each individual cash flow.
you are right, she is saying random things lol
ABSOLUTELY CORRECT
Xirr gives the return rate considering days for which you remained invested
Damn, this woman really needs to go back to her CA notes, commerce notes, or school 😮. I cannot believe this woman said this..
Thanks for restoring my faith! Some sanity finally.
Madam,
Excellent explanation.
Your act of wearing specks is stylish as like introducing hero in Cinema
I am Professor, likes your style of delivering.
Really Thank you soo much for your contribution towards improving Financial Literacy in India
Me too learnt a lot from your videos and your way of teaching is very beautiful and very easy to understand
Answer is 8.42%
BTW, very good video and impressed with your presentation skills Rachana!! Your content and way of explanation is always extraordinary. Keep up the good work and your effort for us is highly appreciated 🙂👍
i got it there was error in format its showing MM-DD-YYYY, I got it correctly 8.42%..Thanking you for the lovely lecture hats of to you Madam..
Great content as always. But I feel you should have explained the concept behind calculating IRR manually and then gone for excel.
Request you to do another video on the same topic.
Agree with this. That will give a greater conceptual clarity.
2 seconds ka wait 😆 your sense of humour is amazzzzzingggg 🤘
Totally wrong ...IRR IS IF THE RETURN IS ANNUALLY GENERATED. XIRR IS ONLY SUITABLE IF INCOME FLOW IS IRREGULARLY NOT ON ANNUAL.BASIS. XIRR COLLAPSE INTO IRR IF RETIRN IS ANNUAL.
EXCEL.IS CLEAR ON IRR AND XIRR.
Thanks for the excellent presentation...! I joined your channel only today. I am sure I will learn a lot though late as I am only 76 years old.
Best wishes.
I work at EY and certified in MS excel from Microsoft.
IRR and XIRR both the functions exists on excel. IRR is calculated when the payment is made evenly for example first day of the month. And in XIRR payments are made on uneven days.
@rachna Ma'am please make correction.
Ma'am, could you pls clarify whether the SIP calculators take XIRR or absolute returns into account ?
Well, it depends on what platform you're using. I can tell you with some first-hand experience that, Kuvera and zerodha can show you both.
If you use Paytm money, then under each sip investment, you can see it mentions XIRR investment return in percentage.
10:28min you have written DD-MM-YYY but you said MM-DD-YYY...
Nice video Thank you..
It all depends on settings in excel
@@alimehran9540 yeah I was just referring to what she has mentioned on the screen and what she was saying was different...
Thanks
@@joysaha8943 always welcome
Bro it depends on windows regional settings.
@@rajuar1955 yes settings. Not the format.
Mam you have got a really great sense of humor, which makes your videos even better.🙂
Thanks for this helpful post Rachana ji. One slight correction though - Date format need not necessarily be DD-MM-YYYY , it really depends on the default which is there in your excel installed software. In my case (Microsoft Excel on Mac OS) , I had something different and XIRR calculation was giving error. I had to change it to my format ( I opted for DD-MMM-YYY like 12-Jun-2020) and boom, it started working then. So might want to add this disclaimer in the video, may in a caption note.
Also XIRR is the extended IRR. IRR is used for fixed periodic entries, usually multiple outflows and one inflow or vice versa. But, if periods are irregular and there are unequal inflows and outflows (say in a PPF), we use XIRR. That's why specific dates are mentioned.
Is Xirr based on per annum rate of interest???????
Please anyone give me answer
I would like to congratulate you ma'am on your goal of growing financial literacy among our people, I am becoming more and more financially literate after watching more and more of your videos.
♥️♥️♥️♥️♥️♥️♥️🙏🙏🙏🙏🙏🙏🙏🙏🌹🌹🌹🌹🌹🌹🌹🌹💐💐💐💐💐💐💐💐👌👌👌👍👍👍👑🏆☕
Mam, excel me percentage format krne k liye shortcut:-
Cell ya range ko select kr k cntl+shift+5(key board wala) 🙏
I m the 2nd one 😂 Mam u r doing great job .. Love & respect from Pakistan 😊😊
Wow btw bro stock exchange waha konsa?
Thank you Ma'am, Rishta wohi soch nayi!! :-D You, have clarified #XIRR concept with such ease, I was always confused about it. Thanks again.
The XIRR explained is completely wrong. That's not what XIRR is
answer is 8.42
but there is a thing which I think rachna ma'am you said might be incorrect i.e IRR and XIRR have different formulas in excel they are not the same.
IRR is calculated when the cash flow is uneven and XIRR is calculated when there is a uneven cash flow with uneven period.
Nice explanation by providing several scenarios.
One more scenario I wish to consider (Similar to chit fund):
> What if the payout (which happened in mid of the duration) have larger value than so-far invested amount?
> Will XIRR work for that too?
> Should the final row value always be the positive value?
For the following example, XIRR = -17.59%
> Is the chit fund a non-profitable for that person?
Eg:
Persons in chit: 24
Chit amount: 120000
Term months: 24 (Monthly pay-in is 5000 without dividend)
7 April 2018 -3850
10 May 2018 -5000
7 June 2018 -4100
6 July 2018 -4000
6 August 2018 -4200
6 September 2018 -4200
9 October 2018 -4200
6 November 2018 -4250
5 December 2018 -4570
6 January 2019 -4620
6 February 2019 -4810
7 February 2019 115000
6 March 2019 -4710
6 April 2019 -4750
5 May 2019 -4720
5 June 2019 -4800
5 July 2019 -4850
5 August 2019 -4850
7 September 2019 -4850
9 October 2019 -4925
5 November 2019 -4920
8 December 2019 -4920
7 January 2020 -4900
10 February 2020 -4930
7 March 2020 -5000
Thanks in advance.
XIRR is 2.1796
I have same question, getting value error while calculating this
Chits has a separate fanbase 😂
But IRR and XIRR is different. IRR considers annual cashflows whereas in case of XIRR, cashflow dates are flexible
Nice video, good learning,
At the time of review you say mm-dd-yyy , but I think it is dd-mm-yyyy, what is correct?
IRR and XIRR are not the same.
IRR assumes that all the periods in a series of cash flows are equal but XIRR allows you to assign a date to each individual cash flow.
Mam you're amazing
😊 blessed to be your RUclips student!!
नमस्ते मैम,मैं आपका प्रत्येक वीडियो देखता हूँ।लेकिन पूरा समझ नही पाता हूँ क्योंकि मेरी इंग्लिश बहुत कमजोर है।कृपा कुछ वीडियो हिंदी में भी बना दिया करिये।क्योंकि भारत जैसे देश मे हिंदी बहुमुखी भाषा हैं।कृपा एक बार विचार करिये।मैं आपका जीवन भर ऋणी रहूँगा।🙏
Thank you Rachana! Great learning on CAGR and XIRR!
I invest like the 4th option , uneven Dates and Uneven Amounts , Very Knowledgable VID
Excellent and easy explanation ❤️ wish we would have 10 years ago, my life would have been financially much more abundant ❤️❤️
Mam the way of delivering is awesome tq
Suggest me some of books to know more about like dividend payment under galton model
And financial problem and solutionss..(like tbills ,bond, certificate of deposit,warrants ,debt - equities practical aspects)
thanx ❤️
4:45
I learnt it in 5th standard, some three years ago
Don't learn only when to buy, also learn when to sell.
Please make a video on Intrinsic value calculation with example in Indian context. There are many formula on net which more relevant to US market.
I very much appreciate your efforts and knowledge of wisdom. Would love to see brief vlog on Sensex & Nifty?
1. How they are different from each other?
2. How it valued on index 40k Sensex or 11000 nifty etc. Maths / equation behind ?
3. Which one should be looked more ?
All the best !!!!
Sensex is indicator of BSE whereas Nifty is Indicator of NSE.
Sensex accounts for different number of companies as Nifty, hence the evaluation and price is different
3. Both or just one world do getting the idea of the market.
More commonly used and referred to market is Nifty
I completely understood you process of calculating xirr ,but do not know how to invest it so can you plz tell
Ma'am what if we want to calculate xirr when we are not at the end of investment. Like my sip is on going and i am having positive amount outstanding balance. What i understood from this video is the xirr function will assume that investment is fully redeemed at the end and calculate returns. Hope am able to explain my confusion.
In general; what should be ideal rate of of return % [ XIRR % ] OF A MUTUAL FUND to decide if this fund is doing good !! As I have a situation ; need ur suggestion !! ; I am having a Home Loan @ 8% and also accumulated Fund from a mutual fund SIP which is showing me return XIRR at 12% . Shall I continue investing in it or Redeem and Pay of the partial Home Loan to reduce interest. ? What will u suggest ?
I was trying to workout IRR in excel, and I did not get the results. After researching, I found out that I should use 'negative' values for paying out; and 'positive' value for receiving in. That worked finally..
Nice feedback
Hi rachana mam.. I have a doubt..XIRR value for 13,000 for a period of 13 months investing 1000 per month is 19.8%...I am calculating in reverse order for how much I will get return ...13,000/100*19.8=2574...but 1500 is return in ur xirr...? please apologise me if I ask a wrong doubt:..
Quick question: let's say I have 1200 to invest monthly. BUT I do not invest in the same thing (for example I buy 3 different ETFs) => this counts as a single investment also, correct?.
Because if I count this separately, it would mean that I have the same date 3 times, because I make that investment in the same day.
Like this:
date x / 400 (I bought ETF1)
date x/ 400 (I bought ETF2)
date x/ 400 (I bought ETF3)
So the correct way would be:
date x/ 1200 (summing ETF1+ETF2+ETF3)
Am I correct?
L.E: nevermind, it doesn't matter. You can have only one date and add them, or you can have the same date 3 times and the result will not change.
Imma leave this here anyway if anyone's interested.
GM,mam please answer my query.Mam I saw your video of XIRR in the RUclips.U have very lucidly explained the whole concept of XIRR, by citing examples.Mam I also tried my very best to solve the very first example, following all your advice, like to take date in dd-mm-yyyy form only etc etc.But my result came as 0.1745......how come.for your answer was 19.80%.I took the formula as =XIRR(values, date) and then did enter.I even asked my computer expert colleagues to do the same,they too got the same answer,how come mam.Please explain it mam as to where I went wrong.🙏
Can you pls explain why should XIRR be different from annuity paid at the end of a period. For example
Future value of annuity = ((1+r)^n-1)/r)*(1+r)*investment per period. Suppose I know the future value of the annuity and the investment per period, then the r calculated from the above equation should match with xirr, isn't it? But I checked it is not matching. Can you please explain why isn't it marching? Regards, Rahul
Let's say I am a Money Lender and I lend Money at a rate of 1% per month (12% annual). Assuming the example 4 in your video, where I keep on giving top up loan as well as accept returns, how would I calculate the final amount that I should receive ?
But at the end of it, all that matters is what I got in hand!. And that's 11.54%
Then what's the use of xirr?? I can't go to anyone and say look I'm getting 19~% returns. Bank acct is going to physically show only 11.54%>>
Ma’am on the grow it showing 94.48% of xirr n when i calculated from the formula that u tell its showing 22.40 something, I’m sure that I’m doing something worng,
Plz tell me
=xirr(amount from sip start:amount of redemption month including profit,date of sip start: date of redemption)
Maharashtra has a few sensible, educated, non racist, good women like RR. Otherwise I've seen horrible Tai, Bai, Mami, Mauxi, Vaini, Atya & Bhau's all ready to slaughter you at a slightest of provocation & opportunity. It's their insider v/s outsider war & rascism.
🤬😡"Aey Baiyya Saliya"😠 is the rage how they speak.
I can proudly say RR is a pride of Maharashtra.👍🙏
OK, Rachana, the XIRR and IRR function in Excel are NOT the same. The latter uses fixed periods for cash flow, the former accommodates incongruous cash flow periods. Which tends to be more real world. In real estate and bond investing, extremely regular cadence of cash flow is the norm. But, elsewhere in business, irregular cash flows is the norm.
Hi Rachana,
I got the result as 0.198292774. Using exact same formula and steps as shown by you.
I think your excel is taking directly in percentage and showing 19.8.
Is there any setting of excel we have to do to show by default in percentage Otherwise to get correct answer in my case i have to multiple by 100 also.
For stock return, holding a stock over a period of time. Will CAGR give me absolute return? Brokerage sites shows simple return calculation which shows more than FD return %age, but while using CAGR the return is very less. So wanted to know, using CAGR is the current calculation for calculating stock returns.
The IRR function assumes periodic cash-flows and doesn't take dates as an argument. This function is used the least. Because IRR assumes equally-spaced cash flows, you must include all months - even if the cash flow was zero in that month. Skipping months changes the implied timing of the cash flows for IRR.
XIRR function assumes uneven cash-flows and basically computes the exact discount factor for each cash-flow based on daily discounting using a 365 day calendar. Apps like Kuvera displays XIRR in their dashboard.
IRR assumes that you have constant periods across the range you pass it (it's agnostic as to if they are across years, months, days), it will return the IRR per period. XIRR you can pass a set of dates that are not constant in time between them, and it will return the annual internal rate of return.
Great video as always but Rachan You got the difference between XIRR and IRR wrong. XIRR takes into account the dates of investments in the calculation. as shown by you. IRR doesn't and that is why they get different rates when calculated in Excel. Hey, it's NOT an MS term :)...
IRR and XIRR are NOT THE SAME - neither in mathematical terms, nor in MS Excel. IRR does not consider the TIME at which the cashflows occur; while XIRR takes into account the exact date of the cash in/out flows. Hence, if the cashflows are not evenly spaced out, the results of IRR and XIRR will differ.
Please reply 🙏🏻
This xirr shows overall return of 19.8 % or annualized return of 19.8%
In the very first example you have calculated XIRR for monthly SIP's. So whatever XIRR is coming was monthly return or yearly return? AND if it is monthly then how to calculate yearly return (vice versa) For second example you calculated for yearly investment then whatever XIRR is coming was yearly return or monthly?
Hi Rachana, Thanks for a very simplyfied video. Just one confusion if you can please answer. Subjectively XIRR and Absolute return should come same in the end of the year? Why XIRR is high and absolute return is less. I mean what XIRR is indicating? does it mean if i would have done a lumpsum investment then return would be 19.8 %. or what?
Hi Mam, i have a doubt in calculating XIRR, for example, when i started investing from jan 2013 to till date, since SIP is for 60 months
Question - how will calculated the XIRR till date
Say I have 22.5 lack home loan with 15 years having 23,000 EMI per month
Suppose want to close it after 5 years how much mutual fund amount I should put per month. It's ok any sector. This is kind of planning for home loan repayment
Is there a formula in excel which gives final value. I already know my XIRR let's say.
Ex: I do an investment of 1lc this month then after 6 month I do 50000 investment. And I know my XIRR to be 15%. I need to find final value after 2 years from first investment
Can you explain how calculate XIRR for those INSURANCE POLICIES in which client will deposit in limited pay let say for 12 years and after the wait of 13th year client get per annum income for next 20 year at the end of year
Mam I am not getting the correct figure.. from 10-10- 20 to 10-sep-2021 2000sip per month..and current value is 27600/- but calculation says 0.96
Hi, with reference to your above video, please , go through Mr Pankaj Mathpal's clear cut explanation of IRR & XIRR for your kind information and clarity on your misconceptions. Don't look at this with a prejudice mind and preoccupied notion..
No one on this planet is perfect, what we need is an open mind to learning and admission of our ignorance if so .. Good luck and cheers 👍😀
FD returns is 6 percent.. it’s simple to understand
In shares
We invest different amount on different dates on different shares.
If all taken together minus the expenses, what is the way I know at any point of time what is my return on investment.
For example
I have invested 1 lakh rupees from jan 2021to July 21 on different dates, different shares at different prices. What is the ROI after considering expenses for using platform like ZERODA and incomes including dividends and excluding short term taxes ?
Do you have a ready made excell chart??
FD returns is 6 percent.. it’s simple to understand
In shares
We invest different amount on different dates on different shares.
If all taken together minus the expenses, what is the way I know at any point of time what is my return on investment.
For example
I have invested 1 lakh rupees from jan 2021to July 21 on different dates, different shares at different prices. What is the ROI after considering expenses for using platform like ZERODA and incomes including dividends and excluding short term taxes ?
Do you have a ready made excell chart??
@CARachanaPhadkeRanade I want to calculate xirr for the below scenario: There is an ideal chit fund of 1lakh[20members,20months], for equated installments of 5k per month. If the chit is used/taken from next month we need to pay 6k till the last month.
Can I calculate the returns/interest if the chit is taken in the middle months using xirr or is there any other parameter for that.
And one more scenario where we pay EMIs for PL the interest will be depreciating and principal increases... in that case how can we calculate the interest or return rate...
Correct me if my approach is wrong...
i am fond of your learning channel for last 2 years. Thanks a lot...Just would like to know that in those 4 secnarios if a person does not know the amount he/she will get at the end of investment tenure and instead knows IRR/XIRR %, and available other parameters like investment amount and date and IRR % then would it be advisable to figure out the Lumpsum?
In addition to that can we use IRR only in place of CAGR ( case of fix re-investable lumpsum with same rate of return case through out the tenure)?
Thanks mam in advance!
humble request @RachanaRanade:please answer
Does XIRR = 100% means 100% return , means you invested 10,000 for a period of time got 20,000 as outcome.?
Madam,
In MS Excel, the date format you recommend is not supporting/working....
Pls suggest.
Hi the does the date have to be in the date format to calculate this . I am facing a problem to type 1-03-20 ( eg Ist March ) it shows as 03-01-20 ie the format is never DD-MM-YY but always MM-DD-YY .. please help
How to account for dividends/buybacks on investment in equity shares. Also, how to include bonus shares issued by any company, if any.
Looking forward to hear from you !! Thankyou in advance !!
Can the same formula be used for calculating returns of 20 Yr SIP ?
Its showing very less % XIRR ( for Nifty bees where current value is almost 2.5X of total investment)
Many a times, i have found that xirr does not work, whatever may be the date format . In my opinion, MS-Excel XIRR is wrong. Try this for several values and different scenarios. Every time i get a different answer. This has been reiterated by many and please see this link and kindly clarify in your Q&A or a separate video. BTW, your videos are a very well presented ruclips.net/video/QerPzdK1nGs/видео.html However if you can find a solution to this problem, that is, whether MS-EXCEL is wrong or the users are wrong, it would benefit all your patronisers
IRR doesn't take into account when the actual cash flow takes place, so it rolls them up into annual periods. By contrast, the XIRR formula considers the dates when the cash flow actually happens. Because of this, XIRR is a more accurate way to evaluate an investment.
Very helpful knowledge ☺️
Ma'am I didn't understand one concept. Is the final value(14500) that u have taken is by your own or the amount came using some Formula as I can see the invested amount is 13000. Kindly explain.
But Madam, On Coin App they shows CAGR of Particular fund And Expense Ratio and Exit Load. Does XIRR have any relevance in such case?
The answer is 1. 8.42,
But the thing is My Exel recognizes the date formate to be right as (MM-DD-YYYY), and it pulls an error if I use the format (DD-MM-YYYY).
Guys, also keep in mind the time format that your excel recognizes. !
Happy Learning. !!!
You can modify the settings buddy.