Overcoming limitation of XIRR with PROIRR
- Viswanathan Baskaran
- Apr 9
- 3 min read
XIRR is one of the commonly used functions in financial modeling to compute IRR when cash flows occur at irregular date intervals.
However, the function does not work under two situations.
1️⃣ When the cash flow is zero
2️⃣ For mixed pattern cash flows, especially if the net return is negative
We, therefore, have created an alternative to XIRR called PROIRR using MS Excel Lambda. You can see its use and its advantages over XIRR in the file.
In order to use the Lambda, you need to have MS Office 365 or access MS Excel through online version. The Lambda will also work with Zoho Sheets and Google Sheets.
You can also learn about the limitations of XIRR from this video.
The Lambda in the file is also reproduced below. This contains comments. You will need to paste it in Advanced Formula Environment. Alternatively, you can remove the comments (all lines starting with "//") and paste the content in Name manager.
In order to get IRR, we first need a function that will compute NPV for irregular cash flows. XNPV also has certain limitations and hence we need to use a custom NPV function.
// This function is an alternative to XNPV which can work with negative returns.
// It requires 3 input parameters:
//Rt: The discounting rate
//CF: The cash flows
//Dts: The date of the cash flows
// It returns the NPV as on the earliest date of the cash flow
PRONPV=LAMBDA(Rt,CF,Dts,
LET(StDt, MIN(Dts),
//Compute the no. of days between the date of cash flow and the earliest date
DtGps, Dts - StDt,
DaysperYr, 365,
//Calculate the PV using the equation PV = CF/(1+rt)^n where n is no. of days gap / 365
PVs, Cf / (1 + Rt) ^ (DtGps / DaysperYr),
Return, SUM(PVs),
Return)
);For IRR, you can use the following Lambda
//This function is an alternative to XIRR that avoids certain bugs in XIRR
// This computes IRR using an iterative approach
// It requires 2 mandatory parameters and 4 optional parameters
// CF and Dts are mandatory parameters
// use the [numDec] parameter to mention the no. of digits to round the NPV to get IRR; by default the function rounds NPV to three decimals
// use the [CFRQ] paramter if you would like to get quarterly or semi-annual returns; type 4 for quarterly and 2 for monthly. The default value for the parameter is 1 i.e. annual
// NIter is the maximum number of iterations that you would like to run to get the results; its default value is 100
// [guess] parameter provides the initial seed value for the IRR and is used in the first trial; its default value is 10%
PROIRR=LAMBDA(CF,Dts,[numDec],[CFrq],[NIter],[guess],
LET(//Assign default value to optional parameters
Rt, IF(ISOMITTED(guess), 10%, guess),
_NIter, IF(ISOMITTED(NIter), 100, NIter),
_numDec, IF(ISOMITTED(numDec), 3, numDec),
_Cfrq, IF(ISOMITTED(CFrq), 1, CFrq),
//Calculate the NPV of the cash flows; assumes each year to have 365 days
FirstNPV, PRONPV(Rt, CF, Dts),
//Recalibrate the discount rate based on first NPV
SecondRt, IFS(FirstNPV = 0, Rt, FirstNPV > 0, Rt + 1%, FirstNPV < 0, -Rt),
//Compute the NPV again with the recalibrated rate
SecondNPV, IF(FirstNPV = 0, FirstNPV, PRONPV(SecondRt, CF, Dts)),
//Perform looped operation to recalibrate the discount rate till NPV goes to zero
//The looped operation will rework the NPV as well as the discount rate; therefore pass all the required data points as a stack into the reduce function
ReturnStack, REDUCE(VSTACK(FirstNPV, Rt, SecondNPV, SecondRt, 1),
SEQUENCE(_NIter),
LAMBDA(Stack,J,
IF(ROUND(INDEX(Stack, 3), _numDec) = 0,
//If the Recalculated NPV is zero, we have reached solution and hence retain that solution
Stack,
//If not we need to recalibrate the rate once more and recalculate the NPV
LET(//In this section we extract each element of the stack that is used in Reduce
PrevNPV, INDEX(Stack, 1),
PrevRt, INDEX(Stack, 2),
CurrentNPV, INDEX(Stack, 3),
CurrentRt, INDEX(Stack, 4),
//Recalibrate the rate using the slope with the idea to take NPV to zero
NewRt, CurrentRt + CurrentNPV * (CurrentRt - PrevRt) / (PrevNPV - CurrentNPV),
NewNPV, PRONPV(NewRt, CF, Dts),
//Replace the old results with the new results in the initial stack
VSTACK(CurrentNPV, CurrentRt, NewNPV, NewRt, J)
)
)
)
),
//The final NPV is the third value of the stack and final discount rate used is the fourth value
//If the final NPV is zero then the final rate is the solution, else we do not have a solution
Return, IF(ROUND(INDEX(ReturnStack, 3), _numDec) = 0,
INDEX(ReturnStack, 4),
SQRT(-1)),
Return));