Net present value of a column of monthly cash flows at an annual discount rate. Each flow is taken at the end of its month, so the k-th flow is divided by (1 + i)^(k/12), where i is the annual rate. The monthly rate (1 + i)^(1/12) − 1 is the one that compounds to i over twelve months, so a flow twelve months out is discounted by exactly one year. Any currency works; the result is in the same currency.
Spreads a capital total over periods in proportion to a phasing profile. Each period gets total × weight / sum of weights, so the profile can be written as percentages, fractions or any relative weights, and the spread always adds back to the total. The result has the shape of the profile: a column profile gives a column of spend by period, ready to subtract from a monthly cash flow. A zero weight leaves that period without spend.
The date on which a production forecast first falls to the economic limit: the cessation-of-production (COP) date that ends the reserves and the cash flow. The forecast can come from any decline model or simulation, as a column of dates and a column of rates. Between the last date above the limit and the first date at or below it, the date is interpolated on the logarithm of the rate, which is exact for an exponential decline.
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.
The month in which cumulative cash flow climbs back to zero after its low point: the undiscounted payout. The cumulative cash flow is found with SCAN. Its low point is the trough, where the capital outlay is deepest; payout is the first month after the trough in which the cumulative is zero or more. Counting from the trough, not from the start, means early revenue before a later capital phase does not count as payout. The LAMBDA returns the month's position in the column (the first month is 1), and the text "none" when the cumulative never climbs back to zero.
The period-by-period split of a production sharing contract (PSC). From the gross revenue the government first takes its royalty. The contractor then recovers its costs as cost oil, up to a cost-recovery cap; costs above the cap carry forward to later periods. What remains is profit oil, shared between the government and the contractor on a sliding scale set either by the contractor's R-factor or by production tranches. The contractor pays income tax on its share of the profit oil. The function returns one of these quantities as a column, one row per period: cost oil, profit oil, either party's profit oil, the contractor's tax, the government take, the contractor's net cash flow, or the government's cumulative share of the profit.
This website uses cookies to enhance your experience and analyze site usage.
By clicking "Accept", you consent to the use of cookies for analytics purposes.
Read our privacy policy