Companion files
Letters of Credit, Collections and Receivables Finance, Priced on One Export Order
These are the companion files of Trade Finance by Julian R. Sterling.The book on Amazon →
These are the four Excel workbooks that go with the book. Harrowgate Instruments ships 4,200,000 of process instrumentation to Meridian Process Holdings, and the operating margin before any finance cost is 306,600, being 7.3 per cent of the price. Hold the buyer’s payment period at 90 days so that the instrument is the only thing that moves, and six routes to payment cost between 178,040 and 223,268: a spread of 45,228, which is 14.8 per cent of the whole operating margin. Every figure the book prints is reproduced here by a live formula rather than a typed constant. Change the cost of goods sold, the days of credit granted, the discrepancy rate or your own cost of funds, and every dependent number moves. Each workbook ends with a Checks sheet setting the printed figure beside the computed one: 580 checks in all, every one passing, the 555 delivered with the book and 25 added for what the book names and never computes. Around the workbooks: the same four models with the inputs emptied, twelve working documents to print, forty questions that mark themselves, and three cases the book names and never takes to a number.
Free to download. No sign-up, no email address, nothing to fill in.
Eleven workbooks, the printable documents and the read-me. Each group below can also be downloaded on its own. Last revised 24 September 2026.
Optional. When a workbook on this page is corrected or a new worked case goes up, readers on the list get one email, plus a short note when a new book comes out. The downloads do not need it.
Join the reader listRun by MailerLite, off this site. How your address is used.
These carry the book’s own figures, so that every number it prints can be traced to a live formula. Each closes on a checks sheet. Download these four167 KB
Chapters 1 to 3
The order’s economics, and all six routes priced at held terms. The five-line build-up from 4,200,000 through 3,423,000 of cost of goods sold to a gross margin of 777,000, then 470,400 of selling, general and administrative cost carried by the order, and the 306,600 that is what the order is actually worth before a dollar of finance. Every route finances the cost rather than the price, so move the cost of goods sold and the financing column moves on all six rows at once.
Then the calendar, built out of its parts rather than asserted: 70 days of production, 26 in transit, 9 to presentation, and only then the buyer’s payment period. The sheet ends on the column every comparison needs and almost none carries, the days of buyer credit measured from the bill of lading, which is 90.0 on four routes, 106.0 on documents against acceptance and 110.8 on an unconfirmed credit held to maturity, because those extra days are the instrument’s own machinery and not a longer term conceded to anybody. With the terms held at 90 days the six rows run from 178,040 on open account with credit insurance to 223,268 on a non-recourse receivables purchase, and margin after finance from 128,560 down to 83,332. A seventh sheet runs the same machinery with each instrument at the terms it is naturally quoted with, where documentary collection lands at 102,902 and a sight credit at 112,089, so that the reason the table everyone builds flatters the short instruments can be read rather than asserted.
Download the workbook38 KBChapters 6, 7 and 8
The documentary credit priced in every form it is sold in, and the risk it removes set against the risk it introduces. The fee schedule prices the credit’s own charges with their minimums live, so an order small enough for the 425 examination minimum or the 1,500 confirmation minimum to bite shows it rather than hiding it inside a percentage. Three columns then carry the sight credit, the unconfirmed usance credit held to maturity and the confirmed usance credit discounted at presentation, each with its days to cash, its fee stack and its expected loss, which is the comparison the book argues is three instruments and not three variants of one.
The sheet worth the download on its own is the document risk. Over the same 136.8 days of exposure, the probability that a presentation is refused and the applicant does not waive is 3.7200 per cent, against 1.1833 per cent for the issuing bank and the country combined: 3.1 times. The workbook prints that ratio with the two expected losses locked to the cell beside it, 2,162 against 30,813, because the document risk is three times more likely and an order of magnitude less costly, and the ratio quoted alone overstates the case. Confirmation is then solved rather than argued: the issuing-bank default probability at which the 26,979 fee exactly equals the 28,166 of loss removed is 1.2172 per cent, against the 1.35 per cent the model assumes, so at these inputs confirmation is bought below its actuarial price. Each figure carries the period it is measured over on the same row, because the two are not measured over the same one. The rate then runs from 0.00 to 4.50 per cent a year, with a note on the top row saying what it does not mean: at a rate of nothing the minimum still applies and the credit is still confirmed.
Download the workbook34 KBChapters 4, 5, 9, 10 and 11
A bank discount is computed on the face and earned on the proceeds, so the quoted rate is never the cost. The same 6.90 per cent is taken to three tenors with the discount, the proceeds and the equivalent yield at each: 72,450 and 7.0211 per cent at 90 days, 96,600 and 7.0624 at 120, 144,900 and 7.1466 at 180, with the gap widening from 12.1 to 24.7 basis points. The next sheet is the one that surprises people: the exporter’s own facility at 8.4 per cent on a cost of 3,423,000 costs 71,883 over 90 days, and a discount at 6.90 per cent on a price of 4,200,000 costs 72,450. The cheaper rate is the dearer arrangement by 567, because a facility funds the cost and a discount funds the price. The break-even discount rate is solved rather than searched for: your cost of funds multiplied by your cost-to-price ratio, 81.5 per cent on this order, giving 6.8460 per cent.
Then the three routes that are not a credit. Open account and insurance prices the policy in full, the 26,040 premium, the 90.0 per cent cover and the uncovered tenth, which takes the expected loss from 34,416 to 3,442 and the total from 182,974 to 178,040, the cheapest of the six. Collections prices both the payment and the acceptance instruction and exposes the judgment inputs behind them as blue cells rather than burying them in formulas. Receivables purchase separates what is sold from what stays: commission at 0.55 per cent and discount at 7.60 per cent on one side, dilution at 2.25 per cent and 34.0 per cent on the other, and the exposures that survive the word non-recourse, which is why the fastest route to cash at 109.0 days is also the dearest at 223,268.
Download the workbook33 KBChapters 12 to 16
This one is the one to open first, and it does not reproduce the book. Its front sheet is a calculator you drive with your own order. Type in your contract value, your cost of goods sold, your overhead on the order, your production, transit and presentation days, your cost of funds and the terms you are about to offer, and it returns two figures: what one day of buyer credit costs you, and the tenor at which your margin after finance reaches zero. On the Meridian order those answers are 1,031.76 a day, being the 185,716 span of the terms table divided by the 180 days it runs over, and the 192nd day on a confirmed discounted credit. That is the argument of the book in two numbers, and they can both be computed before the quotation leaves the building.
Around the calculator sit the tables it comes from. The terms run from zero to 180 days with the instrument held still, which is the rule that makes it a stress test and not a re-pricing: 108,543 of cost at sight against 294,258 at 180 days, and margin after finance from 198,057 down to 12,342. Both break-evens are solved on a bracket that tests the root is interior, because a solver that runs out of room returns the edge of its range and an edge looks exactly like an answer: 192 days on a confirmed discounted credit and 204 on open account. The premium table gives what each length of credit must carry in price to pay for itself, seven rows from 0.74 per cent at 30 days, through 2.27 at 90, to 9.73 per cent at 360. Case B is the order actually won on 180 days at a price premium of 1.5 per cent: 63,000 collected against 125,704 of margin destroyed, leaving 36.5 per cent of the sight margin, on terms that needed 4.64 per cent and were sold for one and a half, a shortfall of 3.14 percentage points. All seven sensitivities follow, each moving one input, with the ranking by span at the foot: 185,716 for the days of credit granted, which is a line in the quotation, down to 9,583 for the discrepancy rate.
It ships filled in with the Meridian order, so the Checks sheet can prove it reproduces the book before you trust it with anything of your own. Overtype the blue cells with your own figures and those checks will fail. That is the workbook working, not breaking.
Revised 24 September 2026, with 25 checks of its own, for three passages of the book. Chapter 12 prints 26,523 as the smallest order a confirmed credit suits, against the gross margin, and leaves the operating-margin figure unquantified: it is 135,281. Chapter 13 says that with the discount for the applicant’s account 31,399 of the premium stays unpaid, “the exporter’s own financing of production and transit” among it; the financing is the same at sight and at any term, and what 120 days still need is 27,481. Chapter 15 says that sold at sight the discount rate ranks first; at sight nothing is discounted, and the exporter’s own cost of funds ranks first at 94,415.
Download the workbook65 KBThe four workbooks with every input emptied. The checks sheets count what is still missing and test what holds for any inputs.
Chapters 1 to 16
The order and the six routes, the credit and its confirmation, the cost of the money and the quote calculator, with the same formulas and every blue input emptied. Day-count conventions, tenor ladders, sensitivity axes and bisection bounds stay. Nothing below the inputs shows until every input is filled.
The book’s inputs are listed in a text file inside the archive. Type them back in and the book’s figures come back with them: 45,228 between the six routes, 1,031.76 a day, the 192nd day.
Download the blank set130 KBTwelve pages to print and use. A4, with margins wide enough for US Letter.
Chapters 1 to 16 · Appendices A and B
The order on one page, with the five lines and the calendar. Six routes at one term, with the identity column. What each route leaves you holding, and the conditions that decide an insurance claim. The collection instruction, refusal branch included. Reading the credit the day it is advised. A refusal advice in the first hour. The confirmation break-even. A discount quote converted and compared with your own facility. A receivables purchase before you sign. The terms page, with the premium and the shortfall note. Which input moves the answer, on your order. And the thirty, sixty and ninety days before and after you quote.
Download the PDF31 KBA workbook that marks itself. Answer in the yellow cells; the marking fills in as you go.
All parts of the book
Seven on the order and the six routes, six on open account and collections, seven on the credit and its confirmation, seven on the cost of the money, seven on terms as a price, and six on the three cases below. Numbers are marked to a tolerance, multiple choice to the letter, and the score sheet counts your errors part by part: that is your revision list.
Nothing is hidden. The marking sheet holds every answer, every tolerance, and the reason for each answer.
Download the question book17 KBNew material, not a restatement. Three situations the chapters name and never take to a number. Each case is one workbook: the note is the first sheet, the model is the rest, and a checks sheet closes it. Type your own figures in and every finding recomputes.
Chapters 13 and 14 · Case one
A credit available at sight with the usance discount for the applicant’s account, on the book’s own figures and nothing added. At 120 days the term then needs 27,481 of premium, 0.65 per cent of price, not the 31,399 the book leaves unpaid. On Case B’s 180 days it needs 0.98 per cent: the 1.5 per cent the sales director obtained would have left 219,427, more than the sight sale, against the 72,354 booked. The buyer pays 7,285 less as well.
Chapters 4, 10 and 11 · Case two
The omission the book declares most often, priced. Per dollar of facility the confirmed discounted credit earns 9.47 per cent a year, insured open account 7.27. If headroom is worth more than 3.55 per cent a year, the route the book ranks third beats the cheapest; the receivables purchase beats the unconfirmed credit above 1.94 per cent. With a facility of 12 million and ten orders a year (ADDED), the confirmed credit earns 167,517 more a year.
Chapter 7 · Case three
The collection branch Chapter 7 names and the model never prices: a refusal by the confirming bank that cannot be cured costs 30,756 more on the order, and cash arrives on day 206.8. If one refused presentation in about 180 cannot be cured, the confirmed credit loses its third place; at 19.8 per cent it costs more than the unconfirmed credit it replaced. The book’s own one per cent waiver concession makes the unconfirmed credit the dearest route once it passes 0.739 per cent.
Download the three cases41 KB| Blue text | an input: change it and everything recomputes |
| Yellow fill | an assumption that decides the answer rather than merely feeding it. In the fourth workbook every input cell carries the fill, because on an order that has not been quoted yet every one of them is an assumption somebody is making |
| Black text | a formula: do not overtype these |
| Grey text | a note |
| Green text | on the Checks sheet, a link to the computed cell |
| ADDED | a figure the book does not give, used in the cases and marked wherever it appears |
| Checks sheet | the printed figure beside the computed one, the difference, a PASS or a FAIL, and the tolerance being applied |
There are no macros, no external links, no protection and no circular references anywhere, and nothing is locked or watermarked. The files behave identically in Excel, LibreOffice and Google Sheets.
A workbook that agrees with a book proves nothing on its own, since the author wrote both. What the Checks sheets do is different: they force the model to reproduce a number that was printed before the model existed, from a formula rather than from the number itself. 580 checks across the four files, being 169, 97, 95 and 219, all passing: the 555 delivered with the book, and 25 added to the fourth workbook on 24 September 2026.
On this book the discipline earns its keep on the identity that catches a discount subtracted twice. Six of the checks in the first workbook do nothing but assert that on every route, margin after finance equals 306,600 less the total cost. A discount is easy to charge once in the fee column and again in the financing column, and the row still looks plausible because both entries belong there; the identity is what refuses it.
The second thing the checks enforce is that the figures which look like each other’s complements are not. The 28,166 of expected loss that confirmation removes is measured over the 125 days a confirmation runs on 90-day terms. The 30,813 attributed to bank and country is measured over the 136.8-day exposure of an unconfirmed credit. Subtracting the confirmed row’s 1,652 from 30,813 gives 29,161, which is a third number and belongs to neither. Every one of those figures carries its period on the same row, and a check holds it there.
Where an input is a judgment rather than a measurement, it is a blue cell and it is named as one. The five behind the collection and purchase routes are judgments, and no sensitivity table in the book stresses them, which is a gap and is printed as one. The assumption most worth arguing with is the discrepancy rate of 62.0 per cent on first presentation, which sits at the severe end of the range a documentary desk would recognize. Type 30 instead, or 80, and watch what moves and what does not: the span of that whole table is 9,583, the smallest of the seven, and the reason why is the point of the chapter.
Trade Finance develops the complete framework behind these models, with the assumptions, trade-offs and worked examples tied together.
View the book on Amazon Read the related analysisThe workbooks open in Microsoft Excel, LibreOffice Calc, Google Sheets and Numbers; the PDF prints on A4 or US Letter. They use no macros and no add-ins, so nothing needs to be enabled or trusted. If your spreadsheet asks to update links on opening, decline, because there are none.
| 24 Sep 2026 | The four workbooks recalculated: the 555 checks pass and nothing in them was wrong. Added to the fourth, with 25 checks: the operating-margin threshold Chapter 12 leaves unquantified (135,281); the premium a term still needs with the discount for the applicant’s account, 27,481 at 120 days and not the 31,399 of Chapter 13, none of it financing; the spans at sight, where the cost of funds ranks first and the discount drops out, not the reverse as Chapter 15 says. Added: a blank set, twelve working documents, forty questions that mark themselves, and three cases. |
The other books with companion files. The full list of titles is on the author page.
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 workbook stays free either way.