IRR, NPV and development profit: read them together

Define the numerator, denominator and cash flow

A return figure needs a label that explains what has been measured. Development profit before financing differs from profit after financing. Profit on cost uses a different denominator from profit on revenue. A return to all project capital differs from the return to an equity investor after debt movements.

Agree the definitions before comparing schemes, versions or external reports. State whether costs include land, transaction costs, finance and tax; which cash flows belong to the investor; and whether a sale at the end includes any remaining liabilities. Identically named outputs can otherwise describe different economics.

Sources: Microsoft Support: IRR function; Microsoft Support: NPV function

An extra year changes the return without changing the profit

Consider an original example with one payment of 10 million today and one receipt of 13.31 million. There are no intermediate cash flows, borrowing, fees or taxes. The amounts are in one currency, and the periods below are exact annual intervals. They illustrate the mathematics, not a suggested hurdle rate.

Receipt after three years gives an annual IRR of 10%. Receipt after four years gives approximately 7.41%. Both cases have a surplus of 3.31 million, profit on cost of 33.10% and profit on revenue of 24.87%. If these were the only equity contribution and distribution, the equity multiple would be 1.331 times in both cases.

At an illustrative annual discount rate of 8%, the three-year case has an NPV of approximately positive 0.566 million. The four-year case has an NPV of approximately negative 0.217 million. The later receipt has fallen below the return required by that particular discount assumption.

Match the function to the timing

Excel IRR assumes equally spaced periods. A monthly cash flow therefore produces a monthly IRR, which is converted to an effective annual rate by compounding: (1 + monthly IRR) raised to 12, less 1. Multiplying by 12 does not produce the same effective annual rate.

For irregular dates, Excel XIRR calculates a dated return using a 365-day year. XNPV applies the same day-based convention to a specified annual discount rate. A monthly period model and a dated model can differ even when their displayed month labels appear similar. Check the underlying dates before treating the difference as an error.

Excel NPV places its listed cash flows at the ends of periods. A payment made at the starting date is added separately to that NPV result. Including it as the first future period shifts its timing. Include zero-value periods where needed; deleting an empty month can compress a periodic timeline.

Sources: Microsoft Support: IRR function; Microsoft Support: XIRR function; Microsoft Support: XNPV function; Microsoft Support: NPV function

Separate the project return from the equity return

Start with a schedule of the underlying project payments and receipts. A separate equity schedule reflects the chosen financing, including equity contributions, distributions and the effect of debt service. Trace one to the other so a reviewer can see whether a higher equity return comes from stronger project economics or a different financing structure.

Debt can change both the amount and timing of equity invested. It also introduces obligations and conditions. A high equity IRR therefore does not establish that funding is sufficient or that repayment can occur when required. Put debt balances, the funding gap and key repayment dates beside the return.

RICS' discussion of developer returns addresses the need for consistent treatment of financing and required return. Document how the discount rate matches the chosen cash flow. A calculation cannot resolve an unclear definition of the capital being measured.

Sources: RICS: Approaches to developer returns in appraisals

Recognise when a single IRR is not enough

A cash flow with more than one change between negative and positive amounts can have multiple IRR solutions, or none. A later remediation cost, further development phase or capital call can create that pattern. Microsoft notes that its iterative functions may return different solutions depending on the starting guess, or fail to find one.

If the result is surprising, examine the full signed cash flow and calculate NPV across a relevant range of discount rates. Do not replace a failed calculation with zero or select the most attractive root. Record the ambiguity and use measures that remain interpretable for that cash flow.

For projects of different scale, report the cash amounts as well as the rates. A high percentage on a small investment does not itself settle how a limited capital budget should be allocated. Delivery constraints, exposure, strategic fit and the amount of value created still require judgement.

Sources: Microsoft Support: Go with the cash flow: Calculate NPV and IRR in Excel

Make the return reproducible

Keep the cash flow, dates, calculation convention and discount-rate assumption with the reported result. Reconcile a simple case independently before relying on a complex one. If another model disagrees, compare scope and timing first, then compare the calculation. A review is much quicker when it starts from matching definitions.

• Are the figures before or after financing and tax?
• Is the return periodic, effective annual or based on actual dates?
• Does the discount rate match the currency and cash flow basis?
• Are final sale costs, liabilities and later capital calls included?
• Can the reported return be reproduced from the saved schedule?