Free Excel templates

A free loan pricing and return on capital calculator in Excel

Two real estate loans priced the way a lender's credit committee should read them: margin and fees, cost of funds, expected loss and operating cost to net spread, then return on the capital each one ties up, the rate floor and early repayment.

Loan B carries a margin of 550 basis points against 200 for Loan A, 2.75 times as much. On return on capital it earns 36.5% against 33.1%, and the lead disappears if its probability of default is 1.45 times the estimate.

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.

Loan_Pricing_Return_on_Capital.xlsx (24 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 model goes with The Real Estate Debt Investor, which explains the method behind it, 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: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

6 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.

The Two Loans
The chapter's example, given figures. Every input is here.
Margin vs Return on Capital
The four adjustments, line by line, side by side
Where B Breaks
The PD and the operating cost at which the wide margin stops paying
The Floor
What the clause conceded at 11pm is worth
Early Repayment
The loan, the redeployment, and what protection actually buys
Checks
Every figure in Appendix G, recomputed and compared

What the worked case returns

MeasureValue
Loan A margin (bps)200
Loan B margin (bps)550
Loan A net spread (bps)132.5
Loan B net spread (bps)438.3
Loan A capital held2,000,000
Loan B capital held6,000,000
Loan A return on capital33.1%
Loan B return on capital36.5%
PD at which B ties with A4.36%
Rate floor worth, reference rate down 150 bps750,000
Loan A repaid after 1.5 years: return on capital38.4%

An early repayment is better for the lender on the loan itself, because the arrangement fee is earned over a shorter life. The cost is in the redeployment: the spread at which the money is lent again.

How to use it on your own case

  1. Enter each loan's amount, term, margin, fees, PD, LGD, risk weight and costs on The Two Loans.
  2. Enter the reference rate, the funding spread and the capital ratio.
  3. Read net spread and return on capital side by side, then where the wider loan stops paying.
  4. Price the rate floor and test early repayment with and without prepayment protection.

Questions people ask about it

Why is margin a poor way to compare loans?

Because it ignores expected loss, operating cost and the capital each loan ties up. A riskier loan with three times the margin can earn only slightly more on capital.

How is return on capital computed?

Profit a year (margin and annualised fees, less cost of funds, expected loss and operating cost) divided by the capital held against the loan's risk-weighted amount.

What is a rate floor worth to a lender?

If the reference rate falls below the floor, the borrower keeps paying as if it had not. The file prices that for each fall in rates.

Is this template really free?

Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Go further

The free workbooks of The Real Estate Debt Investor go further: the full companion set, blank models and the worked cases of the book.

See the book and its free files →

Other free templates

This template goes with The Real Estate Debt Investor. 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.