Excel XIRR Function | How to use Excel XIRR Formula (Examples)

XIRR Excel Function

XIRR function is also known as Extended Internal rate of return function in excel and this function is used to calculate based on multiple investments done on the same period of time, this is also a financial function in excel and is an inbuilt function which takes values dates and guesses value as inputs to it.

Syntax

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%

How to Use The XIRR Function in Excel

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

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

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

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

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

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

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 a 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.

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 of 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.

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

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.