XIRR Excel Function
For example, suppose the first investment example returns an internal rate of return in cell C10. The cash flow values are found in the range A1:A4 with the dates in B1:B4.
Assume that you have an initial investment of ₹7,500 on 1/1/2020. You then have the following income: ₹3,000 on 2/2/2021, ₹5,000 on 4/15/2021, ₹1,200 on 9/1/2021, and ₹4,000 on 10/3/2021.
=XIRR(B1:B4,A1:A4)
= 2.66.
Syntax
- value*: The transaction amounts. It refers to a series of cash flows corresponding to a payment schedule.dates*: The transaction dates. It refers to a series of dates corresponding to its respective transactionestimated_irr: Optional. The approximate return. Default=10%.
How to Use The XIRR Function in Excel
Let us take a few XIRR calculations in Excel examples before using the XIRR excel function workbook:
Example #1
Suppose you invest ₹8,000 in Mar 2012. You receive an amount of ₹2,000 at different time intervals from March to December 2017. 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 ₹2,000 multiple times from 1 April 2017 to 10 Dec 2017. In the end, you receive an amount of ₹20,000 on 5 March 2018. In this case, your input in the Excel sheet should be 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 invested an amount of ₹8,000 in March 2011 in mutual funds. After seeing good returns on your amount, you invested every year with a 10% increase each time. In the 8th year, you received an amount of ₹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).
We can also consider the above example differently. On your first investment, you receive a total amount of ₹8,800 (10% on your investment) in a year. So, you decide to invest this amount, which gives you a return of 10% again. This cycle goes on for consecutive 7 years, and you receive an amount of ₹1,00,000 in the 8th year.
Example #4
Suppose you invest ₹8,000 in three consecutive years and receive a total of ₹28,000 in the next five years. In this case, both the investments and the redemptions are made over some time. Therefore, the input in Excel will be as shown below:
For calculating 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 applies to any investment portfolioInvestment PortfolioPortfolio investments are investments made in a group of assets (equity, debt, mutual funds, derivatives or even bitcoins) instead of a single asset with the objective of earning returns that are proportional to the investor’s risk profile.read more with multiple cash flows over time. Some include Mutual fundMutual FundA mutual fund is a professionally managed investment product in which a pool of money from a group of investors is invested across assets such as equities, bonds, etcread more SIP, money back plans, PPF, EPF, etc. Sometimes you may want to see your returns on your investments in the share marketShare MarketThe share market is a public exchange where one can buy and sell company shares based on the demand and supply of shares. read more over the last 10 years. We can also use the XIRR Excel to combine 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 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 article is a guide to XIRR Excel Function. Here, we discuss the XIRR formula in Excel, how to use XIRR in Excel, and practical examples and downloadable Excel templates.
- COUNTA Function in ExcelCOUNTA Function In ExcelThe COUNTA function is an inbuilt statistical excel function that counts the number of non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but, cell A2 is empty. The formula “=COUNTA(A1,A2,A3)” returns 2.
- read moreSWITCH in ExcelSWITCH In ExcelSwitch function in excel is a comparison and referencing function which compares and matches a referred cell to a group of cells and returns the result based on the first match found. The method to use this function is =SWITCH( target cell, value 1, result 1….).read moreMax Formula in ExcelMax Formula In ExcelThe MAX Formula in Excel is used to calculate the maximum value from a set of data/array. It counts numbers but ignores empty cells, text, the logical values TRUE and FALSE, and text values.read moreExcel PRODUCT Formula