What Is XIRR?
XIRR stands for Extended Internal Rate of Return. It is the annualised rate of return for an investment that involves multiple cash flows — contributions or withdrawals — each happening on specific dates.
The "extended" part distinguishes it from plain IRR (Internal Rate of Return), which assumes cash flows happen at perfectly regular intervals (e.g., every month on the 1st). XIRR lifts that restriction — it works with any dates, irregular amounts, and any combination of investments and redemptions.
Think of XIRR as the annualised return your specific portfolio earned, given your exact investment history — not the fund's advertised return, but your personal return.
Why CAGR Is Not Enough for Most Pakistani Investors
CAGR is the right tool when you invest one lump sum and hold it without touching it. The formula is elegant and easy to compute. But most Pakistani investors don't invest that way.
If you invest in a mutual fund through a SIP — say Rs 10,000 every month — each month's contribution is invested for a different amount of time. The first instalment works for three years. The instalment you made last month has worked for only 30 days. CAGR cannot distinguish between these — it only knows a starting value and an ending value.
XIRR is built precisely for this case. It asks: given every cash flow and its exact date, what single annual rate explains the outcome?
| CAGR | XIRR | |
|---|---|---|
| Best for | Single lump-sum investment, one start date, one end date | SIP, irregular top-ups, partial redemptions |
| Number of cash flows | 2 (start + end) | Unlimited |
| Accounts for dates? | Only start and end year | Every transaction's exact date |
| Measures your personal return? | No — measures fund performance only | Yes — measures what your money earned |
| Used in Excel? | =POWER(end/start, 1/years) − 1 | =XIRR(values, dates) |
The Math Behind XIRR (Without the Scary Parts)
XIRR finds the rate r that makes the Net Present Value (NPV) of all your cash flows equal to zero. In plain English: it finds the interest rate at which the present value of what you put in exactly equals the present value of what you get out.
NPV = Σ [ Cᵢ ÷ (1 + r)dᵢ/365 ] = 0
- Cᵢ — cash flow at event i (negative for investment, positive for redemption)
- r — the XIRR rate (what we are solving for)
- dᵢ — number of days between the first transaction and event i
Because there is no closed-form algebraic solution for
r, XIRR uses an iterative algorithm — specifically
the Newton-Raphson method — which starts with a guess
(usually 10%) and refines it step by step until the NPV is close
enough to zero. Excel's =XIRR() function does this
automatically. So does our
XIRR Calculator.
You don't need to understand Newton-Raphson to use XIRR. You just need to understand what the number means: it's your annualised return, accounting for every rupee and every date.
A Step-by-Step Example: 3-Year Mutual Fund SIP
Suppose you invested in an equity mutual fund with a monthly SIP of Rs 10,000 from January 2023 to December 2025 — 36 months, Rs 3,60,000 total invested. At the end of December 2025, your portfolio value is Rs 5,20,000.
What is your XIRR? Here's how to set it up in Excel or Google Sheets:
| Column A — Date | Column B — Cash Flow | Notes |
|---|---|---|
| 01-Jan-2023 | −10,000 | Investment (negative) |
| 01-Feb-2023 | −10,000 | Investment (negative) |
| 01-Mar-2023 | −10,000 | Investment (negative) |
| … (33 more rows) | −10,000 | Each monthly SIP |
| 31-Dec-2025 | +5,20,000 | Current portfolio value (positive) |
In Excel or Google Sheets, enter this formula:
The result: approximately 22–25% per year, depending on which months the market performed strongly.
Now compare that to how a fund might report its performance: if the NAV grew from Rs 100 to Rs 200 over three years, the fund's CAGR is 26%. But your XIRR is lower — because most of your money was invested partway through the period, not from day one. The fund's CAGR and your personal XIRR are different numbers measuring different things.
When a mutual fund's fact sheet says "3-year CAGR: 28%", that's the return on a lump sum invested exactly three years ago. Your SIP return — your XIRR — will differ, sometimes significantly. Neither number is wrong; they're just measuring different things.
Real Pakistani Investment Scenarios
Scenario 1: Monthly SIP in a Pakistani Equity Fund
Investor invests Rs 15,000/month from July 2022 to June 2025 (36 months, Rs 5,40,000 total). Portfolio value in June 2025: Rs 8,10,000.
XIRR ≈ 19.2% per year. This beats Pakistan's CPI inflation of ~14% in that period — a real return of roughly 5%.
Scenario 2: Irregular Top-ups + Lump Sum
Investor starts with Rs 2,00,000 lump sum in January 2023, adds Rs 50,000 in June 2023 when the market dips, another Rs 1,00,000 in January 2024, and redeems everything in December 2025 for Rs 5,50,000.
| Date | Cash Flow |
|---|---|
| 01-Jan-2023 | −2,00,000 |
| 15-Jun-2023 | −50,000 |
| 02-Jan-2024 | −1,00,000 |
| 31-Dec-2025 | +5,50,000 |
XIRR here is approximately 24.1% per year. A simple CAGR calculation would be misleading because the three investments were made at different times — XIRR handles this correctly.
Scenario 3: NSS Bahbood or Sarwa Islamic Account
Many Pakistani investors use National Savings schemes that pay monthly profit. If you invest Rs 5,00,000 in a Bahbood Savings Certificate at a declared monthly rate and receive monthly profit payments, you can use XIRR to calculate your effective annualised yield — including the benefit of reinvesting those monthly payouts elsewhere.
Enter the initial investment as a negative cash flow, each monthly profit payment as a positive cash flow, and the final maturity value as a positive cash flow on the maturity date. The XIRR result is your true all-in yield.
How to Calculate XIRR in Excel and Google Sheets
Step 1 — Set up your cash flow table
In Column A, enter dates in a date format Excel/Sheets recognises (e.g., 01/01/2023 or 1-Jan-2023). In Column B, enter the corresponding amounts — negative for money you paid out (investments), positive for money you received (redemptions, dividends, profit payments).
Rule: money leaving your pocket = negative. Money returning to your pocket = positive. Your final portfolio value on today's date counts as a positive cash flow — it's the value you would receive if you redeemed today.
Step 2 — Enter the XIRR formula
In an empty cell, type:
=XIRR(B2:B50, A2:A50) where B2:B50 is your cash flow
range and A2:A50 is your dates range. Format the cell as a percentage.
Step 3 — Troubleshoot errors
If you get a #NUM! error, the most common causes are:
- All cash flows are the same sign (you need at least one positive and one negative).
- Dates are stored as text, not as actual date values.
- The rate is so extreme (above 10,000%) that the solver diverges — check for data entry errors.
XIRR vs IRR vs CAGR: Quick Reference
| Metric | Best Used When | Limitation |
|---|---|---|
| CAGR | Single lump-sum, fixed holding period | Ignores multiple cash flows and exact dates |
| IRR | Project cash flows at regular intervals | Assumes equal time periods between flows |
| XIRR | SIP, irregular investments, mixed flows | Requires exact dates; can fail if no sign change |
| Absolute Return | Quick total gain check, short time horizons | Ignores time completely — unusable for comparison |
Common Mistakes Pakistani Investors Make with XIRR
1. Forgetting to add today's portfolio value as the last cash flow
XIRR needs an "exit" cash flow to close the calculation. If your investment is still running, enter today's portfolio value as a positive amount on today's date. This represents what you would receive if you redeemed everything now.
2. Comparing their XIRR to the fund's CAGR
The fund's CAGR is the return on a lump sum from a specific past date. Your XIRR reflects your personal investment pattern. If you invested heavily when the market was near a peak, your XIRR will be lower than the fund's CAGR — not because the fund performed badly, but because your timing was different.
3. Using absolute return instead of XIRR to judge a SIP
"I invested Rs 3,60,000 and now have Rs 5,20,000 — that's a 44% return!" True, but meaningless without knowing the time period. The XIRR on that 3-year SIP tells you the annualised rate and lets you compare it against any other investment.
4. Not accounting for loads and taxes
Front-end loads, back-end loads, and capital gains tax all reduce your actual return. For an accurate after-cost XIRR, subtract the exit load from your redemption amount and — if applicable — deduct capital gains tax before entering the final positive cash flow.
XIRR and Pakistan's Mutual Fund Industry
SECP-regulated AMCs (Asset Management Companies) in Pakistan are required to disclose fund returns. However, the disclosed returns are typically CAGR on a NAV basis — not investor-level XIRR. Two investors in the same fund, starting on different dates or with different SIP amounts, will have different XIRR figures.
Some platforms — like Meezan Investments' online portal and JS Investments — now show investor-level XIRR in account statements. If yours doesn't, calculate it yourself using the transaction history you download from your AMC's portal.
What a Good XIRR Looks Like in Pakistan
| Investment Type | Typical XIRR Range (PKR) | Context |
|---|---|---|
| Money market / income fund SIP | 18–24% | Tracks SBP policy rate; fell as rates cut in 2024–25 |
| Pakistan equity mutual fund SIP (5-yr) | 15–22% | Depends heavily on entry timing relative to KSE-100 |
| NSS Bahbood / Regular Income Cert. | 12–17% | Guaranteed but taxable for high-income filers |
| Real estate (rent + appreciation) | 10–18% | Illiquid; highly location-dependent |
| Pakistan CPI Inflation (benchmark) | ~12–15% | Minimum bar — anything below is losing real value |
⚠️ A high XIRR in the past does not guarantee future results. Pakistan's market environment — interest rates, inflation, PKR exchange rate — changes fast. A money market fund XIRR of 22% in 2023 dropped sharply as the SBP cut the policy rate through 2024–2025.
Bottom Line
XIRR is the most accurate measure of personal investment return available to Pakistani investors. Whenever you have more than one cash flow — which describes virtually every SIP, every NSS holder receiving monthly profit, and anyone who has ever topped up or redeemed an investment — XIRR is the right number to use.
Key rules to remember:
- Investments out of your pocket → negative cash flows.
- Redemptions, profit payments, current portfolio value → positive cash flows.
- Always include today's portfolio value as the last row with today's date, even if you haven't redeemed yet.
- Compare your XIRR to inflation (not to the fund's CAGR) to know whether you are building real wealth.
Use our free XIRR Calculator to skip the spreadsheet entirely — paste in your transaction history and get your annualised return instantly.