Excel Formulas for Private Equity: XIRR

What is XIRR?

Whether you are comparing cash flows from multiple investments or calculating returns for future waterfall distributions, the XIRR function in Excel is a valuable tool that you’ll need at your disposal. XIRR is a simple yet powerful Excel function that calculates the Internal Rate of Return (“IRR”) for a series of cash flows that is not in annual intervals. To refresh, IRR is the annualized discount rate (“r” in the formula below) that sets the Present Value (“PV”) of a project equal to zero.

Where:

PV= Present Value

FV= Projected Future Value of Cash Flows

r= Discount Rate

t= Time Periods

XIRR vs. IRR

The XIRR and IRR functions in Excel are nearly identical besides one crucial difference: timing assumptions. In the traditional IRR function, the time period between each cash flow is assumed to be one year. However, we know in business that this is not always true. You could find yourself with quarterly, monthly, or even daily cash flows or distributions, and this is where XIRR thrives. The XIRR function calculates the IRR for a series of cash flows based on the actual number of days passed in a 365-day period (To account for leap years, Excel uses a 366-day period every 4 years).

XIRR Formula Excel

When using IRR, Excel will simply ask you to select the series of cash flows you would like the value, and then it will calculate the annualized discount rate that will set our PV equal to zero. With XIRR, you’ll have to select the series of cash flows as well as the dates for each specific cash flow.

IRR

=IRR(values, [guess])

XIRR

=XIRR(values, dates, [guess])

Values: a series of cash flows that correspond to a schedule of payments in dates. Cash flows are discounted based on a 365-day year. Investments are displayed as negative numbers, while distributions displayed as positive numbers. Values is a required parameter.

Dates: a schedule of payment dates that corresponds to the cash flow payments. Dates must be entered in Date Excel format, or using the Date Excel function. Dates is a required parameter.

Guess: a number you guess will be close to the IRR. Guess is an optional parameter and is typically not needed unless the calculation results in a severely negative number, where you may need to change the guess from 0 to -0.1  If the IRR results in a 0.00%, you should double check the guess to make sure it is calculating correctly and not returning 0.00% as an error.

Operating tip: When operating the XIRR function, make sure that the columns you have entered cash flows and dates into line up exactly! For example, you can see in the XIRR function that the cash flows are B3:G3 while the dates are B1:G1. If the dates were C1:H1, we would run into a calculation issue.

XIRR in Real Estate:

At Top Shelf Models, we prefer to use the XIRR function in all of our models. XIRR is a powerful tool that allows ultimate flexibility when calculating IRR. XIRR can provide accurate calculations of both unlevered and levered IRR, as well as Investor IRR. Additionally, XIRR is crucial to accurate waterfall calculations. If you are dealing with anything besides annual distributions, XIRR should be your preferred IRR function. Just be sure to check your JV Agreements and other legal documents to confirm that a specific function is not required in your calculations. If not, XIRR will provide you with a more flexible and accurate calculation, especially if you are working with daily distributions.

---

About the Author

Eric Bergin is the founder of TSM. He realized that there was a need for real estate financial models that were more than just generic templates. He wanted to create a personalized product for his customers that would ensure success for them and their company. Please reach out to him if you have any questions about XIRR or if he can help you with your modeling needs.