Quick answer

To calculate SIP returns using XIRR in Excel, enter each SIP investment date and amount, add the current mutual fund value as a positive number, and use the XIRR(values, dates) formula. XIRR calculates your annualised return based on your actual investment dates and cash flows.

In this guide, you’ll learn how to calculate SIP returns using XIRR in Excel with a simple mutual fund example.

What Is XIRR in Mutual Funds?
Why CAGR Is Not Suitable for SIPs
4 Steps to Calculate Your SIP XIRR in Excel
Example: Calculating XIRR for a 12-Month SIP
How to Calculate XIRR for an Ongoing SIP
How to Handle Partial Withdrawals & Redemptions
Why Your XIRR May Not Match App Returns
XIRR vs CAGR
When XIRR May Not Be Needed
Benefits of Using XIRR
Limits of XIRR
Common Excel Errors in XIRR
Tips for Beginners
Who Can Benefit from XIRR?
Conclusion
FAQs

What Is XIRR in Mutual Funds?

XIRR stands for Extended Internal Rate of Return. In mutual funds, it shows the yearly return on investments made on different dates. With SIPs, every investment is made on a different date. Earlier instalments stay invested longer than later ones. So, all instalments cannot be treated as if they were invested on the same day.

To work out XIRR, Excel mainly needs three things:

The amount invested
The date of each investment
The current value or amount received

XIRR helps answer this question: “What annual return did I earn based on my actual SIP dates and amounts?”

Why CAGR Is Not Suitable for SIPs

XIRR works better for SIPs because investments are made over time, not all at once.

For example, if you invest ₹10,000 every month from January to December, your January SIP gets almost a full year to grow. Your December SIP gets only a few weeks if you check your value on 31 December.

A basic return calculation is:

Gain ÷ Total Investment × 100

This shows your profit percentage, but it does not tell you how long each SIP instalment stayed invested.

XIRR is useful for:

Monthly SIPs
Missed SIPs
Step-up SIPs
Extra mutual fund purchases
Partial withdrawals
Full redemption
STP and SWP transactions

4 Steps to Calculate Your SIP XIRR in Excel

To calculate SIP returns using XIRR in Excel, you need two columns:

Date
Cash flow

Cash flow refers to money invested or received.

SIP investment = negative cash flow
Redemption, withdrawal, or current value = positive cash flow

You only need the investment dates and amounts.

Step 1: Create the Excel table

Create a table like this:

Date	Cash Flow
SIP date	SIP amount or final value

Use the actual SIP dates from your mutual fund statement or investment app.

Step 2: Enter SIP amounts as negative values

A SIP is money you invest. So, add a minus sign before the amount.

Examples:

₹10,000 SIP = -10000
₹5,000 SIP = -5000

The minus sign shows money flowing out of your account.

Step 3: Add current value as a positive value

At the end of the table, add the current value of your mutual fund.

Example:

Current value = 128000

Enter this as a positive value because it represents money you would receive if you sold the units on that date.

If you have already redeemed the fund, enter the actual amount received.

Step 4: Apply the XIRR formula

If dates are in A2 to A14 and cash flows are in B2 to B14, use:

=XIRR(B2:B14,A2:A14)

Then format the answer as a percentage.

If Excel shows 0.12, it means 12%.

The same formula also works in Google Sheets.

Example: Calculating XIRR for a 12-Month SIP

Suppose Ananya invests ₹10,000 every month in an equity mutual fund from January to December 2025.

Her total investment is:

₹10,000 × 12 = ₹1,20,000

On 31 December 2025, her fund value is ₹1,28,000.

Her Excel table will look like this:

Date	Cash Flow
05-Jan-2025	-10,000
05-Feb-2025	-10,000
05-Mar-2025	-10,000
05-Apr-2025	-10,000
05-May-2025	-10,000
05-Jun-2025	-10,000
05-Jul-2025	-10,000
05-Aug-2025	-10,000
05-Sep-2025	-10,000
05-Oct-2025	-10,000
05-Nov-2025	-10,000
05-Dec-2025	-10,000
31-Dec-2025	1,28,000

Formula:

=XIRR(B2:B14,A2:A14)

Excel will calculate the annualised XIRR based on the dates and cash flows entered. The result may vary slightly depending on the exact investment and valuation dates.

Compare this with the absolute return:

Gain = ₹1,28,000 – ₹1,20,000 = ₹8,000

Absolute return = ₹8,000 ÷ ₹1,20,000 × 100 = 6.67%

The absolute return is 6.67%, while the XIRR is around 13 –14%.

How to Calculate XIRR for an Ongoing SIP

You do not need to redeem your mutual fund to calculate XIRR. If the SIP is still running, use the current value as the final positive cash flow.

Example:

Date	Cash Flow
05-Jan-2025	-10,000
05-Feb-2025	-10,000
05-Mar-2025	-10,000
05-Apr-2025	-10,000
05-May-2025	-10,000
07-Jul-2026	78,500

Formula:

=XIRR(B2:B7,A2:A7)

Here, 07-Jul-2026 is the date you check your portfolio value. ₹78,500 is positive because it is the value you would receive if you sold the units that day.

How to Handle Partial Withdrawals & Redemptions

XIRR also works when you withdraw part of your mutual fund and keep the rest invested.

Example:

Date	Cash Flow
05-Jan-2025	-10,000
05-Feb-2025	-10,000
05-Mar-2025	-10,000
05-Apr-2025	-10,000
15-Aug-2025	15,000
31-Dec-2025	32,000

Formula:

=XIRR(B2:B7,A2:A7)

The ₹15,000 withdrawal is positive because you received that money. The ₹32,000 current value is also positive because it is the remaining fund value.

Use this method if you’ve made a partial withdrawal and still own units.

Why Your XIRR May Not Match App Returns

Your XIRR may not match the return shown by your AMC or investment app. This does not always mean there is an error.

A fund’s CAGR shows how the scheme performed between two fixed dates. Your XIRR shows how your own money performed based on your SIP dates, amounts, extra purchases, and withdrawals.

For example, two investors may invest in the same flexi-cap fund. One starts when the market is low. Another starts after a market rise. Their returns may be different even if they invest in the same scheme.

Your personal XIRR depends on:

SIP start date
SIP amount
Missed SIPs
Extra purchases
Withdrawals
Current value
Redemption date

It provides a clearer picture of your SIP returns than only looking at the fund’s published return.

XIRR vs CAGR

CAGR and XIRR both show annual return, but they are used in different cases.

CAGR stands for Compound Annual Growth Rate. It is useful when you invest one lump sum amount and hold it for a fixed period.

XIRR is better when money goes in or comes out on different dates.

Point	XIRR	CAGR
Best for	SIPs and multiple cash flows	One-time lump sum investment
Uses exact dates?	Yes	No
Handles SIPs?	Yes	Not properly
Handles withdrawals?	Yes	No
Example	₹10,000 monthly SIP	₹1,00,000 lump sum

Advantages of Using XIRR Instead of CAGR

Why XIRR Is Better Than CAGR for SIPs	How XIRR Helps
Considers every SIP date	Calculates returns based on the actual date of each SIP instalment instead of assuming a single investment date.
Works with irregular investments	Handles SIPs, additional investments, withdrawals, and skipped instalments accurately.
Supports Step-Up SIPs	Calculates returns correctly even when your SIP amount changes over time.
Measures your actual return	Shows the annualised return based on your real cash flows, giving a more accurate picture than CAGR.
Makes fund comparisons easier	Lets you compare the performance of different SIP investments on a like-for-like basis.
Separates returns from contributions	Helps you see whether portfolio growth came from market returns or simply from investing more money.

When XIRR May Not Be Needed

XIRR may not be needed if you made only one lump sum investment and did not add or withdraw money later. In that case, CAGR is usually enough.

Use XIRR when there are multiple cash flows on different dates. This includes SIPs, step-up SIPs, extra purchases, SWPs, STPs, and partial withdrawals.

Limits of XIRR

XIRR only measures past performance: A 13% XIRR today does not mean the fund will keep giving 13% in the future. Mutual fund returns change with market conditions.
Short-term XIRR can be misleading: If you calculate XIRR after only one or two months, the result may look too high or too low. Small short-term changes can look large when converted into yearly return.
XIRR does not show risk: XIRR tells you the return number, but not how risky the fund was. A small-cap fund may show high XIRR during a market rise but can also fall more during corrections.
XIRR may not include tax or exit load: If you enter the current value before tax and exit load, your XIRR will be before these costs. For a cleaner post-redemption return, enter the actual amount received.
XIRR does not replace cost checks: XIRR shows your return, but you should still check the expense ratio. Expense ratio is the yearly cost charged by the mutual fund and can affect long-term returns.

Common Excel Errors in XIRR

Error or Issue	Why It Happens	How to Fix It
#NUM!	Missing positive or negative cash flow	Add SIP investments as negative values and the current fund value as a positive value.
#VALUE!	Dates are entered as text instead of valid Excel dates	Convert the dates to a proper Excel date format.
Very high XIRR	Investment period is too short	Calculate XIRR over a longer investment period.
Negative XIRR	Current fund value is lower than the total amount invested	Verify the latest fund value and check your entries.
Wrong result	Cash-flow signs are incorrect	Record investments as negative values and money received as positive values.
Formula not working	Cash-flow range and date range have different numbers of rows	Make sure both ranges contain the same number of entries.
Duplicate dates	Multiple cash flows are recorded on the same date, causing unexpected results	Check whether duplicate dates are intentional. If not, correct or combine the entries.
Blank rows in the selected range	Empty cells are included in the cash-flow or date range	Remove blank rows or select only the cells that contain data.
Dates entered as DD/MM/YYYY text	Excel treats the dates as text instead of date values	Convert the text to actual Excel dates using DATEVALUE, Text to Columns, or by changing the cell format.
Incorrect regional date format	Excel interprets dates incorrectly because of regional settings (for example, MM/DD/YYYY vs DD/MM/YYYY)	Ensure the workbook uses the correct regional date format or re-enter the dates in a format Excel recognises.

Most XIRR errors are caused by incorrect cash-flow signs, invalid dates, or a missing final portfolio value.

Tips for Beginners

Use the actual dates from your statement.
Enter SIPs with a minus sign.
Enter withdrawals and current value as positive numbers.
Do not judge an equity SIP based on a few months of XIRR.
Review SIP returns once or twice a year.
Compare funds within the same category.
Check risk, cost, tax, and goal fit along with XIRR.
Use online financial calculators for planning.
Read about direct vs regular mutual funds because costs affect long-term returns.
If you are new to mutual funds, start with the guide on how to invest in mutual funds in India.

Conclusion

XIRR gives a clearer picture of your SIP returns than a simple profit calculation. Enter your cash flows correctly in Excel, and use it along with other factors such as risk, costs, taxes, and investment goals. For mutual fund investors in India, it gives a better view than only checking total profit or absolute return.

To calculate it in Excel, enter SIP investments as negative values, add current or redemption value as a positive value, and use =XIRR(values, dates). Use XIRR to track performance, but don’t make investment decisions based on XIRR alone. Also check risk, cost, tax, fund category, and your goal.