XIRR function - determining the Internal Rate of Return (IRR) for a series of periodically uneven cashflows.
What is the XIRR function?
The Excel XIRR function returns the Internal Rate of Return (IRR) for a schedule of cash flows that is not necessarily periodic. While the IRR function works well to determine the IRR for periodically annualized cashflows, the XIRR function assigns specific dates to each cashflow.
Therefore, the main benefit of using XIRR is that it can calculate a precise return for a series of cashflows that occurs unevenly. This is especially beneficial for calculating the return metrics of project financings and is used as best practice in the context of financial modeling for renewable energy investments.
How to use XIRR using Excel?
The XIRR function uses three inputs, of which only two are required.
- The first argument, values, requires the range of cashflows (both cash inflows and cash outflows) that the investment/project generates.
- The second argument, dates, requires the range of dates which are linked to each specific cashflow of the first range.
- The last argument, [guess], can help Excel to calculate the return faster but is not required to be filled in and we recommend omitting this argument when using XIRR.
What's important to consider when using XIRR?
First, the length of both ranges for the first two arguments must match exactly to ensure that Excel can link each cash flow to a specific date.
Second, the value range of cashflows does always need to start with a negative number as an investment typically requires an initial cash outflow - at least, this is how Excel thinks in the manner of this function. If the first number within the value range is not negative, the function will not work - however, there is a workaround: simply use a very small negative number for the first value. Below is an example of how the XIRR function is applied in a typical project finance model for renewable energy investments.
XIRR vs. IRR function - what's the difference?
The IRR function is much less flexible than the XIRR function and does not require any dates as input. The output is the same - the internal rate of return on the investment - however, when using the IRR function, Excel assumes that between each given value for a cashflow lay precisely 12 months.
As it is highly improbable to encounter an investment in the real world with only one cashflow at the end of each year, it is recommended to always prefer the XIRR function over the IRR function.
The XIRR function has the flexibility to consider different cashflows daily, which is why it uses daily compounding, while the IRR function uses simple annual compounding.
What does the IRR tell me as an investor?
Investors often view the IRR as the most crucial investment metric. From a mathematical standpoint, the IRR is the required discount rate for all future cashflows, which sets the investment's Net Present Value (NPV) to zero. This implies that if an investor invests specific payments in accordance with all the negative cash outflows of the input range of the XIRR function - he or she will earn the IRR as a return over the entire lifetime of the asset.
How to properly apply the XIRR function in a financial model for renewable energy investments?
Do you want to learn how to model the XIRR function within a financial model created explicitly for renewable energy investments? Then check out the Advanced Renewable Energy Financial Modeling course.
Article sources
