PO.ECON.IRR.Monthly


Description

Annualised internal rate of return of a column of monthly cash flows, robust to the second root that end-of-life costs create. Excel's IRR searches locally from one guess. A well program's cash flow often has two roots, because plugging costs at the end of life turn the flow negative again, and from its default guess IRR can land on the spurious one at a negative rate. This LAMBDA first evaluates NPV on a grid of monthly rates from −5 % to +45 % in steps of 0.25 %. The first grid point after NPV turns positive where NPV is no longer positive brackets the economic root. IRR, seeded at the bottom of that bracket, refines it, and linear interpolation across the step stands in if IRR strays outside it. The monthly root r is returned as the annual rate (1 + r)^12 − 1. It returns the text "n/a" when NPV never falls through zero on the grid: a program that never pays back its capital has no IRR, and neither does one whose IRR is above 45 % a month.

Formula

=LAMBDA(cash,LET( cash_col,cash, grid_step,0.0025, rates,SEQUENCE(1,201,-0.05,grid_step), periods,SEQUENCE(ROWS(cash_col)), npv_grid,MMULT(TRANSPOSE(cash_col),(1+rates)^(-periods)), first_pos,IFERROR(XMATCH(TRUE,npv_grid>0),0), crossing,IF(first_pos=0,0,IFERROR(XMATCH(1,(SEQUENCE(1,201)>first_pos)*(npv_grid<=0)),0)), IF(crossing=0,"n/a",LET( r_lo,INDEX(rates,1,crossing-1),v_lo,INDEX(npv_grid,1,crossing-1),v_hi,INDEX(npv_grid,1,crossing),r_interp,r_lo+grid_step*v_lo/(v_lo-v_hi),r_refined,IFERROR(IRR(cash_col,r_lo),r_interp),(1+IF(AND(r_refined>=r_lo,r_refined<=r_lo+grid_step),r_refined,r_interp))^12-1))))

Parameters

Parameter Unit Description
cash currency Monthly cash flows, first month first, as a column

Examples

Invocation Expected
=PO.ECON.IRR.Monthly({-1000;0;0;0;0;0;0;0;0;0;0;0;1100}) 0.1
=PO.ECON.IRR.Monthly({-1000;0;0;0;0;0;0;0;0;0;0;0;600;0;0;0;0;0;0;0;0;0;0;0;600}) 0.1306623863

Need a custom LAMBDA? Suggest a New Lambda