Here is how to calculate IRR: find the discount rate that makes the net present value (NPV) of all cash flows equal zero. For most cash flow patterns there is no algebraic shortcut, so you either iterate by hand with a calculation table or let Excel’s IRR or XIRR function do the iteration.
This guide shows all three. It starts with the formula, works through a manual calculation with a table, gives the Excel and Google Sheets steps, and then calculates a private fund’s IRR from dated cash flows, where XIRR is the right tool. It ends with the mistakes that produce wrong or impossible results.
Key takeaways
- IRR is the rate at which NPV equals zero. Money paid in is negative, money received is positive.
- By hand, you bracket and interpolate. In our example, NPV is positive at 14% and negative at 15%, and interpolation gives 14.90% against an exact 14.90%.
- In Excel, use IRR for equal periods and XIRR for real dates. MIRR lets you set the reinvestment rate yourself.
- For a fund, add NAV as the last cash flow. Our sample LP’s net IRR since inception is 10.76%, with a TVPI of 1.53x.
- A quick check: one investment and one exit gives IRR = MOIC1 ÷ years − 1, so 2.0x over five years is 14.87%.
- IRR and NPV can disagree. To choose between projects, rank by NPV at your cost of capital; use IRR to compare against a hurdle.
- Most errors come from signs, dates, and periods, and from cash flows that change sign more than once.
The IRR formula
0 = CF0 + CF1 ÷ (1 + IRR)1 + CF2 ÷ (1 + IRR)2 + … + CFn ÷ (1 + IRR)n
CFt is the net cash flow in period t, negative for money paid in and positive for money received; n is the number of periods. IRR is the rate that makes the sum zero.
The right-hand side is the NPV of the cash flows at a discount rate equal to the IRR. Because the rate sits inside a power for every period, the equation cannot be solved directly once there are more than two cash flows; it has to be solved by trial and error, which is all a spreadsheet does.
The shortcut for one cash flow in and one out
IRR = (Ending value ÷ Starting value)1 ÷ years − 1
$100 that grows to $200 in five years: 20.2 − 1 = 14.87%.
This is the same as a compound annual growth rate. It only works when there is a single payment at the start and a single receipt at the end; anything in between needs the full formula.
How to calculate IRR by hand: the calculation table
Take an investment of $1,000 that returns $300, $400, $400, and $300 over the next four years. The calculation follows five steps.
-
Lay out the cash flows with signs
Year 0 is the outflow of $1,000, written as a negative number.
-
Pick a trial rate and compute NPV
Divide each cash flow by (1 + rate) raised to its year, and add them up.
-
Adjust the rate
If NPV is positive, the IRR is higher than your trial rate; if negative, it is lower.
-
Bracket the zero
Find two rates, one with a positive NPV and one with a negative NPV.
-
Interpolate between them
Use the formula below to estimate where NPV crosses zero.
NPV at a 14% trial rate
| Year | Cash flow ($) | Discount factor at 14% | Present value ($) |
|---|---|---|---|
| 0 | (1,000) | 1.0000 | (1,000.00) |
| 1 | 300 | 0.8772 | 263.16 |
| 2 | 400 | 0.7695 | 307.79 |
| 3 | 400 | 0.6750 | 269.99 |
| 4 | 300 | 0.5921 | 177.62 |
| NPV at 14% | 18.56 |
NPV at each trial rate
| Trial rate | NPV ($) | What it tells you |
|---|---|---|
| 10% | 108.74 | Positive: the IRR is higher |
| 12% | 62.10 | Positive: the IRR is higher |
| 14% | 18.56 | Positive: the IRR is higher |
| 15% | (2.14) | Negative: the IRR is lower |
| 16% | (22.16) | Negative: the IRR is lower |
IRR ≈ rlow + NPVlow ÷ (NPVlow − NPVhigh) × (rhigh − rlow)
With NPV of $18.56 at 14% and minus $2.14 at 15%: 14% + 18.56 ÷ (18.56 + 2.14) × 1% = 14.90%.
The exact IRR, found by iteration, is 14.90%. Interpolation is close because the bracket is only one percentage point wide; a wider bracket gives a rougher estimate, because NPV is curved, not straight. The chart shows that curve, the NPV profile, crossing zero at the IRR.
NPV profile of the example: NPV falls as the discount rate rises and equals zero at the IRR.
How to calculate IRR in Excel and Google Sheets
Excel and Google Sheets have the same three functions, with the same syntax.
| Function | Use it when | Syntax | Watch for |
|---|---|---|---|
| IRR | Cash flows are evenly spaced: years, quarters, or months | =IRR(values, [guess]) | The result is per period; annualize quarterly results with (1 + IRR)4 − 1 |
| XIRR | Cash flows fall on actual dates | =XIRR(values, dates, [guess]) | Already annualized, on a 365-day year; dates must be real dates, not text |
| MIRR | You want to set the borrowing and reinvestment rates yourself | =MIRR(values, finance_rate, reinvest_rate) | Needs evenly spaced cash flows, like IRR |
For the example above, enter -1000, 300, 400, 400, and 300 in cells B2 to B6 and type =IRR(B2:B6). Excel returns 14.90%. Both IRR and XIRR need at least one negative and one positive value, and both work by iteration: if Excel cannot find a result, it returns the #NUM! error, and a guess close to the answer often fixes it.
Get IRR from the books, not from a spreadsheet
FundCount calculates IRR, TVPI, DPI, and RVPI per fund and per investor from the same cash flows that post to the general ledger.
How to calculate IRR on a financial calculator
Exam candidates and many finance teams still use financial calculators. Both common models solve the example above in a few keystrokes.
- TI BA II Plus: press [CF], then [2nd] [CLR WORK]. Enter 1000 [+/-] [ENTER] for CF0. Press the down arrow, enter 300 [ENTER] for C01, and leave F01 at 1. Enter 400 [ENTER] for C02 and set F02 to 2, because 400 repeats. Enter 300 [ENTER] for C03. Press [IRR] [CPT]: the answer is 14.90.
- HP 12C: press [f] [CLEAR REG]. Enter 1000 [CHS] [g] [CF0], 300 [g] [CFj], 400 [g] [CFj], 2 [g] [Nj], 300 [g] [CFj], then [f] [IRR]: the answer is 14.90.
Both calculators assume evenly spaced periods, like Excel’s IRR function. For cash flows on irregular dates, use XIRR in a spreadsheet.
How to calculate IRR in a fund accounting system
In a fund accounting system, IRR comes from the same records as the books, so there is nothing to copy into a spreadsheet:
-
Record the cash flows as transactions
Capital calls and distributions are posted to the general ledger on their cash dates, by fund and by investor.
-
Value the portfolio
Investments are marked at the valuation date, which sets the NAV used as the final cash flow.
-
Choose the scope
A fund, a single investor, or a group of funds, gross or net of fees and carried interest.
-
Run the performance report
The system solves for IRR on the dated cash flows and reports it with TVPI, DPI, and RVPI.
Why the system’s IRR can differ from your spreadsheet
- Dates: the system uses cash dates; a spreadsheet often uses notice or due dates.
- NAV date: a stale NAV in the spreadsheet against the system’s latest valuation.
- Scope: including or excluding the GP’s commitment, fees, or recallable distributions.
- Grouping: monthly or quarterly buckets instead of daily cash flows.
Private fund example: calculating IRR with XIRR
A limited partner (LP) committed $10 million to a fund. It has paid in $7.5 million over three capital calls and received $5.5 million in two distributions, and its remaining interest is valued at $6.0 million at September 30, 2026. Funds call and distribute on irregular dates, so this is a job for XIRR, with the NAV entered as a final positive cash flow, as if the interest were sold at that value.
The XIRR set-up
| Column A: date | Column B: cash flow ($) | What it is |
|---|---|---|
| Mar 15, 2021 | (2,000,000) | First capital call |
| Nov 1, 2021 | (3,000,000) | Second capital call |
| Jun 30, 2022 | (2,500,000) | Third capital call |
| May 15, 2024 | 1,500,000 | First distribution |
| Sep 30, 2025 | 4,000,000 | Second distribution |
| Sep 30, 2026 | 6,000,000 | NAV at the valuation date |
| =XIRR(B2:B7, A2:A7) | 10.76% | Net IRR since inception |
The LP’s net IRR since inception is 10.76%. Over the same period its TVPI is 1.53x and its DPI is 0.73x: the multiples say how much value the fund has created per dollar paid in, the IRR says how fast. Our guides to TVPI, DPI, and MOIC explain the multiples.
Why the dates matter
Put the same cash flows into yearly buckets and run IRR instead, and the answer is 11.15%, not 10.76%. The yearly version treats every flow as if it happened on the same day of the year, which moves money by up to eleven months. For private funds, where a few months can move IRR noticeably, XIRR with actual dates is the standard.
What changes for gross and net IRR
This is a net IRR, because it uses the LP’s own cash flows after fees, expenses, and carried interest. A gross IRR uses the fund’s investment-level cash flows before them; our guide to gross vs net IRR explains the gap, including how subscription lines that delay capital calls can raise IRR without changing the multiple; our guide to fund finance explains how those lines work. Because NAV is part of the calculation, an interim IRR moves every time the portfolio is revalued.
Illustrative example; amounts in dollars.
IRR shortcuts and sanity checks
For a single investment and a single exit, the multiple and the holding period pin down the IRR. Use this table to check whether a reported IRR is plausible:
| MOIC | 3 years | 5 years | 7 years |
|---|---|---|---|
| 1.5x | 14.5% | 8.4% | 6.0% |
| 2.0x | 26.0% | 14.9% | 10.4% |
| 3.0x | 44.2% | 24.6% | 17.0% |
- Rule of 72: money doubles in roughly 72 ÷ IRR years, so a 2.0x over five years implies an IRR near 14% to 15%.
- High IRR, low multiple: a 40% IRR on a deal held for six months may be only a 1.2x; check both before drawing conclusions.
- Interim IRRs drift: early in a fund’s life, fees and a small NAV hold IRR down, the J-curve; it says little until the fund matures.
IRR vs NPV: the decision rule
In capital budgeting, a project is acceptable when its IRR exceeds the cost of capital or hurdle rate, which is the same as saying its NPV at that rate is positive. The two measures can still rank projects differently. Compare the example above, Project A, with Project B, which pays nothing until a single $1,600 in year 4:
| Measure | Project A | Project B |
|---|---|---|
| Cash flows (years 0 to 4) | -1,000; 300; 400; 400; 300 | -1,000; 0; 0; 0; 1,600 |
| IRR | 14.90% | 12.47% |
| NPV at 8% | 158.76 | 176.05 |
A has the higher IRR, but at an 8% cost of capital B adds more value. The two NPV profiles cross at 9.01%: below that rate B wins on NPV, above it A does. Because NPV measures value in dollars at your own cost of capital, it is the better guide when projects compete for the same money.
Levered and unlevered IRR in real estate
Real estate investors quote two IRRs. Unlevered IRR uses the property’s own cash flows, as if bought for cash; levered IRR uses the equity’s cash flows after debt. Take a $10 million property with $600,000 of annual net operating income, sold after five years for $11 million, bought either for cash or with a $6 million interest-only loan at 6%:
| Cash flow ($) | Unlevered | Levered |
|---|---|---|
| Year 0 | (10,000,000) | (4,000,000) |
| Years 1 to 4, each | 600,000 | 240,000 |
| Year 5, including the sale | 11,600,000 | 5,240,000 |
| IRR | 7.71% | 10.09% |
Borrowing at 6% against a property yielding more lifts the equity’s IRR from 7.71% to 10.09%. It also raises the risk: if the property falls in value, the loss on the equity is magnified in the same way.
Illustrative example, ignoring transaction costs and taxes.
Common IRR mistakes and how to fix them
- Wrong signs. Money paid in must be negative and money received positive, or Excel returns #NUM!.
- Periods mixed up. Monthly cash flows in IRR give a monthly rate; annualize it with (1 + IRR)12 − 1.
- Missing terminal value. For an unrealized fund, leave out NAV and the IRR looks like a loss.
- Dates stored as text. XIRR cannot read them; convert the column to dates first.
- More than one sign change. Cash flows that switch between negative and positive more than once can have two IRRs or none; check the NPV profile or use MIRR.
- Comparing IRRs alone. A higher IRR on a smaller, shorter investment can create less wealth; read it with the multiple.
What IRR does not tell you
- It assumes reinvestment at the IRR. Interim cash flows are implicitly reinvested at the same rate; MIRR replaces that with a rate you choose.
- It ignores size. A 30% IRR on $1 million and a 15% IRR on $100 million are not comparable outcomes.
- It can be managed. Delaying capital calls or returning capital early raises IRR without adding value.
- It is not a risk measure. It says nothing about volatility or the chance of loss.
Investors who need to compare managers rather than measure their own cash flows use time-weighted returns; our guide to TWR vs IRR explains when each fits, and our guide to the public market equivalent shows how to benchmark a fund’s IRR against an index.
How FundCount calculates IRR
- Portfolio accounting reports IRR, TVPI, DPI, and RVPI per fund and per investor, and time-weighted and money-weighted returns with GIPS-compliant calculation, from the same positions and cash flows that post to the general ledger.
- For LPs, AI Document Intelligence turns capital call and distribution notices into dated cash flows, so the IRR uses the real dates.
- Reporting delivers the results to investors and to BI tools through native Power BI and data warehouse connectors.
For a wider view of the tools, see our guide to investment performance reporting software.
Watch-outs
- IRR is only as good as the cash flow history; load every call and distribution since inception.
- Agree which NAV date and which cash flows count for gross and net IRR before reporting them.
See IRR reporting in FundCount
In a guided walkthrough, we load a fund’s capital calls, distributions, and NAV and show IRR and multiples by fund and by investor.
FAQs
What is the formula for IRR?
IRR is the rate that makes the NPV of all cash flows zero: 0 = the sum of each cash flow divided by (1 + IRR) raised to its period.
How do you calculate IRR by hand?
Compute NPV at a trial rate, raise the rate if NPV is positive and lower it if negative, find two rates that bracket zero, and interpolate between them.
How do you calculate IRR in Excel?
Enter the cash flows in a column, outflows negative, and use =IRR(range) for evenly spaced flows or =XIRR(values, dates) for flows on actual dates.
What is the difference between IRR and XIRR?
IRR assumes equal periods between cash flows; XIRR uses the actual dates and returns an annual rate. For fund cash flows, XIRR is the better fit.
What is a good IRR?
It depends on risk and on what you compare it with: the cost of capital, the fund’s hurdle, often 8%, and funds of the same strategy and vintage year.
Can IRR be negative?
Yes. If you get back less than you put in, the IRR is negative.
Why does Excel return #NUM! for IRR?
Usually because the values do not include both a negative and a positive number, or because the iteration did not converge; check the signs and add a guess.
How do you calculate IRR on a financial calculator?
Enter the cash flows in the calculator’s cash flow register, with the initial investment negative, then compute IRR: [IRR] [CPT] on a BA II Plus, or [f] [IRR] on an HP 12C.
What is the difference between IRR and NPV?
NPV is a dollar value at a chosen discount rate; IRR is the discount rate at which NPV is zero. When projects compete, rank them by NPV.
What is the difference between levered and unlevered IRR?
Unlevered IRR uses an asset’s cash flows as if bought for cash; levered IRR uses the equity’s cash flows after borrowing, which raises both the expected return and the risk.
How is IRR calculated in private equity?
From dated capital calls and distributions, with the current NAV as a final cash flow, using XIRR. Net IRR uses the LP’s cash flows; gross IRR uses the fund’s investment-level cash flows.
Sources and methodology
Sources: Microsoft Support documentation for the IRR, XIRR, and MIRR functions; CFA Institute, Global Investment Performance Standards (GIPS) 2020, on money-weighted returns. FundCount capabilities reviewed in November 2026.
Examples: fictional; every figure is calculated by script and checked.
Scope: general information, not investment advice.
Published: November 2026.