If a colleague drops a list of cash flows on your desk and asks whether the project earns enough to pursue, you are looking at an internal rate of return problem. The IRR formula in Excel turns that messy column of numbers into a single percentage you can compare against your hurdle rate, and the underlying math (which used to require financial calculators or guess-and-check) collapses to one short function call.
The catch is that Excel ships with three closely related functions: IRR, XIRR, and MIRR. They look similar from the outside but they treat timing and reinvestment differently, and using the wrong one quietly turns a winner into a loser. Pick the right tool, lay out the cash flows in the right order, and IRR becomes a thirty-second answer instead of an afternoon spreadsheet hunt.
This guide walks through everything: the basic IRR syntax, when to switch to XIRR for irregular dates, why MIRR exists, the most common errors and how to fix them, and a worked example you can drop into a blank sheet and adapt to your own deals. By the end you will know which function to reach for, what to type, and how to sanity-check the result so finance folks trust the number.
Internal rate of return is the discount rate at which the net present value of a series of cash flows equals zero. In plain English: it is the annualized percentage return a project earns if you assume every dollar coming out gets reinvested at that same rate. If your IRR is 18 percent and your cost of capital is 10 percent, the deal creates value. If IRR is 6 percent and capital costs 10, you destroy value by accepting it.
The function does not need you to guess a rate or build a discount table. It iterates internally until NPV crosses zero. That is also why you sometimes see a #NUM! error. If the cash flows do not produce a sign change (at least one negative and one positive value), there is no rate that solves the equation, and Excel gives up rather than return nonsense.
For a column of yearly cash flows in B2 to B7 starting with a negative initial investment, type =IRR(B2:B7). Format the result cell as Percentage. If your cash flows hit on specific calendar dates, switch to =XIRR(B2:B7, A2:A7) with the dates in column A. Both functions return an annualized rate.
The standard IRR function takes two arguments: a range of values and an optional guess. The values must include at least one negative (typically the initial investment) and at least one positive (the future cash returns). They have to be in chronological order because Excel assumes each value sits exactly one period apart.
=IRR(values, [guess])
The guess argument defaults to 10 percent and rarely matters. It only tells Excel where to start its iteration. If you have wildly volatile cash flows, you can supply a closer starting point and convergence speeds up, but for almost every business case you can omit it entirely.
Lay out your data in a single column. Put the initial investment in the first cell as a negative number, then list each period's net cash flow below it. In one period equals one row format, the formula becomes =IRR(B2:B7) for a six-period project starting in B2.
Open a blank workbook. In cell A1 type Year. In B1 type Cash Flow. Fill A2 through A7 with 0, 1, 2, 3, 4, 5. In B2 enter -50000 (the initial outlay). In B3 through B7 enter 12000, 14000, 16000, 18000, 20000. These represent five years of growing returns on a $50,000 investment.
In cell D2 type =IRR(B2:B7) and press Enter. Excel returns approximately 16.5 percent. That is the IRR. Compare it to your cost of capital. If you finance projects at 10 percent, this one beats the hurdle by 6.5 percentage points, so the math says proceed.
If you want a sanity check, compute the NPV formula in Excel at the same rate. The NPV at the IRR should be (essentially) zero. That is the definition of IRR after all, and it makes a useful audit trail when you present numbers to managers who want proof the formula did what it was supposed to.
Use plain IRR. Lay out cash flows in a single column with the initial outlay as the first negative value. Excel assumes each row is exactly one period apart. This is the textbook case and works for most capital budgeting decisions where flows are estimated yearly.
Use XIRR with a dates column. Private equity, real estate, venture capital, and any deal where money moves on specific days rather than uniform intervals. XIRR annualizes the return using actual day counts so the math reflects the true timing.
Use MIRR with explicit finance and reinvest rates. Honest about the fact that you cannot reinvest interim distributions at the same rate as the project itself. Useful for skeptical CFOs and audit committees.
Use IRR with loan principal as the positive first cash flow and payments as negatives. The result equals the effective annual interest rate. More accurate than comparing stated APRs across offers.
Combine IRR with a one-variable data table. Vary the key assumption (sale price, exit multiple, growth rate) and watch IRR shift. Produces a defensible range rather than a single point estimate.
Plain IRR assumes every cash flow happens exactly one period apart. That is fine for textbook examples but rare in real life. Real cash flows hit on irregular dates: a deposit in March, a withdrawal in November, a distribution in February of the following year. XIRR handles those dates explicitly.
The XIRR syntax adds a dates range as the second argument:
=XIRR(values, dates, [guess])
The values and dates must be the same length, and each date must line up with its cash flow. The first date is the reference point (it does not have to be January 1). XIRR returns an annualized rate, just like IRR, but it accounts for the exact number of days between flows. For private equity, real estate, or any irregular cash schedule, XIRR is almost always the right call.
Watch the date format. Excel needs proper date values, not text that looks like dates. If you typed dates as strings, XIRR returns a #VALUE! error. Highlight the date column, press Ctrl+1, and confirm the category is Date before you build the formula.
The modified internal rate of return addresses a hidden assumption in IRR: that interim cash flows get reinvested at the IRR itself. That is often unrealistic. A project earning 25 percent IRR rarely lets you reinvest the proceeds at 25 percent again, because such opportunities are scarce. MIRR lets you specify a more realistic reinvestment rate.
The MIRR syntax takes three arguments:
=MIRR(values, finance_rate, reinvest_rate)
Finance rate is what you pay to borrow money for the negative cash flows. Reinvest rate is what you can earn on positive cash flows between now and the end of the project. If you finance at 8 percent and reinvest at 6 percent, the formula becomes =MIRR(B2:B7, 0.08, 0.06). The result will almost always be lower than the plain IRR, and it gives a more honest read for projects with long horizons or large interim distributions.
The single most common error is #NUM!. It means Excel could not find a rate that makes NPV zero, usually because the cash flows do not change sign. Check that your column has at least one negative value and at least one positive value. If all the numbers are positive (which sometimes happens when people accidentally enter the initial investment as a positive), there is no IRR to find.
The second most common error is #VALUE!, which means one of the cells in the range contains text or an error. Use Goal Seek in Excel or a simple COUNT formula to verify every entry is numeric. Blank cells inside the range are skipped, but cells that look numeric and are actually text strings will break the calculation.
A third subtle problem: multiple IRRs. If the cash flow stream changes sign more than once (negative, then positive, then negative again), the equation may have several mathematical solutions. Excel returns whichever one its iteration lands on, which may not be the meaningful one. This is one of the reasons MIRR exists. It always returns a single rate even when IRR is ambiguous.
IRR returns a decimal number. If the result is 0.165, Excel might display it as 0.17, 16.5%, or 16% depending on the cell formatting. To always show percent with two decimals, select the result cell, press Ctrl+1, choose Percentage, and set decimals to 2. Save your template that way to avoid confusion in shared workbooks.
When comparing IRR across projects, line up the formatting first. Mixing a 0.16 with a 12.5% in the same comparison table creates obvious headaches in meetings. Use a single consistent format and label the column clearly: IRR (annual, %).
List five to ten years of projected net cash flows in one column. Initial investment is the first negative value, subsequent years are positive. Use =IRR(B2:B7) and compare against your company hurdle rate. Beats hurdle: accept. Below hurdle: reject. Pair with NPV at the hurdle for a dollar amount as backup.
Cash flows include purchase, monthly carry costs, rent collected, and sale proceeds. Dates are rarely uniform. Use =XIRR(values, dates) with the actual closing and distribution dates. Annual IRR comes out cleanly even with irregular timing. Common targets: 12 to 18 percent for value-add deals.
LP capital calls (negatives) and distributions (positives) hit on specific dates over a ten-year fund life. XIRR is the industry standard. Most fund reports show net IRR (after fees) and gross IRR (before). Run XIRR on the net cash flows you actually received to verify the fund manager number.
Real estate is one of the most common places IRR shows up. You make a large initial purchase, collect rental income for years, then sell. The cash flows are: a big negative on day one, smaller positives during the hold period, and a large positive at sale. Lay them out chronologically, run XIRR if dates vary, and the rate tells you whether the deal beats alternative investments.
For a flip: -250000 at purchase, then -15000 for renovation a month later, then -2000 monthly for carrying costs, then +320000 at sale eight months in. That is the classic XIRR case. Type the dates in column A, the cash flows in column B, then =XIRR(B2:B12, A2:A12) in any empty cell. The result is the annualized return after subtracting your costs.
Rental property is similar but longer. Initial purchase plus closing costs is negative. Each year's net operating income (rent minus expenses) is positive. The sale price at exit is a large positive lump in the final year. If you refinance midway and pull cash out, that goes in too as a positive on the date of the refinance.
IRR also works in reverse. If you are evaluating a loan, the inflow is the loan principal you receive at signing (positive), and the outflows are the monthly payments (negative). The IRR equals your effective interest rate, and you can compare offers by computing IRR for each loan and picking the lowest one. This is more reliable than comparing APRs, because IRR uses every actual cash flow in your specific scenario.
For leases, the cash flows look similar to a loan but the signs depend on which side of the deal you are on. Lessees: monthly payments out, residual value implicit. Lessors: lease income in, asset purchase out. Either way, IRR gives you a single percentage that summarizes the entire deal in a number you can compare across alternatives.
One number is rarely enough for a real decision. Combine IRR with Excel data tables (Data > What-If Analysis > Data Table) to see how sensitive the rate is to changes in key inputs. Set up your cash flows so that one assumption (say, the sale price in year five) is a single cell, then build a one-variable data table varying that cell across a range. Each row of the table recalculates IRR for that scenario.
This turns a single rate into a story: at $300,000 sale price the IRR is 9 percent, at $350,000 it climbs to 14 percent, at $400,000 it hits 18 percent. Now you have a defensible analysis rather than a point estimate. Pair it with a tornado chart and the executive committee sees which assumption drives the result. Decisions get made faster when uncertainty is visible.
NPV gives a dollar amount. IRR gives a percentage. Both answer the same question (is this project worthwhile) but from different angles. The strict academic recommendation is NPV, because it adds up cleanly across multiple projects and accounts for project size. But IRR is what investors and managers ask for, because percentages are intuitive and let you compare projects of vastly different sizes on a level playing field.
In practice, report both. Show the NPV at the company's hurdle rate to confirm the project creates value, then show the IRR so stakeholders see how comfortable the margin is. A project with NPV of $100k and IRR of 11 percent (just above a 10 percent hurdle) is much riskier than one with NPV of $100k and IRR of 25 percent. The two metrics together give a fuller picture than either alone.
If your cash flows happen at perfectly regular intervals (every quarter, every year) and you accept the textbook assumption that interim cash flows reinvest at the IRR itself, use plain IRR. It is the simplest and fastest.
If your cash flows hit on specific calendar dates that are not evenly spaced (the typical private equity, real estate, or venture investment case), use XIRR. It is more accurate because it discounts cash flows by exact day count rather than assuming uniform periods.
If your project has very long horizons or large interim distributions, and you want a more conservative number that accounts for realistic reinvestment rates, use MIRR. It almost always returns a lower rate than IRR, which is more defensible to skeptical CFOs who push back on the IRR reinvestment assumption.
One final reminder: Excel cares about cell formatting more than you might expect. If your cash flows include cents, the column type should be Number with two decimals. If your dates are text, XIRR fails. If the values cell contains a tiny rounding error you cannot see, the IRR can shift by a fraction of a percent. Audit the inputs first; trust the function second.
The fastest way to internalize IRR is to type a few examples by hand. Build a five-year project, run IRR. Build the same project with irregular dates, run XIRR. Apply different reinvestment rates with MIRR and watch the result shift. After three or four examples, the syntax becomes muscle memory and you stop hesitating.
For broader practice on the financial side of Excel, work through the PMT function next. PMT and IRR are the two formulas that show up most often in finance interviews and real-world loan analysis, and they complement each other neatly. If you can build a loan amortization with PMT and verify the rate with IRR, you have covered the basics of time-value-of-money calculations in Excel.
One last note for analysts: when you publish an IRR in a report, always label whether it is plain IRR, XIRR, or MIRR. The same dataset can produce three different numbers depending on which function you used, and an audit trail that names the function and any assumed reinvestment rate prevents weeks of awkward emails when the auditor asks why the headline percentage does not match what they get from rerunning the calc.
=IRR(values, [guess]). The values argument is a column of cash flows in chronological order, with at least one negative (the initial investment) and at least one positive (a future cash return). The optional guess defaults to 10 percent and rarely needs to be changed. The function returns the annualized internal rate of return as a decimal, which you can format as a percentage in the result cell. Available in every version of Excel since 2007.=XIRR(values, dates, [guess]). The values and dates ranges must be the same length, and each date should line up with its corresponding cash flow. The first date is the reference point. XIRR returns an annualized rate based on actual day counts between cash flows, which makes it the right function for private equity, real estate, and any deal where money moves on specific calendar dates rather than uniform intervals.=MIRR(values, finance_rate, reinvest_rate). Finance rate is your cost of borrowing for negative cash flows. Reinvest rate is what you can realistically earn on positive cash flows between now and project end. MIRR is especially useful for long-horizon projects with large interim distributions, where the assumption that proceeds reinvest at the IRR itself is hardest to defend.