IRR financial model: project cash flows and checks

An IRR financial model finds the discount rate that makes a business project's net present value zero. It needs a dated or regularly spaced cash-flow schedule, including the initial investment and all relevant later cash movements. Calculate the return, prove it with a discounted cash-flow check and compare prepared scenarios on the same basis.

Start with project cash, not accounting profit

For a capital project, gather the approved expenditure schedule, operating benefit assumptions, incremental operating costs, working capital changes and any approved disposal proceeds. Record project, scenario, period or payment date, cash-flow category, amount and assumption owner. Keep actual costs distinguishable from future assumptions.

Use incremental cash: the receipts or savings and payments that change because the company undertakes the project. An existing cost that continues unchanged does not become a project benefit merely because someone allocated it differently. Keep the capital versus operating budget distinction clear when equipment purchases and running costs share a workbook.

Choose the cash-flow scope before calculating. The example below is an unfinanced, pretax equipment proposal. It excludes borrowing, interest, cash taxes and residual value by assumption. A full company approval process may require those items on its chosen basis; finance must supply the consistent schedule.

Use the formula that matches the periods

For equally spaced annual cash flows:

NPV at rate r = Cash flow at time zero + Σ[Cash flow in year t / (1 + r)^t]

IRR = The rate r for which NPV equals zero

Microsoft's IRR documentation requires regular intervals and at least one positive and one negative value. The order of the values represents their timing. A missing year therefore needs an explicit zero cash flow; deleting its row changes the investment being measured.

If amounts occur on irregular dates, use Microsoft's XIRR function, which uses the payment dates and a 365-day year:

=XIRR(D2:D5,A2:A5)

Here A contains valid spreadsheet dates and D contains the corresponding signed cash flows. Do not substitute period labels for dates. For monthly IRR, the returned rate is monthly; convert it to an effective annual rate with (1+monthly_rate)^12-1 before comparing it with an annual hurdle.

Build an equal-period project example

A US distributor proposes a conveyor upgrade. Management supplies these annual cash assumptions. Year zero is the purchase date; later cash flows occur exactly one and two years later.

Enter net cash flow in D2 and fill down through D4, then calculate IRR separately:

=B2-C2
=IRR(D2:D4)

The result is 20.0% annually. The total undiscounted cash benefit is 700,000 − 572,000 = $128,000; that dollar figure is different from the return rate. Keep both visible so an attractive percentage does not hide the size of the proposed commitment.

Prove the rate and check NPV

Discount each net cash flow at the calculated 20.0%:

The zero total proves the rate for this schedule. In Excel, place IRR in F2 and use:

=NPV(F2,D3:D4)+D2

The initial outflow is outside NPV because it occurs now. Microsoft's NPV documentation places the supplied future values at period ends. Including D2 inside that range would discount the initial payment incorrectly.

For a separate decision check, assume management has approved a 12.0% annual hurdle rate. =NPV(12%,D3:D4)+D2 gives $43,878, rounded to the nearest dollar. The hurdle is an example assumption, not a universal benchmark for equipment projects.

Compare a prepared timing scenario

Now move only the second benefit period's net receipt to the end of year three. All cash amounts remain unchanged:

Rates are rounded to one decimal place and NPV to whole dollars, using unrounded calculations. Both schedules still total $128,000 undiscounted. The lower return comes entirely from receiving cash later, which is the issue the project owner must explain.

For a wider operating budget around the investment, use the relevant manufacturing financial model or construction financial model. Keep project returns tied to their incremental cash schedules.

Where it goes wrong

• Removing empty periods. A year without cash still occupies time in an equal-period calculation.
• Using profit as cash. Depreciation, inventory purchases and collection timing require a cash bridge.
• Mixing return bases. A pretax project rate and an equity cash-flow rate answer different questions.
• Treating one IRR as conclusive. Repeated sign changes require extra review; project size and cash constraints also matter.

Covirage is not affiliated with Microsoft.

Compare your supplied project scenarios in Covirage

Bring your prepared project cash-flow workbook and scenario results to Covirage. Covirage maps the columns; its tools compute and check the cash-flow figures, and the external AI model explains scenario differences with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own data.

See project cost control for the supporting operating context.