XIRR Excel Function (Formula, Examles) | How to use XIRR in Excel

Excel Functions Tutorials

XIRR in Excel (Table of Contents)

XIRR Excel Function
XIRR Formula in Excel
How to Use XIRR in Excel?

XIRR Excel Function

Many times, the investments and redemptions are made over a different period of time such as in lump sums, money-back plans, etc. In such cases, the XIRR Excel (Extended Internal Rate of Return) is used to calculate the rate of return. For a series of periodic cash flows, the rate of return is calculated using IRR (Internal Rate of Return).

XIRR Excel is a sum of multiple CAGRs (Compounded Annual Growth Rate) with regular cash flow. Let’s first try to understand CAGR. Suppose you invested Rs. 1,00,000 five years ago and you have received an amount of Rs. 1,25,000 on that investment. The CAGR can be calculated as:

(125000/100000)^1/5 – 1 = 4.56%.

However, if you invest an amount say Rs. 100,000 at the start and you receive the amount at different time intervals say Rs. 10,000 in the first quarter, Rs. 10,000 in the next quarter and so on, then, an aggregation of multiple CAGR are calculated to find the rate of return. This aggregation is termed as XIRR.

CAGR is important when the idea is to select a fund. It is done by looking at its return in the past few years. But XIRR excel is essential in evaluating the returns received on the investments you have made in the past. In real life scenarios, the investments and redemptions are mostly irregular and happen over a different period of time. These irregular cash flows can only be calculated using XIRR.

There is no XIRR formula for XIRR calculation in excel, and XIRR is calculated using Excel as

Recommended Courses

XIRR Formula in Excel

Explanation of XIRR Excel Function

Value*: The transaction amounts. It refers to a series of cash flows corresponding to a schedule of payments
Dates* The transaction dates. It refers to a series of dates corresponding to its respective transaction
estimated_irr: Optional. The approximate return. Default=10%

As we have seen in case of annuity and other financial functions, the cash outflow (amount deposited/invested) is kept negative and the cash inflow (amount received) is kept positive.

How to Use The XIRR Function in Excel

Let’s take a few XIRR calculations in Excel examples, before using XIRR excel function workbook:

XIRR Formula Example #1

Suppose you invest Rs. 8000 in Mar 2012 and you receive an amount of Rs. 2000 at different time intervals from March to December 2017. In this case, your input in the excel sheet would contain the time and corresponding amount as shown below.

The XIRR excel can thus be calculated as XIRR(values, dates) as shown below

In this case, XIRR = 7.7%

XIRR Formula Example #2

Suppose you invest Rs. 2000 multiple times from 1 April 2017 to 10 Dec 2017. In the end, you receive an amount of Rs. 20,000 on 5 March 2018. In this case, your input in the excel sheet should like this

To calculate the rate of return on this investment, you will provide the input as XIRR(values, dates) as shown below

You will find that the XIRR in the above case is 0.78.

XIRR Formula Example #3

Let us assume that you started to invest an amount of Rs 8000 in March 2011 in mutual funds. After seeing good returns on your amount, you invested every year with 10% increase each time, and in the 8^th year, you received an amount of Rs. 100,000. The input, in this case, will be as shown below:

The XIRR will be calculated as XIRR(values, dates) à XIRR(B3:B10, A3:A10)

The above example can also be considered in a different way. On your first investment, you receive a total amount of Rs. 8800 (10% on your investment) in a year. You decide to invest this amount which gives you a return of 10% again and this cycle goes on for consecutive 7 years, and you receive an amount of Rs. 1,00,000 in the 8^th year.

XIRR Formula Example #4

Suppose you invest Rs. 8,000 in three consecutive years and receive a total of Rs. 28,000 in the next five years. In this case, both the investments and the redemptions are made over a period a time. The input in the excel will be as shown below:

To calculate the rate of return on this transaction, the XIRR function will be given by XIRR(values, dates) as shown below:

The XIRR here is 0.037.

XIRR Excel Function Applications

The XIRR in excel is applicable in any investment portfolio having multiple cash flows over a period of time. Some of them include Mutual fund SIP, money back plans, PPF, EPF, etc. Sometimes you may want to see your returns on the investments you made in share market over the last 10 years. The XIRR excel can also be used in a combination of several investments made at different places to calculate the overall rate of return.

Things to Remember About The XIRR Function in Excel

The amount invested (outflow) should be counted as negative and the amount received (inflow) as positive
The cash flow values can be listed in any order.
There should be cash outflow and inflow. If either one is missing, the XIRR function will return the #NUM! error.
The dates should be valid. Providing an invalid date in the date parameter will result in #NUM! error in the XIRR function.
The number of values and dates should be equal. Unequal numbers will result in an error.

You can download this XIRR Excel template here – XIRR Function Excel Template

XIRR Excel Function