PROIRRλ: Overcoming limitations of IRR and XIRR
- Viswanathan Baskaran
- 4 days ago
- 2 min read
We have upgraded our PROIRR function with a new function: PROIRRλ for modern spreadsheet users. This is compatible with Microsoft 365, Zoho Sheets, and Google Sheets.
You can download the sheet. You can copy the Lambda to your workbook by either copying the Lambda one-by-one or by just by moving one of the sheets in this workbook to your workbook.
Why PROIRRλ
IRR and XIRR, which are built into spreadsheets, have certain limitations.
1️⃣When there are mixed cash flows, these functions may return a positive IRR even when the project has negative returns, and vice versa. Check out this blog for an example.
2️⃣XIRR will give 0% as the return if the first cash flow is zero or in certain situations of mixed cash flows. The PROIRRλ overcomes these limitations and brings enhancements with the following features:
🎯Use it with or without dates: This function replaces both IRR and XIRR, allowing the users to pass date of cash flow as an optional argument.
🎯Work with all types of cash flows: It works and computes the implied IRR even when the initial cash flow is zero, or it is mixed cash flows.
🎯Enforces sign consistency: By default, when net cash flows are negative, it will ignore any positive IRR, and vice versa. Users can override this with an optional parameter.
Why upgrade from PROIRR to PROIRRλ
We have PROIRRλ over our PROIRR function that we published in April 2025 for two main reasons.
1️⃣Enforce sign consistency: The prior version was meant to address the bug in XIRR when the initial cash flow is zero. But the new function also addresses the mathematical limitations of IRR — providing for misleading IRR for mixed cash flow projects.
2️⃣Become BEST compliant: In August 2025, we published The BEST framework addresses proper design principles for dynamic arrays and Lambda in MS Excel. We have incorporated the prescriptions and recommendations of the standards in this new Lambda.
Check out The BEST modeling framework here.
And there is a bonus
The file also includes the PRONPVλ, which is a replacement for the XNPV function in MS Excel. The XNPV function does not work with negative returns. That limitation is overcome using the PRONPVλ.
Get started
To use the Lambda, download the MS Excel file and do either of the two: Option 1: Move any of the sheets in the workbook to the file in which you want to use the function.
Option 2: The file has the individual Lambda provided in a textbox with associated comments. You can copy and paste it into the "Advanced Formula Environment" and store it in your file or copy and paste into your Lambda repository, if you have one.
Comments