Master Internal Rate of Return (IRR) Calculations in Excel and Google Sheets

Search Fintrev

Search our guides, reviews and glossary.

The internal rate of return (IRR) is the discount rate at which an investment’s cash flows have a net present value of zero. In Excel and Google Sheets you calculate it by listing the cash flows in a column, with money paid out as negative numbers and money received as positive numbers, and then entering =IRR(range). If the cash flows land on irregular dates, use =XIRR(values, dates). If you want to set your own borrowing and reinvestment rates instead of accepting IRR’s built-in assumption, use =MIRR(values, finance_rate, reinvest_rate). The syntax is the same in both programs.

The formula is the easy part. Most of the work, and most of the errors, come from building the cash flow series correctly and reading the result in the right time unit.

What IRR measures

NPV discounts every future cash flow back to today at a chosen rate and adds the results, including the initial outlay:

NPV = CF₀ + CF₁/(1 + r) + CF₂/(1 + r)² + … + CFₙ/(1 + r)ⁿ

IRR is the value of r that makes this sum exactly zero. No algebraic formula solves for r once there are more than a few periods, so spreadsheets find it by iteration. They try a rate, measure how far NPV is from zero, adjust the rate and repeat until the result is close enough.

The answer is a percentage that summarizes the whole cash flow pattern in one number. That makes it popular in corporate finance, private equity and venture capital, where investments of different sizes need a common yardstick. The single number also hides the size of the investment, the timing of the money and the assumptions behind it.

IRR vs. NPV

The two measures come from the same equation but answer different questions.

NPV depends heavily on the discount rate, which is often an estimate. IRR avoids picking a rate up front, and that is part of its appeal. When two projects compete for the same capital, though, choosing the one with the higher IRR can leave you with less total value than choosing the one with the higher NPV. IRR equals the annual return actually earned only when the project produces no interim cash flows or when those interim flows can be reinvested at the IRR.

Building the cash flows from company financials

When you compute IRR for a company or a project inside one, the spreadsheet function is the last step. First you need a cash flow series, and it has to be cash, not accounting profit. Net income includes non-cash items such as depreciation and leaves out capital spending, so running IRR on earnings gives a misleading answer. The difference between revenue and profit, and between profit and cash, matters here.

A common way to build the series from the financial statements:

1. Year 0 outflow. The purchase price, or the upfront capital spending for a project, entered as a negative number.
2. Annual free cash flow. Start from operating cash flow on the cash flow statement and subtract capital expenditures. When you are forecasting, the usual build is EBIT × (1 − tax rate) + depreciation and amortization − capital expenditures − increase in net working capital.
3. Exit or terminal value. If you assume the business or asset is sold or valued at the end, add that amount to the final year’s cash flow. This is often the largest single input, so it drives the result.
4. Choose the perspective. Cash flows before financing give a project (unlevered) IRR. Cash flows after interest, debt repayments and new borrowing give an equity (levered) IRR. Leverage usually pushes equity IRR away from project IRR, so label which one you computed.

Each row in the column should represent one equal period, such as a year, quarter or month. If a period has no cash flow, enter 0 instead of leaving the cell blank, because IRR skips empty cells and would compress your timeline. For the methods used to set the exit value, see business valuation methods.

How to calculate IRR in Excel and Google Sheets

The following example compares two hypothetical investments with annual cash flows.

The real estate cash flows end with a large sale. The startup needs a second injection of cash in year 1 before it starts paying back.

Step 1: Enter the cash flows

Put headers in row 1. Enter the years 0 to 5 in A2:A7 and the real estate cash flows in B2:B7. Enter the startup’s years in C2:C6 and its cash flows in D2:D6. Outflows must be negative. If every value has the same sign, there is no rate that brings NPV to zero, and the function returns an error.

Step 2: Apply the IRR function

In B9 enter:

=IRR(B2:B7)

In D9 enter:

=IRR(D2:D6)

The full syntax is IRR(values, [guess]) in Excel and IRR(cashflow_amounts, [rate_guess]) in Google Sheets. The values must be in chronological order, and the first value is treated as occurring at time 0 with no discounting. The optional guess is a starting point for the iteration. If you leave it out, both programs start from 10%. You could write =IRR(B2:B7, 0.15) if you expect something near 15%. For a series with a single sign change, the guess only affects how quickly the function converges, not the answer.

Format the cell as a percentage with one or two decimals. With no decimals, a result such as 17.8% displays as 18% and hides real differences between projects.

Step 3: Verify with NPV

A quick check confirms the inputs are in the right cells. Discount the flows at the IRR you just calculated, and the result should be essentially zero:

=NPV(B9, B3:B7) + B2

Excel’s and Sheets’ NPV function treats its first value as arriving one period from now. The year 0 outflow therefore has to sit outside the function and be added separately. Writing =NPV(rate, B2:B7) discounts the initial investment by one period, which is one of the most common spreadsheet errors in capital budgeting. If your check returns a value far from zero, either the formula or the range is wrong.

Step 4: Read the results

With these inputs, the real estate investment has an IRR of about 17.8% and the startup about 9.9%. Formatted to whole percentages, they show as 18% and 10%.

On IRR alone the real estate deal looks better. Before deciding, a few factors change that comparison:

• Risk. The two investments carry different risks: property values, vacancies and interest rates on one side, and competition, market acceptance and funding on the other. IRR treats them the same way.
• Timing and concentration. About 150,000 of the real estate return arrives in the final year. If the sale price falls short, the IRR drops sharply. Changing that one cell is a quick sensitivity test.
• Horizon. Five years versus four. A shorter project frees capital sooner, and what that capital earns next affects which choice is better.
• Scale and NPV. The real estate deal commits twice as much money. At a given required return, compare the dollar NPVs, not only the rates.

IRR does not account for risk. A low-risk project with a modest IRR can be the better investment compared with a high-risk project that shows a higher IRR on paper.

Monthly, quarterly and installment cash flows

IRR returns a rate per period, whatever the period is. If each row is a month, the result is a monthly rate. This matters for loans, leases, installment plans and any model built on monthly rows.

To convert a monthly IRR to an annual figure, you have two options:

• Effective annual rate: =(1+IRR(range))^12-1. This compounds the monthly rate and is the right figure to compare with an annual IRR from another project.
• Nominal annual rate: =IRR(range)*12. This is how many loan rates are quoted (an APR-style figure), but it understates the compounded return.

Example (hypothetical): if a series of monthly installments produces an IRR of 1% per month, the effective annual rate is 1.01¹² − 1 ≈ 12.68%, and the nominal annual rate is 12%. For quarterly flows, raise to the 4th power instead of the 12th.

The same per-period logic applies to MIRR. Its finance and reinvestment rates must be stated per period as well, so annual rates need converting before you use them on monthly rows.

Using MIRR in Excel and Google Sheets

IRR implicitly assumes every positive interim cash flow is reinvested at the IRR itself. For a project with a high IRR, that assumption is often unrealistic. The modified internal rate of return (MIRR) replaces it with two rates you choose:

• Finance rate: the cost of funding the negative cash flows, such as a borrowing rate.
• Reinvestment rate: what the positive cash flows can realistically earn once received.

Mechanically, MIRR compounds the positive flows forward to the final period at the reinvestment rate, discounts the negative flows back to time 0 at the finance rate, and solves for the single rate that connects the two:

MIRR = (FV of positive flows ÷ PV of negative flows)^(1/n) − 1

Here n is the number of periods.

Step 1: Choose the two rates

Using the same example with hypothetical rate assumptions:

• Real estate: finance rate 6.5% (a mortgage-type cost), reinvestment rate 7% (a conservative rate for reinvesting rental income).
• Startup: finance rate 9% (reflecting higher funding risk), reinvestment rate 10%.

Step 2: Apply the MIRR function

In B10:

=MIRR(B2:B7, 0.065, 0.07)

In D10:

=MIRR(D2:D6, 0.09, 0.10)

The syntax is MIRR(values, finance_rate, reinvest_rate) in Excel and MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate) in Google Sheets. The finance rate always comes first. Entering the rates as decimals or as percentages both work.

Step 3: Read the results

• Real estate MIRR ≈ 16.1%, below its 17.8% IRR. Reinvesting the rental income at 7% rather than at about 17.8% lowers the overall return. You can check it by hand: the positive flows compounded at 7% to year 5 total about 210,958, and (210,958 ÷ 100,000)^(1/5) − 1 ≈ 16.1%.
• Startup MIRR ≈ 9.9%, essentially the same as its IRR. The assumed reinvestment rate (10%) and finance rate (9%) sit very close to the project’s own IRR, so replacing IRR’s reinvestment assumption barely changes the answer.

MIRR moves toward your chosen rates and away from the IRR. The gap between the two numbers grows with the size and earliness of interim cash flows and with the distance between your rates and the IRR. Changing the two rate inputs and watching MIRR respond is a simple sensitivity analysis. Like IRR, MIRR says nothing about risk.

Opportunity cost belongs in the same discussion. A long project that ties up capital can end up less profitable than a short one whose proceeds are quickly redeployed. The reinvestment rate is where you state what that redeployed money can earn.

Using XIRR for irregular dates

IRR assumes every row is the same length of time apart. Real investments rarely work that way: capital calls, distributions, property sales and contributions to a super or retirement fund arrive on specific dates. XIRR takes the actual dates and returns an annualized rate.

Step 1: Enter amounts and dates

Put each cash flow next to its date, for example amounts in B2:B7 and dates in A2:A7. The first entry is the starting point: the initial investment on its date. Excel’s documentation requires that no later date precede the first date, and dates must be real date values rather than text that looks like a date. Unlike IRR, rows with no cash flow are simply left out, because the dates carry the timing.

Step 2: Apply the XIRR function

=XIRR(B2:B7, A2:A7)

The syntax is XIRR(values, dates, [guess]) in Excel and XIRR(cashflow_amounts, cashflow_dates, [rate_guess]) in Google Sheets. A guess, if you add one, goes third: =XIRR(B2:B7, A2:A7, 0.1). It cannot replace the dates argument.

XIRR discounts each cash flow by (1 + rate)^(days since first date ÷ 365). That day-count basis is fixed at 365, including in leap years.

Step 3: See how XIRR differs from IRR

A hypothetical example: invest 10,000 on January 1, 2024, and receive 10,500 on July 1, 2024, 182 days later.

• =IRR on the two amounts returns 5%. It treats the gap as one period of unknown length.
• =XIRR with the dates returns about 10.3%: 1.05^(365/182) − 1.

Both numbers are correct, but they answer different questions. XIRR expresses the return on an annual basis, so a gain earned over a short holding period produces a high annualized figure. The reverse happens over long holding periods. Before comparing an XIRR with an annual IRR from another project, make sure the holding periods are comparable. A very high XIRR on a holding period of a few months may simply reflect annualization.

Excel and Sheets have no single built-in function that combines irregular dates with separate finance and reinvestment rates, an XIRR and MIRR hybrid. For that case you would build the compounding manually with XNPV-style date math, which uses the same 365-day convention as XIRR.

Why IRR isn’t calculating: #NUM and odd results

#NUM! error. Excel’s IRR iterates up to 20 times to find a rate accurate to within 0.00001%. Excel’s XIRR allows up to 100 attempts. If no rate is found, the cell shows #NUM!. Check these causes in order:

1. The range contains at least one negative and one positive value.
2. Values are numbers, not text. Numbers imported from accounting systems often arrive as text and are ignored.
3. The range covers the whole series and nothing extra.
4. For XIRR, the dates are valid date values and none precedes the first date.
5. If all of the above are correct, supply a guess (start with 0.1, then try values well above or below the expected answer).

#VALUE! error in XIRR. This usually means a date cell holds text, or the values and dates ranges are different sizes.

Two or more valid answers. A series whose sign changes more than once can have several IRRs. For example, −100 today, +230 in one year and −132 in two years has an NPV of zero at both 10% and 20%. The function returns whichever root it reaches from the starting guess, so =IRR(range, 0.05) and =IRR(range, 0.25) can give different results for the same data. Projects with large closing costs, such as decommissioning or remediation, or with staged refinancing produce this pattern. When it happens, the IRR is ambiguous, and NPV or MIRR (which always returns a single answer) is the better measure.

A negative IRR. If total inflows fall short of total outflows, the IRR is negative. A result of −8%, for example, means the cash flows are equivalent to losing about 8% per period. Excel displays it normally once the cell is formatted as a percentage. To flag losses visibly, apply conditional formatting (red text for values below zero) or a wrapper such as =IF(IRR(B2:B7)<0, "Loss: "&TEXT(IRR(B2:B7),"0.0%"), IRR(B2:B7)). Note that the wrapper turns the result into text, so keep a numeric version elsewhere if other formulas depend on it. Very large losses can also make the function fail to converge, and a negative guess helps in that case.

The result looks too high or too low. The usual causes are a period mismatch (monthly rows read as annual), a blank cell where a zero belonged, a missing terminal value, or using net income instead of cash flow.

How to interpret IRR

The standard rule compares IRR with a hurdle rate, which is the required return or the company’s cost of capital:

• IRR above the hurdle: the project is expected to earn more than its funding costs and adds value at that rate.
• IRR below the hurdle: the project does not cover its cost of capital and would normally be rejected.
• IRR equal to the hurdle: NPV at the hurdle rate is roughly zero, and the project neither creates nor destroys value.

In capital planning, IRR is typically used to compare alternatives that both look viable, such as opening a new location versus expanding an existing one, or comparing two equipment purchases. It works best when the options are similar in size, length and risk.

Limitations of IRR

• Reinvestment assumption. Interim cash flows are implicitly reinvested at the IRR. MIRR addresses this.
• Scale blindness. A 40% return on 10,000 creates far less value than a 15% return on 10 million. IRR ranks the small project higher, while NPV captures the dollar difference.
• Duration. A short project can show a high IRR without earning much in absolute terms. Comparing IRRs across very different horizons can mislead.
• Multiple or missing solutions. These occur with non-conventional cash flows, as shown above.
• Timing manipulation. Delaying contributions or accelerating distributions raises IRR without changing the total profit. This is one reason private market investors report IRR alongside a multiple of invested capital.
• No risk adjustment. IRR is a hurdle comparison, not a risk measure.

Explain like I’m five

You put money into something now and get money back later. IRR is the interest rate a savings account would have to pay to give you exactly the same money back at the same times. If that rate is higher than what the money costs you, the investment is worth doing. Excel and Google Sheets find the rate for you once you list the money going out and coming in.

FAQs

The Bottom Line

Analysts comparing projects or deals in Excel or Google Sheets get reliable IRR results only when the inputs are built from cash flows, with negative outflows, zeros for empty periods and the correct time unit. Use XIRR for dated flows and MIRR when IRR's reinvestment assumption is unrealistic. Because IRR ignores scale and risk, check competing projects against their NPVs before choosing one.