A free reserve-based lending model in Excel: the oil and gas borrowing base
Reserve reports run at the bank price deck and PV9, risked by category, hedge credit, the advance rate and three redeterminations, a deficiency cured in six installments, covenants, a PDP securitisation and a restructuring: nineteen sheets, no macros.
The file runs a fictional bank's oil and gas book. Redfern's borrowing base is set three times from three reserve reports. In May 2025, at a $60 deck, risked PV9 plus hedge credit of $589 million at a 55% advance rate gives a base of $320 million. In the fall the deck falls to $55, the report is weaker and the base is cut to $235 million while $265 million is drawn: a $30 million deficiency, cured in six monthly installments of $5 million. In spring 2026, at a $62 deck, the base recovers to $335 million.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
Reserve_Based_Lending_Model.xlsx (461 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
The book behind this model
This file is one worked case from The Oil and Gas Lender. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Nineteen sheets. Blue cells on Inputs are the only inputs.
Read Me and Inputs
Prices, decks, volumes, decline rates, costs, rates and valuations.
Redfern Unit Economics
One barrel from WTI to margin before fixed costs at two decks.
Reserve Report Jan 2025, Jul 2025 and Jan 2026
Three bank runs at the deck and PV9, by category.
Borrowing Base Bridge
Risked PV9, hedge credit, advance rate, the three bases and two counterfactuals.
Deficiency and Installments
Draws, the deficiency, the six installments and the cure.
Interest, Hedges, Covenants and SEC Price vs Bank Deck
Interest and fees, the hedge book, covenant tests and the two price conventions.
PDP ABS, Thornmere, Second Lien, Haskett, Pellridge Book and Checks
A securitisation of producing reserves, a second borrower's covenant tests, second lien returns, a restructuring, the bank's book and the checks.
What the worked case returns
Measure
Value
Deck oil price: May 2025, fall 2025, spring 2026
$60, $55, $62
Risked PV9 plus hedge credit, May 2025
$589.3m
Advance rate
55%
Borrowing base: three redeterminations
$320m, $235m, $335m
Drawn at the fall redetermination
$265m
Deficiency
$30m
Cure
6 installments of $5m
PDP share of risked value, May 2025
66.9%
Hedges carry the base when prices fall. In the fall of 2025 the hedge credit doubled, from $15.8 million to $32.1 million, because the swaps were struck above a lower deck; without them the cut would have been deeper.
How to use it on your own borrower
Enter your reserve report by category, the bank deck and the discount rate on Inputs.
Apply the risking factors and the hedge credit.
Read the borrowing base on the bridge, then the deficiency if drawings exceed it.
Test the covenants at the new deck.
Questions people ask about it
Is this template really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference.
How is an RBL borrowing base calculated?
The lenders run the reserve report at their own price deck and discount rate (often PV9), risk each reserve category, add the value of hedges, and apply an advance rate. Here 55% of $589 million gives $324 million, rounded down to $320 million.
Can I use it for a real borrower?
You can reuse the structure. The bank, the borrowers and every figure are fictional, and nothing here is credit advice.
The rest of the files
This template is the companion workbook for The Oil and Gas Lender; the companion page also carries the other files that go with it. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
This template comes from The Oil and Gas Lender. The book is on Apple Books and Google Play.
If this book helped, or didn’t, a rating where you bought it is worth more than it looks: it is what the next reader goes on. The template stays free either way.