Free Excel templates

A free ship finance model in Excel: a loan, a lease and the fund that owns them

Charter margins and cash breakeven, a term loan on six bulk carriers with covenant tests, a cure and release prices, a bareboat sale-leaseback with purchase options, a default with recovery and recycling, and the fund's cash flows and waterfall: thirteen sheets, no macros.

The file follows a fictional $600 million shipping credit fund through three deals. Sorrel borrows $120 million against six bulk carriers; the first loan-to-value test fails and a $4.44 million prepayment cures it to 70%. When one ship is sold for $25.8 million, the 115% release price sends $14.09 million to the fund. Ketterly sells a vessel to the fund and charters it back bareboat at $11,786 a day; an early purchase option lifts the fund's IRR from 9.64% to 10.08%. Then Dunmore defaults. By the valuation date the fund has called $556.0 million, distributed $265.4 million and holds $397.2 million of NAV, with 12.5% carry accrued.

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.

Ship_Finance_Model.xlsx (82 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 Ship Finance Professional. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Apple Books → Or on Google Play

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: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Thirteen sheets. Blue cells on the Inputs sheet are the only inputs.

Read Me and Inputs
Fund terms, the three deals and the other loans, every rate, date and value.
Charter and Breakeven
A time charter margin against operating costs, the bareboat hire, and a cash breakeven per ship per day.
Sorrel Schedule, Covenant Tests and Release Price
A quarterly loan schedule with the cure prepayment, loan-to-value at every test date with a stress, and the release price when a ship is sold.
Ketterly Lease and Options and Exit
The bareboat hire from the lease rate, the balance against value, and the purchase option exercised against the full-term IRR.
Dunmore Default and Recovery and Recycling
The default, the recovery on the claim and the recycling of the ship.
Fund Cash Flows and Waterfall
Calls, fees, expenses, interest and distributions by date, then the preferred return, the catch-up and the carry accrued at NAV.
Checks
Every figure printed in the book against the formulas: 166 lines, all OK.

What the worked case returns

MeasureValue
Sorrel loan$120m on six ships
Cure prepayment after the first test$4.44m (LTV back to 70%)
Release price on one ship sold for $25.8m$14.09m (115%)
Cash breakeven per ship per day$15,571
Ketterly bareboat hire$11,786 a day
Ketterly IRR, option exercised against full term10.08% against 9.64%
Fund: capital called$556.0m
Fund: distributed$265.4m
Fund: net asset value$397.2m
Carry accrued at NAV$13.3m (12.5% of profit)

The release price is the clause to read first. Allocated by drawdown value, the ship carries $12.25 million of the loan; the 115% release price makes the borrower repay $14.09 million out of a $25.8 million sale, so the loan on the remaining five ships falls by more than the sold ship's share of it.

How to use it on your own deal

  1. Enter your loan, lease or fund terms on Inputs: amounts, dates, rates, values and covenants.
  2. Read the covenant tests at every date, then stress the values to see how far they can fall.
  3. Price the release on any ship sale before agreeing the percentage.
  4. For a lease, compare the option-exercise IRR with the full-term IRR.
  5. Use the fund sheets to see what each deal does to calls, distributions and carry.

Questions people ask about it

Is this ship finance model 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. Schedules run on dates, and IRRs are XIRR over dated cash flows.

What is a release price in a ship loan?

The amount the borrower must repay to release one vessel from the mortgage when it is sold, usually the loan allocated to that ship times a percentage above 100%. Here it is 115%.

Can I use it for a real deal?

You can reuse the structure. The fund, the borrowers and every figure are fictional, and nothing here is investment advice.

The rest of the files

This template is the companion workbook for The Ship Finance Professional; the companion page also carries the toolkit of working documents that goes with it. Free, like this one.

Open the companion files →

Other free templates

This template comes from The Ship Finance Professional. 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.