XIRR MEANING

XIRR or Extended Internal Rate of Return is a dynamic and significantly used function in Excel/spreadsheet. It is used to calculate the personal returns of investors. In today’s time, where the markets are volatile, most investors opt to invest through SIP. So, multiple investments over different time periods can make the calculation of returns very complex and difficult since the more the variations, the more the purchase price varies. XIRR comes into the play here. XIRR gives you the present value of your investment and is a precisely calculated parameter which when applied to investments gives out a specific evaluation. XIRR can also be used in lump sum investments. In this article, we are going to explore more aspects of XIRR.

XIRR IN MUTUAL FUNDS

As discussed, XIRR is a phenomenon used to calculate returns on investments where there are multiple installments at different time intervals. When you invest in different mutual funds, you are investing through lump sum or SIP, redeeming either through lump sum or SWP, XIRR is the feature that will help you to ascertain the returns by taking into consideration withdrawals and investments. Thus, it can be quoted that XIRR is an aggregation of multiple CAGRs.

USE OF XIRR

Investment through SIP is considered ideal in many cases, but it is often seen that investments in the long term are irregular and the cash flows of the plan also vary. Sometimes, there are late deposits or early withdrawals, or sometimes, months in a row are skipped. In these cases, investors can’t rely on simple IRR.
Attention needs to be paid to factors like the amount of investment, the number of installments, and time intervals. These factors may determine the output differently with each investment. And this is the primary feature and function of XIRR, to provide you returns calculation by taking all the variations into consideration and presenting a consolidated return of a particular fund or scheme.

CALCULATION OF XIRR

There are two broad ways to calculate XIRR.

First is by using EXCEL

The formula used here is =XIRR(values,dates,[guess])

Here,

Value is the array of values represented by reference to a range of cells that represent the series of cash flows.

Dates are a series of dates that correspond to the given fund. Subsequent dates should be later than the initial date.

[guess] is an initial guess or estimate of what the IRR can be. The default value taken by excel is 10%.

The process to use this is as follows:

Make two columns and title the left one with ‘DATE’ and list all the inflow/outflow dates.
Title the right column with “VALUES”. Write down the redeemed amounts in positive and the investment made in negative since they are outflows from you.
The last row of your sheet should contain the current value of your holding and the current date.
Just below it, use the XIRR function.
Now, you have to insert the respective value by referencing or typing corresponding to the respective date.
Now simply press enter and enjoy the precise analysis of your investments.

The second way

It is not much different but it rules out the guess part from the above process

The steps here are:

Transaction dates are listed on the left column.
Transaction values are written on the right column. Write down the redeemed amounts in positive and the investment made in negative since they are outflows from you.
The formula to be used here is “=XIRR(values,dates)*100

DIFFERENCE BETWEEN CAGR AND XIRR

CAGR only considers the initial value, number of years, and the end value and does not consider multiple cash flows. Whereas, XIRR considers the multiple cash flows whether withdrawn or invested.
CAGR gives investors the compounded growth rate whereas XIRR is the average rate earned by each and every cash inflow/outflow made during the period.
XIRR is the measure of performance of cash flows whereas CAGR is the measure of performance of lump-sum investments.
XIRR gives the annualized returns whereas CAGR gives us the absolute return as it is an end-to-end return indicator.
Both the indicators will show different values if investors invest multiple times and also make withdrawals in the middle.
Both the indicators will show different values if the annual returns are not the same.

SIMILARITIES BETWEEN CAGR AND XIRR

Both the indicators will show the same values if you invest lump sum only once and calculate them after one year.
Both the indicators will show the same values if investors are making multiple investments but the annual returns are the same throughout the tenure.

Frequently Asked Questions (FAQs)

1. What is XIRR?

XIRR or Extended Internal Rate of Return is a dynamic and significantly used function in Excel/spreadsheet used to calculate the personal returns of investors when investing through SIP.

2. What is the use of XIRR?

The primary feature and function of XIRR are to provide you returns calculation by taking all the variations into consideration and presenting a consolidated return of a particular fund or scheme. It pays attention to factors like the amount of investment, the number of installments, and time intervals. These factors may determine the output differently with each investment.

3. What is the formula used in excel to calculate XIRR?

The formula of XIRR in excel is “=XIRR(values,dates,[guess])”

4. What is the key difference between CAGR and XIRR?

CAGR only considers the initial value, number of years, and the end value and does not consider multiple cash flows. Whereas, XIRR considers the multiple cash flows whether withdrawn or invested.

Also Read:

SIP vs STP vs SWP - Suitability, Tax Impact, Advantages
What is Expense Ratio in Mutual Funds & How they Work
Best Large Cap Mutual Funds to Invest in India
Best Small Cap Mutual Funds to Invest in India
Best Multi Cap Mutual Funds to Invest in India
Best ELSS Mutual Funds to Invest
Section 80D - Deductions, Tax Benefits, Claim Amount, Example
Information Ratio: Meaning, Features, Formula, Example
Difference between Large Cap, Mid Cap and Small Cap Funds