Upstream oil and gas model: E&P
Type curves, reserves, netbacks, PV-10 and NAV per share. Build a producer from its wells: a hyperbolic type curve and its EUR, a base in decline, wells drilled from a development budget, production in boe with gas at 6 to 1, reserves rolled forward from proved developed to 2P, prices realised from a forward curve, royalties, costs and the netback, DD&A on units of production, then PV-10 and NAV per share, and read what a low or high price deck does to each.
By Surojit Chakraverti, founder of L3VLUP and an investor running a long-short healthcare and technology equities strategy.
On this subject: upstream oil and gas model: e&p.Updated 5 November 2026
Who builds it, and for whatThe model an energy analyst builds for an exploration and production company, and the one a natural resources banker or an energy private equity fund rebuilds before advising on, lending to or buying a producer. Equity research uses it to forecast production and cash flow at the strip and to set a price target on net asset value; bankers use the same reserves to size a reserve-based loan and to value an acquisition on its proved developed reserves; buyers test what a lower oil price does to the value of the wells already drilled.
Every sheet below, in your browser, with its outputs and checks
Starter workbook free with an account. Worked model: one a month with Plus, every one with Pro. Downloads
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Assumptions | |||||||||||||||||||
| 2 | $m unless stated. Production in thousand boe a day (mboe/d), reserves in million boe (mmboe). Gas converts at 6 mcf to 1 boe. | |||||||||||||||||||
| 4 | Year | Unit | FY0 | FY1 | FY2 | FY3 | FY4 | FY5 | FY6 | FY7 | FY8 | FY9 | FY10 | FY11 | FY12 | FY13 | FY14 | FY15 | Notes | |
| 5 | Year number | # | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | FY0 = 0. Drives decline, cost escalation and the end of the horizon. | |
| 7 | Scenario | |||||||||||||||||||
| 8 | Price deck | # | 1 | 1 = strip, 2 = low price, 3 = high price. Sets WTI, Henry Hub and price revisions from FY1. | ||||||||||||||||
| 10 | Benchmark prices and differentials | |||||||||||||||||||
| 11 | WTI, strip (FY0 actual) | $ per bbl | 74.00 | 68.00 | 66.00 | 65.00 | 65.00 | 66.00 | The forward curve on the valuation date, one price per year. | |||||||||||
| 12 | WTI, low-price deck | $ per bbl | 52.00 | 50.00 | 50.00 | 50.00 | 50.00 | |||||||||||||
| 13 | WTI, high-price deck | $ per bbl | 88.00 | 85.00 | 82.00 | 80.00 | 80.00 | |||||||||||||
| 14 | WTI, selected deck | $ per bbl | 74.00 | 68.00 | 66.00 | 65.00 | 65.00 | 66.00 | 66.00 | 66.00 | 66.00 | 66.00 | 66.00 | 66.00 | 66.00 | 66.00 | 66.00 | 66.00 | Held flat after FY5. | |
| 15 | Henry Hub, strip (FY0 actual) | $ per mmbtu | 2.60 | 3.60 | 3.80 | 3.90 | 3.90 | 4.00 | ||||||||||||
| 16 | Henry Hub, low-price deck | $ per mmbtu | 2.50 | 2.50 | 2.60 | 2.70 | 2.70 | |||||||||||||
| 17 | Henry Hub, high-price deck | $ per mmbtu | 4.80 | 4.80 | 4.60 | 4.50 | 4.50 | |||||||||||||
| 18 | Henry Hub, selected deck | $ per mmbtu | 2.60 | 3.60 | 3.80 | 3.90 | 3.90 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | Held flat after FY5. Taken as $ per mcf. | |
| 19 | Oil differential to WTI | $ per bbl | 3.50 | Quality and the cost of moving crude from the basin to the hub. Deducted. | ||||||||||||||||
| 20 | NGL realisation, % of WTI | % | 32.0% | Ethane, propane and butanes, sold on their own markets. | ||||||||||||||||
| 21 | Gas basis differential to Henry Hub | $ per mcf | 0.75 | Local pipeline constraints. Deducted. | ||||||||||||||||
| 23 | Type curve, base decline and drilling | |||||||||||||||||||
| 24 | Days in the year | days | 365 | |||||||||||||||||
| 25 | Type curve: first-year average rate per well | boe/d | 650 | A new horizontal well in its first twelve months online. | ||||||||||||||||
| 26 | Type curve: initial annual decline (Di) | % | 75.0% | |||||||||||||||||
| 27 | Type curve: hyperbolic exponent (b) | $ | 0.90 | Between nil (exponential) and one. The higher it is, the flatter the tail. | ||||||||||||||||
| 28 | Producing wells, FY0 average rate | mboe/d | 220 | Every well online before FY0 ends, except the wells drilled in FY0. | ||||||||||||||||
| 29 | Base decline: initial annual decline (Di) | % | 30.0% | Shallower than a new well: the base mixes wells of every age. | ||||||||||||||||
| 30 | Base decline: hyperbolic exponent (b) | $ | 0.80 | |||||||||||||||||
| 31 | Wells drilled in FY0, online in FY1 | wells | 125 | Drilled and completed; already proved developed at FY0. | ||||||||||||||||
| 32 | Development budget | $m | 1,000.0 | 1,050.0 | 1,100.0 | 1,150.0 | 1,200.0 | Drilling and completion. Wells are turned in line the year after they are drilled. | ||||||||||||
| 33 | Cost per well, FY0 | $m per well | 8.00 | Drilling and completion of a two-mile lateral. | ||||||||||||||||
| 34 | Well cost inflation, annual | % | 2.0% | |||||||||||||||||
| 35 | Oil share of production | % of boe | 52.0% | |||||||||||||||||
| 36 | NGL share of production | % of boe | 22.0% | Gas is the remainder. | ||||||||||||||||
| 37 | Gas share of production | % of boe | 26.0% | |||||||||||||||||
| 38 | Gas conversion | mcf per boe | 6 | Energy equivalence: 6,000 cubic feet of gas hold about the energy of a barrel of oil. | ||||||||||||||||
| 40 | Reserves | |||||||||||||||||||
| 41 | PUD locations booked, FY0 | wells | 560 | Proved undeveloped locations. SEC rules require each to be drilled within five years of booking. | ||||||||||||||||
| 42 | Locations added to PUD | wells | 110 | 110 | 110 | 110 | 110 | Extensions: probable locations upgraded to proved as the field is delineated. | ||||||||||||
| 43 | Probable reserves, FY0 | mmboe | 420.0 | |||||||||||||||||
| 44 | Discoveries added to probable reserves | mmboe | 25.0 | 25.0 | 25.0 | 25.0 | 25.0 | |||||||||||||
| 45 | Price revisions, % of opening PD reserves, strip | % a year | 0.5% | |||||||||||||||||
| 46 | Price revisions, % of opening PD reserves, low price | % a year | -4.0% | Lower prices bring forward the date a producing well stops paying its way. | ||||||||||||||||
| 47 | Price revisions, % of opening PD reserves, high price | % a year | 1.5% | |||||||||||||||||
| 49 | Royalties and operating costs | |||||||||||||||||||
| 50 | Royalty, % of revenue | % | 19.0% | Paid to mineral owners on the gross value of production. | ||||||||||||||||
| 51 | Production and severance taxes, % of revenue after royalties | % | 6.5% | |||||||||||||||||
| 52 | Lease operating expense, FY0 | $ per boe | 8.50 | Lifting, water handling, workovers and field staff. | ||||||||||||||||
| 53 | Lease operating expense inflation, annual | % | 2.5% | |||||||||||||||||
| 54 | Gathering, processing and transport, FY0 | $ per boe | 3.80 | |||||||||||||||||
| 55 | Transport cost inflation, annual | % | 2.0% | |||||||||||||||||
| 56 | General and administrative, FY0 | $m | 160.0 | Cash G&A, fixed. | ||||||||||||||||
| 57 | G&A inflation, annual | % | 3.0% | |||||||||||||||||
| 59 | Depreciation, tax and financing | |||||||||||||||||||
| 60 | Net capitalised cost of oil and gas properties, FY0 | $m | 6,000.0 | All of it on proved developed reserves: undeveloped locations have not yet been spent on. | ||||||||||||||||
| 61 | Tax rate | % | 22.0% | The tax basis is depleted on units of production, as the book cost is. | ||||||||||||||||
| 62 | Net debt, FY0 | $m | 2,800.0 | |||||||||||||||||
| 63 | Interest rate on net debt | % | 6.0% | On the opening balance. | ||||||||||||||||
| 64 | Shares outstanding | m shares | 250.00 | |||||||||||||||||
| 66 | Valuation | |||||||||||||||||||
| 67 | Reserves discount rate (PV-10) | % | 10.0% | The SEC’s fixed 10%, for comparison between companies. | ||||||||||||||||
| 68 | Discount rate for the NAV (WACC) | % | 9.0% | |||||||||||||||||
| 69 | Plugging and abandonment of the PD wells, end of FY15 | $m | 450.0 | Paid when the wells stop producing. No tax relief is taken. | ||||||||||||||||
Select a cell to see its row, its schedule and the type of cell. Font colours: blue for inputs, black for formulas, green for links to other sheets. Scroll sideways to see every period.
Download
Upstream oil and gas model: E&P: the workbook
An Excel workbook with working formulas, no macros and no external links: the same model you can work with on this page.
- Browse without an accountPreview every model online, sheet by sheet, with its outputs and checks.
- Free accountWork with every model online: change the inputs, switch the case, see every formula. And every starter workbook, with its key schedules left blank to build.
- L3VLUP Plus$4.99/monthEverything in Free, and one worked model each billing month as an Excel file: every schedule built, formulas live, checks at zero, yours to keep.
- L3VLUP Pro$25/monthEverything in Plus, every worked model in the library, and the Pro applications.
- The worked model
- 9 sheets and 1,020 live formulas, all 6 schedules built and linked, every check reading zero. Change an input in Excel and the whole model moves.
- The starter
- The same inputs and layout with the oil and gas reserves roll-forward left blank for you to build, each blank cell marked. The worked model is its answer.
What the base case says
- NAV per share ($)
- 10.46
- PV-10 of proved developed reserves
- $5,669.4m
- After-tax value at the WACC
- $5,415.4m
- Production, FY5 (mboe/d)
- 290
- Production, FY1 (mboe/d)
- 249
- EUR per well (thousand boe)
- 868
- 1P reserves, FY0 (mmboe)
- 986
- Reserve life, 1P, FY5 (years)
- 9.30
- Reserve replacement, 1P, FY1
- 107.6%
- Field netback per boe, FY1 ($)
- 19.81
- EBITDAX, FY1
- $1,638.0m
- Recycle ratio, FY1
- 2.1x
- DD&A rate, FY1 ($ per boe)
- 11.53
- Free cash flow, FY1
- $377.5m
- Net debt / EBITDAX, FY5
- 0.7x
Read from the workbook as served, every input at its default. Periods: FY0, FY1, FY2, FY3, FY4, FY5, FY6, FY7, FY8, FY9, FY10, FY11, FY12, FY13, FY14, FY15. The figures are invented and move with whatever you type in.
What this model is
An invented onshore exploration and production company: a five-year corporate forecast (FY1 to FY5, from an FY0 base year) and a fifteen-year run-off of the wells it has already drilled.
Production is built from wells. A type curve gives one new well’s rate in each year online on an Arps hyperbolic decline; the wells already producing decline on their own, shallower curve; the development budget buys wells at a cost per well, and each comes online the year after it is drilled.
Reserves are rolled forward: proved developed reserves fall with production and rise as drilling converts undeveloped locations, extensions book new locations, and price revisions move what the producing wells will recover. Probable reserves take 1P to 2P.
Prices come from a forward curve for WTI and Henry Hub less differentials. Royalties, production taxes, lease operating expense and transport give the field netback; G&A gives EBITDAX; DD&A on units of production, tax and interest give free cash flow and net debt.
The proved developed reserves are valued as an asset: PV-10 before tax, after-tax value at 10% and at the WACC, less net debt, per share. Three price decks (strip, low and high) and a board-style table of NAV per share by WTI and discount rate.
Seats: Equity research and hedge funds, Investment banking, Private equity.
Careers that do this work: infrastructure & real assets, debt capital markets, private credit.
How the schedules connect
Every row in the workbook is tagged with the schedule it belongs to; the inspector above shows the tag when you click a row. These are the schedules this model is made of and where each one lives.
Realised prices and the field netback
Benchmarks, differentials, royalties, costs per boe
Assumptions, rows 11–21 · Assumptions, rows 50–57 · Operations, rows 6–9 · Operations, rows 12–17 · Operations, rows 20–31
Type curves and production decline
Arps decline, EUR, the base and wells drilled
Assumptions, rows 24–38 · Production, rows 6–9 · Production, rows 12–16 · Production, rows 19–25 · Production, rows 28–34
Oil and gas reserves roll-forward
PD, PUD, 1P and 2P from one year to the next
Assumptions, rows 41–47 · Reserves, rows 6–11 · Reserves, rows 14–24 · Reserves, rows 27–33 · Reserves, rows 36–41 · Reserves, rows 44–47
Units-of-production DD&A
A cost pool over reserves, times production
Assumptions, rows 60–64 · Cash Flow, rows 6–11 · Cash Flow, rows 31–32
PV-10 and upstream NAV
Proved developed reserves valued, net debt, per share
Assumptions, rows 67–69 · NAV, rows 6–10 · NAV, rows 13–23 · NAV, rows 26–33
Sensitivity tables
Two assumptions at once, without a data table
Sensitivity, rows 5–6 · Sensitivity, rows 9–14 · Sensitivity, rows 17–24
What you should be able to explain
- Why a producer has to keep drilling to stand still: every well declines, steeply at first, and the base falls by a quarter or more a year without new wells.
- How a type curve works: a first-year rate, an initial decline and a hyperbolic exponent, and the estimated ultimate recovery (EUR) the curve adds up to.
- What proved developed, proved undeveloped, 1P and 2P reserves are, and how drilling converts undeveloped reserves into developed ones without adding to proved reserves.
- Why gas is converted to barrels at 6 mcf to 1 on energy, and why a boe of gas is still worth far less than a barrel of oil.
- How the field netback is built from realised prices, royalties, production taxes, lease operating expense and transport, and why the recycle ratio compares it with the cost of replacing a barrel.
- What PV-10 is, why it is pre-tax and at a fixed 10%, and how an asset NAV after tax at a cost of capital, less net debt, gives a value per share.
What a reviewer looks for
- Production grown at a rate rather than built from the decline of existing wells and the wells drilled, so the capital needed just to hold production flat never appears.
- Exponential decline on shale wells, which understates the long tail and so the EUR and the reserves.
- Gas converted at 6 to 1 for revenue, so a boe of gas is priced like a barrel of oil.
- DD&A at a flat rate on the cost base rather than on units of production, so it does not follow output or the reserves the cost was spent to find.
- A NAV of proved developed reserves that keeps G&A, undeveloped locations or the development spending still needed to produce them, or that forgets plugging and abandonment.
Conventions this workbook uses
Stated on the cover sheet too. A model is only as trustworthy as the decisions it tells you it made.
- Annual periods. FY0 is the base year; the price decks differ from FY1, so FY0 is the same in every deck. WTI and Henry Hub are held flat after FY5.
- Gas converts to barrels of oil equivalent at 6 mcf to 1 boe, an energy equivalence. Henry Hub is quoted per mmbtu and taken as $ per mcf.
- Type curve: rate in year online t = q1 x (1 + b x Di x (t - 1))^(-1/b), the Arps hyperbolic decline applied to whole years, with q1 the first-year average rate. With b at nil it is an exponential decline. A well produces for fifteen years, and its EUR is the sum of those years.
- A well drilled in a year is turned in line at the start of the next. Wells drilled = development spending / cost per well, and need not be whole.
- Production, revenue and reserves are on a working-interest basis, before royalties; royalties are a cost. Production taxes are charged on revenue after royalties.
- Proved developed reserves at FY0 are the producing forecast summed over the horizon. Drilling converts wells x EUR from PUD to PD; PUD reserves are always locations x EUR. Price revisions apply to PD reserves only.
- DD&A on units of production, successful-efforts style: (opening net capitalised cost + development spending in the year) / (opening PD reserves + PD converted in the year) x production. No exploration expense or impairment is modelled.
- Interest is charged on opening net debt and free cash flow repays it. Tax is charged on profit before tax, never below nil, and there is no working capital.
- The NAV values the wells drilled by the end of FY0 only, before G&A, with year-end discounting. Tax on it depletes the FY0 cost pool over the PD run-off; abandonment is paid at the end of FY15 without tax relief.
- The sensitivity table moves the whole WTI deck in parallel and recomputes tax in every year; its centre equals the model.
What this model does not cover
Where its answer stops, so you know what to add before relying on it.
- Individual fields, basins or wells. One type curve stands for every new well and one decline for the producing base.
- Hedging, which most producers use to lock in part of next year’s price.
- Proved undeveloped and probable reserves in the NAV, and the corporate G&A, which a full NAV would value separately.
- Exploration expense, dry holes and impairments under successful efforts, and the full cost ceiling test.
- Midstream ownership, international production sharing contracts and fiscal regimes other than a royalty and a production tax.
- A real company. ConocoPhillips is the candidate for a case built from its filings; this model uses invented figures.
Build it yourself
The starter workbook
The Reserves sheet has been cleared: proved developed reserves, PUD locations and reserves, 1P, probable and 2P, reserve life and the reserve replacement ratio. Rebuild it from the Assumptions and Production sheets, then watch the DD&A rate, the development cost per boe and the recycle ratio come back.
Blanks: Oil and gas reserves roll-forward. Download it beside the worked model.
The path around this model
Understand it, drill it, read the build, then apply it to a real company.
Build · Lab · ~15 min
DCF Builder
Build a DCF by hand from revenue to a share price, know how much of it sits in the terminal value, and name the one assumption the answer hinges on.
Build · Lab · ~12 min
WACC Builder
Build a discount rate you can defend line by line: beta, equity risk premium and cost of debt, each from a source you can name.
Read · Guide · 7 min
Equity Research Interview Questions: The Coverage Mindset
Read · Guide · 16 min
How to Build a DCF Model: From Operating Assumptions to a Defensible Valuation
Read · Guide · 10 min
Scenario and Sensitivity Architecture: Building a Model That Can Be Stress-Tested
Apply · Skill
Company Profile
Build a banker-grade one-pager on any company, fast.
Apply · Skill
Model Audit
Find the errors in a financial model before someone senior does.
Vocabulary: Barrel of Oil Equivalent (boe), Proved Reserves (1P and 2P), PV-10, Recycle Ratio, Depreciation & Amortisation (D&A), Net Debt, WACC, Free Cash Flow (FCF).
Questions about this model
How is production built?
From wells. A new well averages 650 boe a day in its first year and declines on an Arps hyperbolic curve with an initial decline of 75% and an exponent of 0.9, so it makes 366 boe a day in its second year and 48 in its fifteenth; its EUR over fifteen years is 867,500 boe. The wells producing at FY0 average 220,000 boe a day and decline more gently. A budget of $1,000 million in FY1 buys 122.5 wells at $8.2 million each, online the year after. Production reaches 249,400 boe a day in FY1 and 289,900 in FY5.
What is PV-10 and what is the NAV here?
PV-10 discounts the pre-tax cash flows of the proved developed reserves, 500.7 million boe at FY0, at 10%: $5,669 million at the strip, after royalties, production taxes, field costs and $450 million of abandonment in FY15. After tax, at a 9% WACC, the same reserves are worth $5,415 million; less $2,800 million of net debt that is $2,615 million, or $10.46 a share on 250 million shares.
What does the price deck do?
At the strip WTI runs from $68 to $66 a barrel. On the low-price deck, $50 a barrel and Henry Hub at $2.50 to $2.70, the FY1 netback falls from $19.81 to $11.35 a boe, the recycle ratio from 2.1 to 1.2, free cash flow turns negative at the same budget and net debt reaches 6.5 times EBITDAX by FY5; NAV falls to $1.10 a share. On the high-price deck it is $19.13.
What is the recycle ratio?
The field netback per boe divided by what it costs to develop a boe. In FY1 a well costs $8.2 million and recovers 867,500 boe, $9.41 a boe; the netback is $19.81, so the recycle ratio is 2.1: each barrel sold pays for replacing itself about twice over.
Related models
Infrastructure and project finance · Advanced
Renewables project finance model
Solar or wind: P50 yield, PPA, merchant tail, debt sizing
Core modelling · Core
Discounted cash flow model
Unlevered free cash flow to a share price
Infrastructure and project finance · Advanced
Project finance model
Sculpted debt, reserves, lock-up, coverage ratios
Questions about this model
Discussions open here soon.