Returns are the main output investors, lenders and sponsors look at when they decide whether to invest in a project, divest from a portfolio or refinance an asset. Yet return calculations in Excel are one of the most common sources of error in the models our model audit team reviews. From using the wrong Excel function to misaligning cash flow timing, small mistakes can lead to materially misleading investment decisions.
Joshua Grimm (opens in a new tab) (Director, APAC Energy and Infrastructure, Singapore), Shiv Sindhu (opens in a new tab) (Manager, EMEA Model Audit, India) and Charu Mathur (opens in a new tab) (Associate Director, London) presented our webinar on how to calculate and interpret returns in project finance models. This article captures the main points. You can watch the full webinar.
The webinar walked through the concepts of NPV and IRR, demonstrated how to calculate project and equity returns in a financial model and highlighted the errors the team finds most often.
What investor returns mean in project finance
Returns in project finance measure the economic benefit investors receive for taking part in a project. In most cases that means cash. In project finance, cash can be trapped in the project company by debt covenants, reserve account requirements and distribution lock-ups. So the question is not only how much cash the project generates, but when that cash can reach the investor.
“Even as a financial modeller, I want to have the equity return metrics in the model at the start, so that I can see the impact of the calculations that I’m making throughout.”
For a given amount of cash flow, the biggest driver of returns is timing. Cash received sooner is discounted less, carries less risk and so produces higher returns. That is why the debt repayment profile, the tenor of the facilities and the restrictions on distributions all affect equity returns directly, even when the project economics do not change.
Returns are also the basis for comparing investments, assessing portfolio diversification and running sensitivities on the main assumptions. The metrics are standard across the industry, so they compare directly from project to project.
Types of returns in project finance models
Investors receive returns from a project in several ways, and each is modelled differently.
Dividends and distributions are cash paid from the project company to equity holders after debt service. They are often subject to distribution tests, such as a minimum DSCR, a fully funded debt service reserve account, no outstanding defaults and sufficient retained earnings. The gap between when cash is available and when it can be distributed directly affects equity returns.
Shareholder loans are a more flexible alternative to pure equity. The return comes through principal repayment and interest, which in some jurisdictions may be tax deductible. As Joshua Grimm noted, equity contributed through a shareholder loan can cost less than equity contributed as share capital.
Preferred equity sits between debt and common equity. It carries fixed returns and ranks ahead of common equity, which adds another layer to how cash leaves the project.
The main metrics for these returns are the internal rate of return (IRR), which expresses the return as a percentage, and net present value (NPV), which expresses it as an amount of money. Both take account of when cash flows in and out of the project.
How to calculate NPV in a project finance model
NPV is today’s value of expected future cash flows minus the value of the initial investment. The discount rate stands for the risk the investor takes, combining the time value of money and a risk premium.
If the NPV is positive, the investment earns more than the required rate of return and adds value. If it is zero, it breaks even against the hurdle rate. If it is negative, the present value of future cash flows does not cover the investment, and the project should not go ahead on those terms.
The webinar showed an important difference between the NPV and XNPV functions in Excel. NPV assumes cash flows arrive at regular annual intervals and discounts each period uniformly. XNPV discounts by the actual date of each cash flow, which is essential for models with quarterly, semi-annual or hybrid timelines.
“In many financial models we review, the timeline is not annual. It is quarterly or hybrid. Using the NPV function instead of XNPV in those cases will give you an incorrect result.”
The demonstration showed that even on an annual timeline NPV and XNPV can differ, because of leap years. NPV assumes exactly equal periods, while XNPV works on a 365-day basis. Aligning the timeline to exact 365-day intervals removed the difference and confirmed that leap years caused it.
Our tutorial on calculating NPV without Excel functions works through the mechanics step by step, for anyone who wants to see what these functions do behind the scenes.
How to calculate IRR and XIRR in project finance models
IRR is the discount rate at which the present value of all future cash flows equals the initial investment: the rate at which NPV is zero. If the IRR exceeds the investor’s hurdle rate, the investment is profitable. If it falls below, the project does not meet the threshold.
The same distinction applies. IRR assumes regular intervals, while XIRR uses actual dates. For any model whose timeline is not perfectly annual, use XIRR.
XIRR has a practical trap: it needs a negative value in the first period to return a result. If the investment is delayed and the first period holds no cash flow, XIRR returns zero or an error. The fix is a very small negative number, such as -0.0001, in the first period. It does not materially change the result, but it lets the function calculate.
“If you do not have a negative first value in your XIRR range, just add a very minor value so that the formula functions correctly. Otherwise your return will just be depicted as nil.”
Our tutorial on the Excel IRR function and other ways to calculate IRR covers these details and the alternative approaches.
Project IRR and equity IRR: what each measures
Project IRR and equity IRR answer different questions. Project IRR measures the return from the project itself, before financing, to all its providers of capital. It indicates the intrinsic quality of the project. If the project IRR is above the market benchmark for similar assets, the project is technically sound and commercially viable.
Equity IRR measures the levered return to equity holders. It includes the effects of debt service, distribution restrictions, refinancing and the capital structure, and it is usually the metric investors test against their hurdle rate.
The webinar gave one clear warning. If the project IRR is negative but the equity IRR is positive, the project is not earning enough to cover its cost of capital, and the leverage structure is creating the positive equity return. Modellers and reviewers should treat that result with great care.
How capital structure affects equity returns
The webinar analysed how different financing levers move project and equity returns. The project IRR stays the same in every scenario because it is based on cash flows before financing.
Higher leverage reduces the equity investment, which increases the equity IRR because distributions are measured against a smaller base. Lower leverage increases the equity investment and reduces the equity IRR.
A longer debt tenor spreads repayments over more periods, leaving more cash for equity holders early on and usually producing higher equity returns. Higher interest rates increase debt service, reduce the cash available for distribution and lower the equity IRR.
Stricter distribution rules, such as a cash sweep or lock-up provisions, delay distributions to equity holders. That reduces the equity IRR even when the total cash received over the project life is unchanged. Our tutorial on cash sweep analysis in project finance explains how these mechanisms work.
For how debt service metrics interact with equity returns, our tutorials on CFADS, debt sculpting to a target DSCR and the loan life coverage ratio (LLCR) cover the mechanics between project cash flows and equity distributions.
Periodic discounting and XNPV
The webinar spent time on how XNPV discounts cash flows and how that differs from a manual periodic discounting approach. Modellers who build their own discount factors need to understand the assumptions XNPV makes.
XNPV assumes a 365-day year regardless of the calendar. It prorates the annual discount rate on a compounding basis instead of dividing it by the number of periods. The periodic rate for a quarterly model is (1 + annual rate) raised to the power of (days in period / 365), minus 1.
One reconciliation point matters: XNPV does not discount the first cash flow. It treats the first date in the range as the valuation date and adds that cash flow at face value. If a periodic calculation also discounts only future periods, the two approaches reconcile. If the current period’s cash flow is included in one and left out of the other, the results differ.
“Be very mindful when you are calculating the discounted value using these two approaches. It should be discussed and taken care of whether the current period cash flows are required to be included or not.”
Common errors in return calculations
From their model audit work, the team set out the errors they find most often.
- Using NPV and IRR instead of XNPV and XIRR on a timeline that is not annual. This is the most common error the team sees. Any model with a quarterly, semi-annual or hybrid timeline needs the date-based functions.
- Misaligned cash flow and date ranges. In XNPV and XIRR the cash flow range and the date range must cover exactly the same periods. A mismatch gives a wrong result with no error message.
- Wrong signs on cash flows. From the investor’s point of view, investments are negative and distributions positive. Reversed signs give a meaningless return.
- No negative first value in XIRR. If the first period in the range holds no negative number, XIRR returns zero or fails silently.
- Financing cash flows in the project IRR. Project IRR uses unlevered cash flows. Include drawdowns or repayments and the result is neither a project return nor an equity return.
- Not decompounding the discount rate. Converting an annual rate to a periodic rate for manual discounting needs decompounding, not division. Dividing an annual rate by four for a quarterly model gives the wrong discount factor.
- Expecting XNPV to handle a changing discount rate. XNPV takes a single annual rate. If the rate changes over time, use periodic discounting.
To build these skills hands-on, our Best Practice Project Finance Modelling course covers building the financial statements and cash flow model, and Advanced Project Finance Modelling covers detailed debt sizing, sculpting, return analysis and scenario modelling.