I remember when I first entered the investment industry, and for the very first time using the xirr calculator. And the truth be told it took me some good to comprehend the xirr meaning practically. So I will try my best to use my real live experiences with investments, in making you understand this concept.
XIRR meaning
Now, a lot of you might have come across situations where calculating return with actual dates is a challenge. That’s because in your academic journey, you and me have been taught the Internal rate of return formula. Which basically averages the rate of return in case of uneven cash flows.
For example;
- An even cash flow scenario might look like this; you invest $100 in the year 2024 and continue with regular $5 additional investment until year 2030.
- But real life scenarios are usually uneven. For example you invest $100 on a specific date lets say 15 Aug 2023, then you withdraw $10 for urgency expenses constituting a cash outflow, and again add $1000 on 31st December 2024.
Hence XIRR return means the compounded annual growth rate or annualised returns, but with real dates and real cashflows.
XIRR Full form
Believe it or not but there is a full form of XIRR. XIRR stands for extended internal rate of return. I always wondered though, why it’s abbreviation is not EIRR( Extended IRR).
XIRR Formula in Excel
Now let me take you through step by step, on how to calculate xirr in excel.
The formula for XIRR is based on the following equation:
where:
C_t= Cash flow at time t
( t \) = Time period (0, 1, 2, …, n)
( n ) = Total number of periods
( XIRR ) = Internal Rate of Return
Now how do you do this in excel, so
Additional points
If you will definitely notice, that in the function in excel, the formula parenthesis gives you an option of guess. However, while using the XIRR return calculator this functionality will be used when the cashflows change their signs too frequently. In such cases, we can use the guess functionality to point to an approximate estimated returns.
XIRR Calculator
I have created this customised calculator to calculate XIRR. You may use your own real life examples to calculate annualised returns.
XIRR Calculator
Examples: XIRR in Mutual Fund
When I was very new to the investment domain, I used to be very interested in knowing things like;
- How much XIRR in SIP can be made by investing monthly versus quarterly.
- Most of the mutual fund schemes do present their NAV data, which helps in you to calculate the annualised returns.
I am attaching an excel data of Franklin tempelton mutual fund prima fund net asst value data from 2023 to 2024.
- Daily SIP amount is INR 500
- Date of withdrawal is 6TH JULY 2024
Now using this template calculate the XIRR in mutual funds.
Mutual Fund Documents
Whenever you will come across any scheme related documents of any mutual fund. Then you need to assume that the XIRR meaning in mutual funds is CAGR. For example below I have one of the performance snapshot, of one of the mutual funds where I regularly invest. What you should notice, is that the 3 years return, 5 years return are all XIRR calculation.
So hopefully that will clarify what is xirr in mf.
Mutual fund XIRR CALCULATOR
SIP Calculator
XIRR Practice Questions
I would highly recommend you to practice some of the questions below, to get a solid hang of this concept.
Practice Question 1
Cash Flows:
- Initial Investment: -₹10,000 on 01/01/2020
- Cash Flow 1: ₹12,000 on 01/01/2021
Answer:= 20%
Practice Question 2
Cash Flows:
- Initial Investment: -₹5,000 on 01/01/2019
- Cash Flow 1: ₹1,000 on 01/07/2019
- Cash Flow 2: ₹1,000 on 01/01/2020
- Cash Flow 3: ₹1,000 on 01/07/2020
- Cash Flow 4: ₹5,500 on 01/01/2021
Answer: XIRR= 20.48%
Practice Question 3
1. Initial Investment: -₹5,000
2. Date: 01/01/2019 Cash Flow 1: ₹1,000
4. Date: 01/07/2019 Cash Flow 2: ₹1,000
Date: 01/01/202 Cash Flow 3: ₹1,000.
Date: 01/07/2020Cash Flow 4: ₹5,500
Date: 01/01/2021
Answer: XIRR= 22.86%
Practice Question 4
Cash Flows:
- Initial Investment: -₹20,000 on 01/01/2018
- Cash Flow 1: ₹5,000 on 01/01/2019
- Cash Flow 2: ₹5,000 on 01/01/2020
- Cash Flow 3: ₹25,000 on 01/01/2021
Answer: XIRR= 23.72%
Frequently Asked Questions
is xirr annualized returns?
Yes, the XIRR will automatically annualise the returns. For example look at these calculation that I have done for Prima fund. Notice that I have calculated xirr only for July but the returns shows as 98%.
What is the XIRR full form in mutual fund?
The xirr full form stands for Extended internal rate of return, which is nothing but a more accurate version of IRR calculation.
How to calculate xirr manually?
While I would never recommend to calculate xirr manually. However, below are the steps by which you can calculated the XIRR extended internal rate.
- Add the multiple cash flows in one column in excel, including the final portfolio value.
- Now, create a label in blank cell and write it as rate.
- Now use the net present value formula (NPV( Rate, cashflows )+ Initial invested amount(0).
- Use different rates until your NPV shows as zero.
What is the use of XIRR?
XIRR is one of the very important capital budgeting functions, which helps you in making investment decisions. It gives you basically the rate of return IRR. However, note that rate of return can look high, merely because of a certain arrangement of the cashflows.
How much XIRR is good?
So, a simple rule of thumb according to my experience is that, the annualized rate of return xirr needs to be compared to cost of capital. If your XIRR is lets say 12% and cost of capital is 9% (interest rate) then 12% is a good IRR.
What is the meaning of XIRR in mutual fund?
The compounded annual growth rate mentioned by any mutual fun scheme is nothing but the xirr returns.
Is XIRR better than CAGR?
Actually there is better or worse. Both have different applications. While the former is for uneven cash flow situation, whereas CAGR is calculated when we have even cashflows. But both mean the same in terms of understanding.
