How to Calculate IRR in Excel for a Real Estate Deal

The internal rate of return is the number investors argue about most and calculate wrong most often. It compresses an entire schedule of cash flows, money out to buy the deal, money in from operations, and a lump sum at sale, into a single annualized percentage you can compare against a hurdle. Excel will hand it to you in one function. The catch is that the function only means what you think it means when the cash flows are laid out correctly, and the cash flows usually start life trapped inside a PDF.

This walks through calculating IRR in Excel with both the IRR and XIRR functions, when to reach for each, the errors that send people to a search box, and the reconciliation that keeps the answer defensible. It is written for real estate and small business deals, but the mechanics are the same whether you are underwriting an apartment building or a piece of equipment.

Last updated July 2026.

What is the internal rate of return?

The internal rate of return is the annual discount rate at which a series of cash flows has a net present value of exactly zero. Put plainly, it is the rate the deal earns on the capital while that capital is actually invested. An IRR of 15% means every dollar left in the deal is compounding at 15% a year until it comes back to you. It is the return figure most equity investors screen on, because it accounts for both how much you make and how long your money is tied up.

Two deals can return the same total dollars and have very different IRRs. The one that returns capital sooner wins, because money back early can be redeployed. That time sensitivity is the whole point of IRR, and it is also why a deal that looks great on a simple profit multiple can look ordinary once you run the rate.

How do you calculate IRR in Excel?

Use the IRR function on a column of periodic cash flows: =IRR(values). The values must include at least one negative number, your initial investment, and at least one positive number, the cash coming back. Excel solves for the rate that discounts every flow back to a net present value of zero. The function assumes the periods are evenly spaced, so each row is one year, or one month, apart.

Lay a five year hold out like this, with the purchase as a negative outflow in year zero and the sale proceeds folded into the final year:

That returns roughly 12.7%. The large positive figure in year five is operating cash flow for the year plus the net proceeds from the sale, because IRR does not care what a number represents, only when it lands and how big it is. Keep the initial outlay negative. A common way to get a nonsense answer is to type the purchase price as a positive number, which tells Excel you received a million dollars on day one.

If the annual figure hides monthly timing that matters, build the schedule monthly instead and read the result as a monthly rate, then annualize it with =(1+monthly)^12-1. Do not mix monthly and annual rows in one IRR call.

What is the difference between IRR and XIRR in Excel?

IRR assumes every cash flow is one equal period apart. XIRR lets you attach a real date to each flow, so it handles the uneven timing that real deals actually have. Its syntax is =XIRR(values, dates), with a column of amounts next to a column of dates. Because a closing rarely happens on January 1 and a sale almost never lands exactly five years later to the day, XIRR is the more honest function for most transactions.

The gap between the two is not academic. A distribution that arrives in month two versus month eleven changes the annualized return, and only XIRR sees that difference. Use IRR when your model is genuinely built on clean annual or monthly periods. Use XIRR the moment your cash flows carry actual dates.

MIRR is the third option and it exists to fix a real criticism of IRR: standard IRR silently assumes you can reinvest every interim distribution at the IRR itself, which is often optimistic. MIRR lets you set a realistic reinvestment rate separately. Most deal memos still quote IRR or XIRR, so know that assumption is baked in when you read one.

What is a good IRR for a real estate deal?

There is no single benchmark, and anyone who quotes one without naming the strategy and the risk is guessing. A stabilized, low leverage core property throws off a modest IRR because the cash flows are predictable and the downside is small. A ground up development or a heavy reposition targets a much higher IRR precisely because the risk of the plan not working is real. The right question is not whether an IRR is good in the abstract, it is whether it compensates you for this specific risk versus what you could earn elsewhere.

Compare a projected IRR against your own cost of capital and against the return on the next best use of the money. And treat any projected IRR with suspicion until you see the assumptions behind the sale, because the terminal value in the final year usually drives most of the number. A small change in the exit cap rate can swing IRR by several points, which is why sensitivity tables matter more than the single point estimate.

Why is my IRR formula returning a #NUM error?

An IRR of #NUM almost always means the cash flows do not contain both a negative and a positive value, or Excel could not converge on a rate within its iteration limit. The function needs at least one outflow and one inflow to have something to solve for. If every number is positive, there is no rate that makes the series worth zero, and Excel gives up.

When the flows are genuinely mixed and it still errors, hand the function a starting guess: =IRR(B2:B7, 0.1) nudges the solver toward 10%. Deals with several sign changes, money out, in, out again for a capital call, then in at sale, can have more than one mathematical solution, and the guess steers Excel toward the sensible one. If it still fails, check that no cash flow cell is stored as text, because a number formatted as text is ignored and can quietly strip the only negative value out of the range.

Should I use IRR or NPV to evaluate a deal?

Use both, because they answer different questions. IRR gives you a rate you can compare across deals of different sizes. Net present value gives you a dollar figure of value created at a discount rate you choose, which IRR cannot do because it solves for the rate instead of assuming one. IRR can also mislead when comparing a small fast deal against a large slower one, since a high percentage on a tiny base can create less actual wealth than a lower percentage on a large base.

The clean workflow is to screen on IRR, then confirm with net present value at your real cost of capital before committing. If you are not set up for the second step yet, walk through how to calculate NPV in Excel, which uses the same cash flow column you just built. The two functions are two views of the same discounted cash flow, and serious underwriting looks at both.

How do you get the cash flows into Excel in the first place?

An IRR is only as good as the cash flows feeding it, and those start in documents, not in a clean spreadsheet. The operating income comes off a trailing statement, the debt service off a loan document, the rent off a rent roll, and all of them tend to arrive as PDFs or scans. Retyping them is where a transposed digit slips in and quietly moves the return.

Convert the source documents instead of rekeying. Drop the property's operating statement PDF into a spreadsheet so each income and expense line lands in its own row, and do the same with the rent roll to confirm in place rent. When a deal's returns hinge on the cash that actually moved through the accounts, it helps to turn the underlying account statements into clean rows before you build a single formula on top. Scanned pages are fine, since OCR reads the characters off the image. The full deal package workflow lives on the PDF to Excel for real estate page.

Before you trust any IRR, confirm the net operating income underneath it is right by working through NOI from a T12, and pair the return with the coverage a lender will test using DSCR. A return that only works at an exit cap rate no buyer will accept is a return on paper, so pressure test the sale against a realistic cap rate.

A short checklist before you trust the IRR

• Confirm the initial investment is entered as a negative number and the range holds at least one positive inflow.
• Check that every cash flow cell is a real number, not text, so nothing is silently ignored.
• Use XIRR with actual dates whenever the timing is irregular, which is almost always.
• Isolate the terminal sale value and run the IRR again at a more conservative exit assumption.
• Confirm with net present value at your cost of capital before the IRR alone decides the deal.

Get those right and the rate you carry into an investment committee is one you can defend line by line. The function was never the hard part. The cash flows were.