Calculating XIRR is quite easy in Excel, you just need to know: 1) How to select the data to calculate XIRR, and 2) What does XIRR mean.
What is XIRR and how is it different from CAGR?
The XIRR…
- stands for Internal Rate of Return
- considering each instalment
- paid at a different data (it does not need to be a regular payment, i.e. monthly, weekly, yearly). The payments can be at any date
Example: Lets assume you have been making an SIP payment of Rs. 10,000/- per month since 1st January 2020 and the last SIP was deducted on 1st November 2021. The total amount invested till date is: Rs. 2.3 Lacs.
Today, i.e. 13th November 2021, lets assume the value of this investment is Rs. 3.0 Lacs. So, as of today, the XIRR (or Internal Rate of Return), considering each instalment paid at 1st of every month since 1st Jan’20, is 30.70%.
How to calculate XIRR using Excel?
Using the example used above, below screenshot shows how to calculate the XIRR in Excel:
Steps to follow:
- List down the Date of Cash Outflow and the amount of outflow. Do note that outflow are mentioned as negative numbers (in our example cells D3 to D25 are Cash Outflow). Also, the dates can be any but as our SIP is assumed to get deducted on 1st of every month, hence we have used that as the day of the month
- Also, mention the date as of which you need to know XIRR and the Current Value against it. In the above screenshot cell C27 is the date of calculation (i.e. today’s date) and D27 is the assumed Current Value of investments
- Now, the formula to use is: XIRR ( <Range representing Cash Outflow and Current Value>, <Range representing the dates>, [guess] )
The formula will give you a value in decimals (such as 0.3070, using our example). If you change the format of this value to % it will show like: 30.70%.
But in the screenshot, we multiplied the result with 100 to make the value show in absolute terms.
I hope the explains the concept of XIRR and how it can be used to calculate the return of multiple tranches of an investment made at different dates.