fbpx

What is XIRR in Mutual Funds?

0 59

XIRR in Mutual Funds

Everyone who invests in any asset class, whether its real estate, shares, bond, Gold, ETFs, Mutual fund and others expect a return over a certain period. Returns are always the benchmark to judge your investment decision. Higher returns mean, better investment decision. There are numerous ways to measure the returns like IRR & CAGR. One of the simplest and best ways to measure the return in a mutual fund is through XIRR formula.  

What is XIRR?  

XIRR is a short-used version of the Extended Internal rate of return. It is basically a method used to calculate the rate of return when an individual investment and withdrawal is not the same over different time periods. It helps in calculating the rate of returns, where there are multiple transactions are happening at different times, especially in the case of mutual funds. In a mutual fund, most of the investment happens at different time periods for each installment, calculating returns is a bit tricky. XIRR helps in calculating the return easily. 

How to Calculate the XIRR?  

The best way to calculate the XIRR is through Excel. Microsoft Excel has a simpler process of calculating the internal rate of return through financial functions. Let’s look at the process of calculating the XIRR.   

  • First, write down the XIRR formula in excel.  
  • Then, enter all your transactions in one column. All your outflows in another column.  
  • Make sure that all the outflows like an investment, purchase are negative and while all the inflows marked with positive.  
  •  Write down the date of the transaction in the next column. 
  • In the last row, write down the current value of your holding and current date.  
  • Now, Use the XIRR functions in excel, which represents like XIRR = (values, dates, Guess)

Let’s try to understand the situation through examples:  

SIP Amount = Rs 5000 

Investment Horizon = start = 01/01/2019, End = 01/06/2019 

Redemption Date = 01/07/2019 

Redemption or Maturity amount = 31,000 
 

Column A   Column B 
01/01/2019  -5000 
01/02/2019  -5000 
01/03/2019  -5000 
01/04/2019  -5000 
01/05/2019  -5000 
01/06/2019  -5000 
01/07/2019  31000 
  11.88 

 

In this example, we have followed certain steps:  
 

  • In this series of Column, A, we have put the dates of the transactions on the left side. 
  • In the series of Column B, we have the enter the SIP amount in negative as the cash flow is negative.  
  • Afterwards, against the redemption date, write down the redemption amount.  
  • In this type, below type 31,000, type in = XIRR (B1: B7, A1: A7) *100” and hit enter.  

Bottom line:  

In case of a series of investments being made successively over time, whether its withdrawals, dividends, switch etc. The best way to calculate the return is with XIRR. It is a better way to calculate the returns as compared to IRR and CAGR. 

Rating: 5.0/5. From 7 votes.
Please wait...
Voting is currently disabled, data maintenance in progress.

Leave a Reply

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept