XIRR for real cash flows
Advanced Investment BankingThe standard IRR function assumes cash flows occur at regular intervals (typically annually). In practice, investment cash flows happen on specific dates, like capital calls, distributions, and exits rarely align with neat annual periods. The XIRR function calculates the internal rate of return accounting for the actual timing of each cash flow.
XIRR uses the exact dates to determine the annualized return, making it the industry standard for measuring private equity, venture capital, and real estate investment performance.
XIRR syntax:
=XIRR(values, dates, [guess])
- values: The cash flows (outflows as negative, inflows as positive)
- dates: The dates corresponding to each cash flow
- guess: Optional starting estimate for the iteration (defaults to 0.1)
Your task:
You're analyzing a private equity investment that spans over 3 years. The fund made two capital calls (negative cash flows) and received multiple distributions before the final exit.
Calculate the internal rate of return in cell B15.
Note: One row in the cash-flow table is a fund administration fee true-up and should be excluded from the investment performance XIRR.
Need some help?
Hint 1
Use XIRR with matching ranges of cash-flow amounts and dates, and make sure your selected values include both negative and positive cash flows.
Hint 2
For investment performance, include capital calls, distributions, and exit proceeds, but exclude the fund admin fee true-up row.
Hint 3
The performance cash flows are the 7 investment rows above the blank line, so your XIRR ranges should stop before the fee row.
XIRR for real cash flows
Advanced Investment BankingThe standard IRR function assumes cash flows occur at regular intervals (typically annually). In practice, investment cash flows happen on specific dates, like capital calls, distributions, and exits rarely align with neat annual periods. The XIRR function calculates the internal rate of return accounting for the actual timing of each cash flow.
XIRR uses the exact dates to determine the annualized return, making it the industry standard for measuring private equity, venture capital, and real estate investment performance.
XIRR syntax:
=XIRR(values, dates, [guess])
- values: The cash flows (outflows as negative, inflows as positive)
- dates: The dates corresponding to each cash flow
- guess: Optional starting estimate for the iteration (defaults to 0.1)
Your task:
You're analyzing a private equity investment that spans over 3 years. The fund made two capital calls (negative cash flows) and received multiple distributions before the final exit.
Calculate the internal rate of return in cell B15.
Note: One row in the cash-flow table is a fund administration fee true-up and should be excluded from the investment performance XIRR.
Need some help?
Hint 1
Use XIRR with matching ranges of cash-flow amounts and dates, and make sure your selected values include both negative and positive cash flows.
Hint 2
For investment performance, include capital calls, distributions, and exit proceeds, but exclude the fund admin fee true-up row.
Hint 3
The performance cash flows are the 7 investment rows above the blank line, so your XIRR ranges should stop before the fee row.