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:
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
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 8th 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 8th 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.
Recommended Articles
This has been a guide to XIRR Excel Function. Here we discuss the XIRR Formula in excel and how to use XIRR on Excel along with practical examples and downloadable excel templates.
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
