You’ve built a cash flow projection, the investment looks solid on paper, and then Excel returns a #NUM! error or a percentage that makes no sense. Calculating the internal rate of return in Excel isn’t complicated once you understand what the function actually needs, but setup mistakes are easy to make and difficult to diagnose without a clear framework. This guide covers exactly that: how to structure your data, which function to use (IRR, XIRR, or MIRR), how to interpret the result, and how to fix the errors that show up when something is off.
Structured financial models reduce these common setup errors because the cash flow tables are already built to the right specification before you write a single formula.
The three functions you’ll use are IRR , XIRR , and MIRR . Each one solves for a rate of return, but each makes different assumptions about your cash flows. Choosing the wrong one doesn’t always produce an error; sometimes it produces a number that looks plausible but is quietly wrong. By the end of this guide, you’ll know exactly which function fits your situation and why.
The internal rate of return (IRR) is the discount rate at which an investment’s net present value (NPV) equals zero. In plain English, it estimates the percentage return generated by the money invested in a business, startup or project.
In Excel, the standard formula is:
=IRR(values, [guess])
IRR becomes useful when compared with a benchmark such as:
A projected IRR of 18% does not automatically mean an investment is attractive. If the required return is 12%, the project may create value. If investors require 25% because of the risk involved, the same 18% IRR may be inadequate.
For entrepreneurs, this matters because a risky startup projecting an IRR of only 8% may offer little compensation for the operational and financial risk involved when investors have other places to deploy their capital.
The riskier the project, the greater the return investors will generally expect. IRR should therefore never be analysed alone. NPV, cash flow, funding requirements, payback period, financial risk and the assumptions supporting the forecast should also be considered.
An entrepreneur invests R2 million in a new coffee shop. The financial model produces a projected IRR of 8%. This may sound acceptable until the entrepreneur compares it with the potential long-term return from a diversified JSE or S&P 500 investment. If the market alternative could reasonably deliver approximately 10% to 12% per annum over a long period, an 8% startup return offers little compensation for the added operational and financial risk.
A US entrepreneur invests $1 million in a new manufacturing operation. The financial model calculates a projected IRR of 22%. The entrepreneur uses a hurdle rate of 15%, allowing for the historical long-term return potential of the S&P 500, the cost of funding and the higher risk attached to a startup. Because the projected IRR exceeds the hurdle rate by seven percentage points, the venture may offer sufficient additional return to justify further investigation.
IRR dominates in project finance, real estate development, and startup evaluation because it’s a single, comparable number that communicates across conversations. Whether you’re presenting to a bank, a venture capital firm, or a board, everyone understands what a percentage return means. NPV requires an assumed discount rate before it means anything to a listener; IRR doesn’t. That portability explains its prevalence in pitch decks, development appraisals, and investment committee papers.
Its key limitation is equally important to understand: IRR tells you the rate of return, not the scale of value created. A 40% IRR on a $10,000 investment creates far less wealth than a 15% IRR on a $1,000,000 investment. This is why IRR is almost always paired with NPV in a complete investment analysis. Neither metric alone is sufficient; together they give you both the rate and the magnitude of the decision.
| Function | Best Used For | Cash-Flow Timing | Formula |
|---|---|---|---|
| IRR | Investments with evenly spaced cash flows | Monthly, quarterly or annually | =IRR(values,[guess]) |
| XIRR | Investments with cash flows on actual irregular dates | Irregular dates | =XIRR(values,dates,[guess]) |
| MIRR | Investments where financing and reinvestment rates should be specified separately | Regular periods | =MIRR(values,finance_rate,reinvest_rate) |
The practical rule is straightforward:
Most conventional investment models begin with the initial investment as a negative cash flow, followed by positive operating or investment returns. However, later cash flows can also be negative where additional capital expenditure, remediation costs or further investment is required. Excel’s IRR function requires the cash-flow range to contain at least one negative value and at least one positive value. If all values have the same sign, IRR cannot calculate a valid return.
The logic is straightforward: IRR is looking for the rate at which the discounted present value of your inflows exactly offsets your initial outflow. Without a negative value, there’s no outflow to offset, and the equation has no meaningful solution. Getting this right before you type the formula saves a lot of diagnostic time later.
For the standard IRR function, a single contiguous column of cash flows is all you need. The values should be in chronological order, starting with the initial outflow at the top. For XIRR, you need two aligned columns: one for cash flow amounts and one for the corresponding dates. The date cells must contain real Excel date values, not text strings that look like dates. If Excel can’t read a date cell as a date, XIRR will return an error regardless of how the rest of the table is structured.
Excel ignores empty cells in an IRR range. However, if a genuine forecast period has zero cash flow, enter 0 rather than omitting that period. This preserves the correct sequence of equally spaced cash-flow periods. Also confirm that the range contains at least one negative and one positive cash flow before calculating IRR.
The syntax is =IRR(values, [guess]). The values argument is the cell range containing your cash flows. The [guess] argument is optional: it’s the starting estimate Excel uses to begin its iterative search. According to Microsoft’s Excel documentation, if you omit it, Excel starts from 10% and runs up to 20 iterations looking for a solution accurate within 0.00001%; if it can’t converge within that limit, it returns #NUM!.
Providing a guess closer to your expected result can help Excel converge on the correct answer when the default starting point fails, particularly for unconventional cash flow streams. For most standard investment scenarios, the default works fine. For complex project finance structures with irregular patterns, a guess argument is worth including as a precaution.
Take a project with an initial outlay of $150,000 in Year 0, followed by five annual inflows: $25,000, $38,000, $45,000, $52,000, and $60,000. Enter those six values in cells B2 through B7, with B2 containing -150,000. The formula =IRR(B2:B7) returns the annual rate of return for this investment. Because the cash flows are entered annually, the result is an annual rate; you don’t need to convert it.
If you add a guess argument, the formula becomes =IRR(B2:B7, 0.15), telling Excel to start its search from 15%. The result will be the same in most cases, but starting closer to the expected answer helps when the function struggles to converge. One important point: if your cash flows are monthly rather than annual, the IRR result is a monthly rate. Annualizing a monthly IRR requires the formula =(1+monthly_IRR)^12-1, not a simple multiplication by 12.
IRR assumes your cash flows happen at perfectly even intervals. Every period is treated as equal length, whether that’s a month, a quarter, or a year. Real investments rarely follow that schedule: a property development might have capital draws in March and August, a first lease payment the following January, and a refinancing event 18 months after that. When you plug those irregular cash flows into a standard IRR formula, Excel doesn’t know about the time gaps. It treats each entry as one period, regardless of whether those periods are 30 days or 300 days apart. The result looks like a rate of return but is silently wrong.
XIRR was built to solve this. According to Microsoft’s XIRR documentation, the function uses actual dates to calculate the true time elapsed between each cash flow, producing an annualized return that reflects reality rather than assumed equal spacing. For any deal where cash flows don’t land at predictable regular intervals, XIRR is the correct function to use.
The syntax is =XIRR(values, dates, [guess]). The values range and the dates range must be the same size and aligned row by row: the first date corresponds to the first cash flow amount, the second date to the second amount, and so on. Using the same six-period example, with actual dates in column A and cash flow amounts in column B, the formula is =XIRR(B2:B7, A2:A7).
XIRR returns an annualized rate regardless of the time intervals between cash flows, which makes it directly comparable across different projects with different cash flow schedules. That comparability is the main reason to prefer XIRR whenever real dates are available, even for cash flows that are roughly annual. The marginal effort of maintaining a date column is minimal, and the accuracy gain is real.
Standard IRR can become difficult to interpret when a project generates significant interim cash flows and those cash flows will realistically be reinvested at a rate different from the project’s calculated IRR.
MIRR, or Modified Internal Rate of Return, makes the financing and reinvestment assumptions explicit. It allows you to specify a finance rate representing the cost associated with negative cash flows and a separate reinvestment rate for positive cash flows generated by the investment.
This can provide a more useful return measure where financing and reinvestment assumptions materially affect the economics of the project.
According to Microsoft’s Excel documentation, the syntax is ‘=MIRR(values, finance_rate, reinvest_rate)’. There’s no guess argument; MIRR doesn’t iterate in the same way IRR does, which is why it always produces a single result. Using the same six-period cash flows, a 6.5% financing rate, and a 7% reinvestment rate, the MIRR will be lower and more conservative than the standard IRR for the same data. That difference is the honest cost of the reinvestment assumption.
MIRR is particularly useful when financing and reinvestment assumptions matter or when a project contains unconventional cash-flow patterns that make standard IRR difficult to interpret. Where projects differ materially in scale, timing or economic life, NPV should also be considered because it measures the absolute value created rather than only the percentage return.
IRR tells you the rate of return; NPV tells you the dollar value created. These are complementary metrics, not interchangeable ones. The decision rule for IRR is clear: if the IRR exceeds your hurdle rate (your cost of capital or required return), the project clears the bar. NPV then tells you how much value actually sits above that bar. A project with a 25% IRR and a $50,000 NPV is not the same proposition as a project with a 25% IRR and a $2,000,000 NPV, even though both clear a 12% hurdle rate by the same margin.
For independent projects where you’re simply asking, “Does this investment make sense?” IRR and NPV will generally agree. When the IRR exceeds the hurdle rate, NPV at that hurdle rate will be positive, and both metrics point toward the same decision. The friction appears when you’re comparing alternatives.
Conflicts between IRR and NPV arise most often when comparing mutually exclusive projects that differ in size, cash flow timing, or economic life. A smaller project might show a higher IRR while a larger project creates more total value. In that scenario, IRR ranks the smaller project higher, but NPV correctly identifies the larger project as the better use of capital. The professional recommendation is consistent: use NPV as the primary decision metric and treat IRR as supporting context, not the tie-breaker.
When the two metrics conflict, follow NPV. NPV measures the absolute increase in value to the investor, which is ultimately what matters. IRR is a rate; it tells you efficiency but not magnitude. Any complete investment analysis should include both, but NPV carries the final call when they diverge.
According to Microsoft’s IRR documentation, Excel calculates IRR iteratively and returns ‘#NUM!’ when it cannot find a result after 20 attempts. A common cause is a cash-flow range that does not contain at least one negative and one positive value. The default 10% starting guess may also fail for unusual cash-flow patterns, while multiple sign changes can create more than one mathematically valid IRR.
The fixes map directly to those causes. Verify that your cash flow range contains at least one negative and at least one positive value. If the sign convention is correct, add a guess argument closer to your expected result: try 0.05, 0.20, or even 0.50 depending on the project. If the cash flows have multiple sign changes, switch to MIRR, which sidesteps the convergence problem entirely by using an explicit finance and reinvestment rate rather than searching for an internal root.
When a cash flow series changes sign more than once, the underlying NPV equation can cross zero at more than one discount rate. This gives you multiple mathematically valid IRRs for the same data. Excel will return whichever solution is closest to your guess value, which means different guess arguments produce different results for the same cash flows. That’s not a formula error; it’s a mathematical property of the data.
The practical fix is MIRR, which always produces a single result. Alternatively, calculate NPV at your stated discount rate and use that figure to make the decision. NPV at a specified rate is unambiguous; it doesn’t have a multiple-solutions problem. For project finance deals with restoration costs, earn-outs, or phased capital calls that create sign changes after Year 0, MIRR or NPV-based analysis is more reliable than standard IRR.
A #VALUE! error generally points to invalid or non-numeric data being supplied to the calculation. Common culprits include numbers stored as text, incorrectly formatted inputs or date values accidentally included in the cash-flow range rather than kept in a separate date column. Check the referenced cells carefully and confirm that the cash-flow inputs are genuine numeric Excel values before rerunning the formula.
Running a quick ISNUMBER() check across your cash flow range before entering the IRR formula catches these issues before they produce errors. A single pass through the data quality check takes less time than diagnosing a #VALUE! error after the fact.
Building a clean IRR setup from scratch requires more than just entering a formula. You need structured cash flow tables with correct sign conventions, date columns aligned for XIRR, output cells labelled and formatted for anyone else who opens the file, and the return metrics connected to the same assumption layer that drives revenue, costs, and capital expenditure. Best Financial Models (BFM) offers financial model templates with IRR and XIRR pre-configured alongside NPV, DSCR, and break-even analysis, all drawing from a shared assumption layer. You enter your project inputs, and the return metrics populate automatically, without rebuilding the underlying logic.
The templates span more than 60 industries and are built under the direction of a certified analyst (FMVA and FPWM), so the cash flow structure, formula logic, and output layout are designed to meet the standards lenders and equity investors typically expect. Whether the model is for a solar project, a real estate development, a startup seeking venture capital, or a business applying for an SBA loan, the IRR and return analysis is already configured for the context.
The time saved on formula-building is real, but the deeper value is structural. A properly built cash flow table doesn’t just calculate the right number; it presents that number in a format that communicates credibility. Lenders and investors have seen hundreds of models. A clean, assumption-driven layout with labelled outputs signals that the person behind the numbers understands what they’re doing. A messy or inconsistently structured model, even with accurate formulas, introduces doubt at the wrong moment in a funding conversation.
BFM’s templates are fully unlocked Excel files, meaning every formula is visible and every calculation cell is editable. You can inspect the logic, verify the internal rate of return in Excel, and adapt the structure to your specific deal without starting from a blank spreadsheet. Pricing is one-time, starting at $39, with no subscription tiers. For more complex transactions or bespoke funding applications, BFM also offers custom financial modelling where a standard template isn’t the right fit.
An IRR is only meaningful when compared with an appropriate hurdle rate or required return.
For a company assessing a new project, the hurdle rate may be informed by its weighted average cost of capital (WACC), adjusted where necessary for the specific risk of the investment.
For an equity investor, the relevant comparison is normally the investor’s required equity return. In that case, the levered or equity IRR should be compared with the return required for taking that equity risk.
For project-level analysis, an unlevered IRR can be compared with an appropriate project hurdle rate to assess the underlying economics before financing.
Debt should be assessed separately through cash flow, repayment capacity and measures such as the Debt Service Coverage Ratio (DSCR). IRR measures investment return; it does not tell you whether a business can actually repay its loans.
A project with a 12% IRR evaluated against a 10% required return may create value. The same project evaluated against a 15% required return would fail the investment hurdle. The IRR has not changed. The required return has.
Set the hurdle rate before reviewing the IRR. Otherwise, it becomes remarkably easy to choose a hurdle rate that conveniently agrees with the investment decision you already wanted to make.
An IRR result is only as reliable as the assumptions behind the cash flows. Revenue projections, cost structures, and project timelines all carry uncertainty, and that uncertainty flows directly into the return calculation. A 10% reduction in projected revenue or a six-month delay in the first cash inflow can move the IRR by several percentage points on a typical investment, enough to push a marginally viable project below the hurdle rate or an apparently strong project into a different risk category.
Running base, upside, and downside scenarios on the same cash flow table yields a realistic return range rather than a single point estimate. Lenders and investors consistently ask for this sensitivity analysis because it demonstrates that the analyst understands where the model is vulnerable. A project that shows an IRR of 18% in the base case, 24% in the upside, and 11% in the downside tells a much more complete story than a model that presents only the base case and asks for a funding decision.
The three functions each have a clear role. Use IRR for evenly spaced cash flows where you control the intervals. Use XIRR when real transaction dates are available, and the timing between cash flows is irregular. Use MIRR when the reinvestment assumption matters, when sign changes in the cash flow stream create multiple solutions, or when you want a more conservative and defensible return figure for a project with large interim distributions. None of these functions is universally superior; the right choice depends on the structure of the cash flows.
Pairing the internal rate of return in Excel with NPV is the professional standard for a reason. One metric gives you the rate of return; the other gives you the total value created. A project that clears your hurdle rate by a wide margin but produces a modest NPV is a different proposition from one that barely clears the hurdle but generates substantial absolute value. Both metrics belong in every investment analysis, and when they conflict, NPV carries the decision.
The mechanics of calculating the internal rate of return in Excel can be learned in an afternoon. The harder work is ensuring the cash flow assumptions underlying the number are realistic, defensible, and structured to communicate clearly to whoever is reading the model. For anyone who wants a well-structured starting point, Best Financial Models offers ready-built templates with IRR, XIRR, NPV, and sensitivity analysis already configured, so the focus stays on the inputs and assumptions that actually drive the investment decision rather than on the formula setup that surrounds them.
Need Support with Your Business Plan, Financial Model or Company Valuation?
Established in 2006, JTB Consulting assists entrepreneurs, SMEs and established companies across South Africa and international markets with professionally prepared, bank-ready and investor-ready business documentation.
Our services include custom Business Plans, Investor Pitch Decks, Excel Financial Models, Company Valuations, Financial Projections, Market Research and Feasibility Studies. Each engagement is structured to support funding applications, investor submissions, business acquisitions, expansion decisions and long-term strategic planning.
Whether you are launching a startup, seeking funding, valuing a business, testing a new project, or preparing for investor discussions, JTB Consulting can provide the financial and strategic analysis you need to strengthen your decision-making and improve funding readiness.
To discuss your requirements, contact JTB Consulting Founder, Dr Thommie Burger, on +27 66 206 8920, by email (info@jtbconsulting.co.za) or via LinkedIn.
We don’t spam, and you can always opt out at any time.
