Free Excel templates

A free borrowing base calculator in Excel, run live off a loan tape

Eligibility criteria, concentration limits by obligor, broker, jurisdiction and tenor, the advance rate, reserves and headroom, every line computed from 1,880 contracts, plus the advance rate set from a stress analysis: eight sheets, no macros.

The file runs a full borrowing base certificate for a fictional equipment finance facility. A gross pool of €262.6 million across 1,880 contracts loses €45.3 million to the eligibility criteria and €14.2 million to the concentration limits, leaving an eligible pool of €203.2 million. At an 88% advance rate that gives €178.8 million of gross availability; less €7.7 million of reserves, €171.1 million of net availability against €168.0 million drawn. The borrower is compliant with €3.1 million of headroom, and the effective advance rate against the gross pool is 65.1%, not 88%.

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.

Borrowing_Base_Calculator.xlsx (182 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 Asset-Based Finance Handbook. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

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).

Get the Library, $79 →

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

Eight sheets. Inputs are blue cells on the Criteria and Advance rate sheets; every deduction on the certificate is computed from the tape.

Read me
What the workbook computes, where each limit comes from, and how to read the certificate.
Loan tape
1,880 synthetic contracts with the fields a real equipment tape carries: obligor, product, introducer, channel, jurisdiction, balance, term, days past due, deposit and security status.
Criteria
The eligibility criteria, the concentration limits, the advance rate and the reserves, all editable.
Borrowing base
The certificate line by line: gross pool, exclusions by cause, eligible pool, each concentration excess, advance rate, reserves, net availability, outstanding advances and headroom, with a compliance status.
Obligors and Brokers
The subtotals the single-obligor, single-broker and jurisdiction limits need, because a limit on an obligor cannot be tested contract by contract.
Advance rate
The advance rate set from the stress: base case loss times a multiple plus a margin, the enhancement it implies, the break-even loss multiple, what the borrower's request would have cost, and a step-down with the deficiency it creates.
Checks
Every figure the book prints, reproduced within its printed precision.

What the worked case returns

MeasureValue
Gross pool balance€262,646,900
Failing eligibility (all causes)€45,273,500 (17.2%)
Concentration excesses€14,179,809
Eligible pool balance€203,193,591
Advance rate88%
Gross availability€178,810,360
Reserves (servicing transition, commingling)€7,700,000
Net availability€171,110,360
Outstanding advances€168,000,000
Headroom€3,110,360
Effective advance rate on the gross pool65.1%

The advance rate on the Advance rate sheet is set from a stress, not from the market: a 2.4% base case cumulative loss times 2.5, plus a 1.5% margin for servicing and data risk, gives a 7.5% floor for the enhancement. At 88% the enhancement is 12%, five times the base case loss. At the 92% the borrower asked for it would have been 8%, or 3.3 times. A three-point step-down to 85% then creates a deficiency of about €5.7 million on the same drawn amount.

How to use it on your own facility

  1. Paste your own tape over the Loan tape sheet, keeping the column order, or map your fields to it.
  2. Set your facility's eligibility criteria and concentration limits on Criteria.
  3. Read the Borrowing base sheet from the top: the exclusions by cause overlap, so they explain the total but do not sum to it.
  4. Set the advance rate on Advance rate from your own base case loss and the multiple you want it to survive.
  5. Watch the headroom line: a negative figure is a deficiency to be cured within the agreed number of business days.

Questions people ask about it

Is this borrowing base calculator 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. Every exclusion is a SUMIFS over the tape, and the concentration limits are tested on subtotals built on helper sheets.

Why is the effective advance rate 65%, not 88%?

Because the 88% applies only to what survives eligibility and the concentration limits. On this pool 22.6% of the gross balance is excluded before the advance rate is applied, and the reserves come off after it.

Can I use it for a real facility?

You can reuse the structure. The tape is synthetic, the facility is fictional, and nothing here is investment or credit advice.

The rest of the files

This template is one of the companion workbooks for The Asset-Based Finance Handbook: the others take one transaction end to end, design and stress the triggers, and carry segment reference sheets and diligence checklists. Free, like this one.

Open the companion files →

Other free templates

This template comes from The Asset-Based Finance Handbook. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.