PO.ECON.PSC.Split


Description

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.

Formula

=LAMBDA(Mode, Gross, Price, Cost, Royalty, CostCap, Tax, Limits, Shares, Output, LET( rev, Gross*Price, roy, Royalty*rev, net, rev-roy, capv, CostCap*net, cumc, SCAN(0, Cost, LAMBDA(run_c, val_c, run_c+val_c)), cumcap, SCAN(0, capv, LAMBDA(run_k, val_k, run_k+val_k)), rec, cumcap+SCAN(0, cumc-cumcap, LAMBDA(low_r, val_r, MIN(low_r, val_r))), recprev, cumcap-capv+SCAN(0, cumc-Cost-cumcap+capv, LAMBDA(low_p, val_p, MIN(low_p, val_p))), costoil, rec-recprev, profit, net-costoil, tier, SCAN(0, Shares, LAMBDA(run_t, val_t, run_t+1)), ds, Shares-SCAN(0, tier, LAMBDA(run_d, tier_d, SUM((tier=tier_d-1)*Shares))), ix, SCAN(0, Gross, LAMBDA(run_i, val_i, run_i+1)), tranche, SCAN(0, ix, LAMBDA(run_q, per_q, LET( g, SUM((ix=per_q)*Gross), IF(g>0, SUM((g-Limits>0)*(g-Limits)*ds)/g, 0)))), receipts, SCAN(0, ix, LAMBDA(run_x, per_x, LET( cprev, SUM((ix<per_x)*Cost), rf, IF(cprev>0, run_x/cprev, 0), run_x+SUM((ix=per_x)*(costoil+SUM(ds*(rf>=Limits))*profit))))), back, SCAN(0, ix, LAMBDA(run_b, per_b, SUM((ix=per_b-1)*receipts))), cpo, CHOOSE(Mode, receipts-back-costoil, tranche*profit), gpo, profit-cpo, tx, Tax*cpo, govtake, roy+gpo+tx, ncf, costoil+cpo-tx-Cost, share, SCAN(0, govtake, LAMBDA(run_g, val_g, run_g+val_g))/SCAN(0, rev-Cost, LAMBDA(run_v, val_v, run_v+val_v)), CHOOSE(Output, costoil, profit, cpo, gpo, tx, govtake, ncf, share)))

Parameters

Parameter Unit Description
Mode — 1 profit split by R-factor; 2 profit split by production tranche
Gross bbl Gross production in each period, one column of at least two periods
Price currency/bbl Oil price: one value, or one per period
Cost currency Recoverable costs (capital and operating) incurred in each period, same rows as Gross
Royalty fraction Royalty share of the gross revenue
CostCap fraction Cost-recovery cap, as a share of the revenue after royalty
Tax fraction Income tax rate on the contractor's profit oil
Limits — Lower bound of each tier, one column of at least two tiers in ascending order starting at 0: R-factor values in Mode 1, production per period (bbl) in Mode 2. For a flat split give two tiers with the same share
Shares fraction Contractor's share of the profit oil in each tier, same rows as Limits
Output — 1 cost oil; 2 profit oil; 3 contractor profit oil; 4 government profit oil; 5 contractor income tax; 6 government take (royalty + government profit oil + tax); 7 contractor net cash flow (cost oil + profit oil − tax − costs); 8 government take as a share of the cumulative profit (revenue − costs) to date

Examples

Invocation Expected
=PO.ECON.PSC.Split(2, {1;0}, 100, {20;0}, 0.1, 0.5, 0.3, {0;1000}, {0.4;0.4}, 1) [20, 0]
=PO.ECON.PSC.Split(2, {1;0}, 100, {20;0}, 0.1, 0.5, 0.3, {0;1000}, {0.4;0.4}, 3) [28, 0]
=PO.ECON.PSC.Split(2, {1;0}, 100, {20;0}, 0.1, 0.5, 0.3, {0;1000}, {0.4;0.4}, 6) [60.4, 0]
=PO.ECON.PSC.Split(2, {1;0}, 100, {20;0}, 0.1, 0.5, 0.3, {0;1000}, {0.4;0.4}, 7) [19.6, 0]
=PO.ECON.PSC.Split(2, {1;0}, 100, {20;0}, 0.1, 0.5, 0.3, {0;1000}, {0.4;0.4}, 8) [0.755, 0.755]
=PO.ECON.PSC.Split(1, {100000;120000;90000;80000}, 70, {9000000;3000000;1000000;1000000}, 0.1, 0.6, 0.3, {0;1;1.5}, {0.5;0.35;0.2}, 1) [3780000, 4536000, 3402000, 2282000]
=PO.ECON.PSC.Split(1, {100000;120000;90000;80000}, 70, {9000000;3000000;1000000;1000000}, 0.1, 0.6, 0.3, {0;1;1.5}, {0.5;0.35;0.2}, 3) [1260000, 1512000, 1134000, 965300]
=PO.ECON.PSC.Split(1, {100000;120000;90000;80000}, 70, {9000000;3000000;1000000;1000000}, 0.1, 0.6, 0.3, {0;1;1.5}, {0.5;0.35;0.2}, 6) [2338000, 2805600, 2104200, 2642290]
=PO.ECON.PSC.Split(1, {100000;120000;90000;80000}, 70, {9000000;3000000;1000000;1000000}, 0.1, 0.6, 0.3, {0;1;1.5}, {0.5;0.35;0.2}, 8) [-1.169, 1.512823529, 0.8330804598, 0.7436157895]
=PO.ECON.PSC.Split(2, {100000;120000;90000;80000}, 70, {9000000;3000000;1000000;1000000}, 0.1, 0.6, 0.3, {0;90000;110000}, {0.5;0.35;0.2}, 3) [1222200, 1360800, 1134000, 1379000]
=PO.ECON.PSC.Split(2, {100000;120000;90000;80000}, 70, {9000000;3000000;1000000;1000000}, 0.1, 0.6, 0.3, {0;90000;110000}, {0.5;0.35;0.2}, 7) [-4364460, 2488560, 3195800, 2247300]

Need a custom LAMBDA? Suggest a New Lambda