What is XIRR? How to compute the same?
Cash inflows and outflows may not always be evenly matched and instead, these could be at irregular intervals.
Specially, in a mutual fund SIP. In the case of SIP, there are investments made at regular intervals, some withdrawals, then investments and so on. There is no fixed pattern of such investments and it makes calculating the exact return on these investments a bit difficult.
XIRR or extended rate of return is a measure of return when multiple investments at different points of time are made in a financial instrument.
SIP Investments Method
In a SIP, you keep investing regularly over a long period and get back the maturity amount upon exit. SIP investments happen on a pre-decided date and even the amount is fixed and depending on the NAV of the scheme on that day, you get a certain number of units. You can read more about SIP in our Article http://www.wealthcafe.in/why-should-you-do-a-sip/
Hence, you keep accumulating units from the day your SIP starts. On the day you exit the scheme, i.e., redeem your total units, you get the maturity amount, which is NAV (of redemption day) multiplied by total units (on redemption day). You may also choose to redeem a part of your investments as and when you need them.
XIRR is used to calculate the return in the case above where various investments are made on different dates and the simple return formula is not applicable.
XIRR can be computed using an excel as excel has an inbuilt XIRR formula. To compute XIRR, we do not need the NAV amount or number of units.
The details required :
- SIP Amount
- SIP dates
- Any lumpsum Investments
- Date of such investments
- Redemption Amounts
- Date of Redemptions
Steps to Compute XIRR. (The steps are explained with reference to the image below)
Step 1 – Enter all the transactions in column B
Step 2 – In the next column (Column C), enter all the amounts of SIP and the lump sum investments. All the investments amount should be in negative. Also, any lump sum amount should be added to this column and the same should also be in negative.
Step 3 – In the case of redemption, add that amount in Column C in positive.
Step 4- In the next box, enter the XIRR formula which is = XIRR (select all dates, select all values)*100. This shall give you the XIRR amount.
You can see the extract of the excel in the photo below.