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] |