Missed filing your ITR?

You can still file it on Cleartax in just minutes

What is XIRR in Mutual Funds: Meaning, Example, Formula, How to Calculate XIRR

Updated on: Mar 18th, 2024

|

4 min read

social iconssocial iconssocial iconssocial icons

You may invest in mutual funds for regular income, capital appreciation or both depending on the mutual fund type. You could measure the return from mutual funds using CAGR (Compound Annual Growth Rate) and XIRR (Extended Internal Rate of Return).

You will find CAGR showing you the rate at which your mutual fund investment grows per year over the investment period. However, you cannot use CAGR to calculate the return for multiple cash flows.

You may consider using XIRR to calculate mutual fund returns for multiple cash flows. For example, you may use XIRR to determine the return from mutual fund investments through the systematic investment plan (SIP).

What is XIRR?

You can put a lump-sum amount in a mutual fund. You have a one-time redemption where you sell the entire investment after some time. Investing through a lump sum involves two cash flows. You have a cash outflow which is your investment, and a cash inflow (redemption). 

You may consider using CAGR to calculate the return if you invest a lump-sum amount in a mutual fund scheme. However, you may invest in mutual fund schemes through the SIP or redeem units through the systematic withdrawal plan (SWP).

It involves multiple cash flows, and you cannot use CAGR to determine returns from the mutual fund. You can use XIRR to determine returns from mutual funds through SIP (multiple-cash flows through SIP, Top-ups) and SWP (multiple-cash flows through SWP, dividends, partial redemption and so on).

How to calculate XIRR in Excel?

You can use the XIRR formula in excel sheets to calculate return from mutual funds for multiple cash flows. You must enter all mutual fund transactions such as SIP, SWP, additional purchases or redemptions, and the corresponding dates.

The XIRR formula is the modification of IRR (Internal Rate of Return) and factors irregular periods. You will have to enter SIP transactions, and the corresponding dates from mutual fund statements in the excel sheet. You then apply the XIRR formula to calculate SIP returns.

For example, you invest Rs 3,000 per month in a mutual fund scheme through SIP. You may calculate mutual fund returns through SIP using XIRR Formula in excel, as shown below.

how to calculate xirr in excel

  • You must enter all amounts you have invested with a ‘negative’ sign. It includes your SIP instalments and additional repurchases. 
  • You must enter all redemption amounts with a ‘positive sign.

Procedure to calculate XIRR using excel

  • You must open an excel sheet and enter SIP transaction dates in Column A.
  • You then enter the SIP instalment as a negative figure in Column B.
  • Enter the redemption amount against the redemption date in Column B.
  • You have XIRR (values, dates, [guess]). Use the formula =XIRR (B5:B15, A5:A17) * 100 and hit the enter button.

XIRR vs CAGR: What is the difference between XIRR and CAGR?


Particulars

CAGR

XIRR

Description

It shows the annual growth of mutual fund investments over some time





It shows the average rate earned by every cash flow that you invest during a period





Multiple Cash Flows

Not Considered

Considered

Return





Measures Absolute Return

Only Annualised Return

Use





Useful for measuring the return from lumpsum investment in mutual funds





Measures mutual fund returns through SIP and SWP
CONTENTS

Clear offers taxation & financial solutions to individuals, businesses, organizations & chartered accountants in India. Clear serves 1.5+ Million happy customers, 20000+ CAs & tax experts & 10000+ businesses across India.

Efiling Income Tax Returns(ITR) is made easy with Clear platform. Just upload your form 16, claim your deductions and get your acknowledgment number online. You can efile income tax return on your income from salary, house property, capital gains, business & profession and income from other sources. Further you can also file TDS returns, generate Form-16, use our Tax Calculator software, claim HRA, check refund status and generate rent receipts for Income Tax Filing.

CAs, experts and businesses can get GST ready with Clear GST software & certification course. Our GST Software helps CAs, tax experts & business to manage returns & invoices in an easy manner. Our Goods & Services Tax course includes tutorial videos, guides and expert assistance to help you in mastering Goods and Services Tax. Clear can also help you in getting your business registered for Goods & Services Tax Law.

Save taxes with Clear by investing in tax saving mutual funds (ELSS) online. Our experts suggest the best funds and you can get high returns by investing directly or through SIP. Download Black by ClearTax App to file returns from your mobile phone.

Cleartax is a product by Defmacro Software Pvt. Ltd.

Company PolicyTerms of use

ISO

ISO 27001

Data Center

SSL

SSL Certified Site

128-bit encryption