How to Calculate IRR (Excel & Google Sheets): Step-by-Step

Quick Answer

To calculate IRR in a spreadsheet: (1) list cash flows in order (money you invest is negative; money you receive is positive), (2) use IRR(values) if cash flows happen at regular intervals, or XIRR(values, dates) if cash flows happen on irregular dates (common in real estate), and (3) sanity-check results using NPV or equity multiple. If IRR returns an error, the usual fixes are correcting signs, ensuring you have at least one negative and one positive cash flow, switching to XIRR, or providing a guess.

Before You Calculate: The Cash Flow Rules (This Is Where Most People Mess Up)

IRR is not “magic.” It’s just a function that tries to solve for a rate that fits your cash flow stream. If you feed it the wrong cash flows, you get a wrong answer—or an error.

Rule 1: you must have at least one negative and one positive cash flow

If you only have negative numbers (all money out) or only positives (all money in), IRR can’t be computed. A real investment always starts with money out (negative) and later returns money in (positive).

Rule 2: signs matter more than labels

You can call it “investment,” “deposit,” “capex,” “distribution”—IRR doesn’t care. It only cares whether cash is leaving your pocket (negative) or entering your pocket (positive).

Rule 3: order matters

Cash flows must be in chronological order. With XIRR you’ll also provide dates, but even then you should list them in time order to avoid confusion and errors.

Rule 4: include all friction and fees if you want a real IRR

In real estate and private investing, leaving out costs is the #1 way people inflate IRR: acquisition fees, closing costs, renovation costs, refinance fees, selling commissions, taxes (if you model taxes). If it’s a real cash flow to you, it belongs in your IRR list.

IRR vs XIRR: Choose the Right Tool

Use IRR when cash flows are periodic

IRR assumes equal spacing between cash flows: monthly, quarterly, yearly—whatever you chose, but it must be evenly spaced. If your cash flows are exactly monthly (one per month), IRR can work.

Use XIRR when cash flows are irregular

XIRR allows irregular dates: you provide a cash flow list and a matching date list. This is the common real-world case: purchase on one date, renovation over multiple dates, rent starting later, sale on another date.

Why using IRR instead of XIRR can materially change the answer

If you treat irregular cash flows as “equal spacing,” you’re distorting time. And time distortion changes IRR—sometimes a lot. That’s why real estate analysts default to XIRR unless they’re modeling perfectly periodic cash flows.

Excel IRR: Step-by-Step (Periodic Cash Flows)

If your cash flows occur at regular intervals (say, one per year or one per month), use IRR in Excel.

Step 1: Create a cash flow column

Put cash flows in a single column. Example (yearly):

• Row 1: -100000 (initial investment)
• Row 2: 8000 (year 1 distribution)
• Row 3: 8000 (year 2 distribution)
• Row 4: 8000 (year 3 distribution)
• Row 5: 8000 (year 4 distribution)
• Row 6: 120000 (final distribution / sale)

Step 2: Use IRR()

In Excel:
=IRR(A1:A6)

Step 3: Format as percent

Excel returns a decimal (e.g., 0.12). Format as percent (12%).

Step 4: sanity-check

If IRR says something extreme, check: cash flow signs, missing costs, and whether cash flows are truly periodic.

Google Sheets IRR/XIRR: Step-by-Step

IRR in Sheets (periodic)

Same idea:
=IRR(A1:A6)

XIRR in Sheets (irregular dates)

Put cash flows in one column and dates in another column. Example:

• Column A: cash flows
• Column B: dates

Then:
=XIRR(A1:A12, B1:B12)

Date formatting matters

If dates are stored as text, XIRR can return errors. Make sure Sheets recognizes them as dates (not strings).

Real Estate IRR: What Cash Flows to Include (Full List)

Real estate IRR is usually calculated on equity cash flows (cash that you, the investor, actually put in or receive). Here’s the complete list most investors forget pieces of:

At purchase (usually negative)

• Down payment (negative)
• Closing costs (negative)
• Upfront repairs/renovations (negative)
• Any initial reserves you fund (negative, if it’s your cash and not returned)

During ownership (positive or negative)

• Net operating cash flow (rent minus all expenses, including vacancy and repairs)
• Capital expenses (roof, HVAC, major remodels)
• Leasing costs or turnover costs
• Large property tax/insurance changes if modeled as cash outflows

Financing events (often forgotten)

• Refinance closing costs (negative)
• Refinance cash-out proceeds (positive)
• Mortgage paydown is not a “cash flow” unless you model it explicitly as part of cash flow to equity (usually it’s embedded in debt service)

At sale (final positive cash flow net of everything)

• Sale price (positive)
• Minus selling costs (agent commissions, transfer taxes, closing fees) (negative)
• Minus remaining loan payoff (negative)
• Minus taxes (if you include them) (negative)
• Equals net sale proceeds (positive cash flow)

Worked Examples (So You Can Copy the Pattern)

Example 1: Simple periodic IRR (annual)

You invest $100,000 today, receive $10,000 per year for 5 years, then receive $110,000 in year 5. Put cash flows in A1:A6:

• A1: -100000
• A2: 10000
• A3: 10000
• A4: 10000
• A5: 10000
• A6: 120000

Formula:
=IRR(A1:A6)

Example 2: Real estate XIRR (irregular dates)

You buy on Jan 15, spend renovations on Feb 1 and Mar 1, start receiving rent later, then sell on a specific date. Put values and dates:

• Cash flow -80000 on 2026-01-15 (down payment + closing)
• -10000 on 2026-02-01 (renovation)
• -5000 on 2026-03-01 (renovation)
• +600 monthly cash flow for many months (each with its date)
• +95000 net sale proceeds on 2028-06-20

Formula:
=XIRR(A1:A30, B1:B30)

Why the “net sale proceeds” line is everything

Many real estate IRR spreadsheets “forget” to subtract selling costs or loan payoff. That can add several percentage points to the IRR and produce misleading comparisons.

Troubleshooting: Why IRR Returns #NUM!, #VALUE!, or Weird Results

Error: #NUM!

This usually means the function couldn’t find a rate that solves the equation. Common causes:

• No sign change (all cash flows same sign)
• Cash flows create multiple IRRs (more than one sign change)
• Dates/cash flows aren’t aligned (XIRR)
• The algorithm needs a different starting “guess”

Error: #VALUE!

Often caused by:

• Dates stored as text instead of date values (XIRR)
• Non-numeric cash flow cells (blank strings, commas as text)
• Range mismatch (values range and dates range different sizes)

Weird results (like 200% or negative IRR)

Weird doesn’t always mean wrong. A short deal with a big early return can produce a high IRR. But it can also mean:

• You accidentally flipped signs (initial investment positive)
• You double-counted sale proceeds
• You forgot the loan payoff or selling costs
• You assumed rent starts earlier than it can

What Is the “Guess” Argument (and When to Use It)?

IRR and XIRR functions often allow a “guess” input: it’s just a starting point for the iterative calculation. If the function struggles to converge, giving a reasonable guess can help.

Example

=IRR(A1:A20, 0.1) starts the search near 10%. If you expect a negative IRR, you can use a negative guess.

When guesses are useful

• Very high or very low returns
• Cash flow patterns with multiple sign changes
• When IRR returns #NUM! but you’re sure the setup is correct

MIRR: Modified IRR (When IRR Is Misleading)

IRR has a built-in assumption that interim cash flows are reinvested at the IRR itself. For very high IRRs, that’s often unrealistic. MIRR (Modified IRR) fixes this by letting you specify:

• A finance rate for money you put in
• A reinvestment rate for money you receive before the end

When MIRR is most helpful

• Cash flows have multiple sign changes (multiple IRRs)
• IRR is extremely high due to early distributions
• You want a more conservative, realistic rate assumption

MIRR is more “decision-friendly” in some contexts, but it’s less commonly quoted than IRR. Many analysts will compute both: IRR for the standard metric and MIRR for realism.

Sanity Checks: How to Know Your IRR Is Actually Correct

Check 1: Does the cash flow pattern make economic sense?

• Large negative upfront
• Small positives over time
• Final positive at exit

If your list is mostly positives early with no initial negative, something is wrong.

Check 2: Does the IRR match rough intuition?

If you doubled your money in one year, IRR should be near 100%. If you made 20% total over 10 years, IRR should be low. Use intuition as a “smell test.”

Check 3: Compare with NPV at a hurdle rate

If you have a required return (say 10%), compute NPV. If IRR is above the hurdle rate, NPV should usually be positive.

Check 4: Compare with equity multiple

Equity multiple is total cash returned ÷ total cash invested. It ignores time, but it’s a reality check for “how much money did I get back?”

Quick Checklist (Copy/Paste)

• ✅ At least one negative and one positive cash flow
• ✅ Correct signs: money out = negative, money in = positive
• ✅ Use XIRR when dates are irregular (common in real estate)
• ✅ Dates are true date values (not text), ranges match in size
• ✅ Include all costs: closing, fees, CapEx, selling costs, loan payoff at sale
• ✅ Run a conservative scenario and compare to base
• ✅ Pair IRR with NPV or equity multiple
• ✅ If errors: try a guess, check sign changes, consider MIRR

Related links

Frequently Asked Questions

Bottom Line

Calculating IRR is straightforward once your cash flows are correct and your timing method matches reality. Use IRR for evenly spaced flows and XIRR for irregular dates. In real estate, XIRR is usually the right default. Most mistakes come from wrong signs, missing costs, and “gross sale” proceeds that ignore selling costs and loan payoff. Once you compute IRR, validate it with sanity checks and pair it with NPV or equity multiple so you don’t get misled by a single headline percentage.

Methodology and assumptions

Educational only. IRR depends entirely on your cash flow assumptions and timing. For irregular cash flows, XIRR is typically more appropriate than IRR. Always include real-world fees and friction for a realistic result.