The book's own figures

1. The_Return_the_Lender_Earns_Blank.xlsx  (Chapter 7 and Appendix G)

   1. The Two Loans!B5               50000000
   1. The Two Loans!C5               50000000
   1. The Two Loans!B6               5
   1. The Two Loans!C6               3
   1. The Two Loans!B7               0.02
   1. The Two Loans!C7               0.055
   1. The Two Loans!B8               0.0075
   1. The Two Loans!C8               0.015
   1. The Two Loans!B9               0
   1. The Two Loans!C9               0.01
   1. The Two Loans!B10              0.005
   1. The Two Loans!C10              0.03
   1. The Two Loans!B11              0.15
   1. The Two Loans!C11              0.3
   1. The Two Loans!B12              0.5
   1. The Two Loans!C12              1.5
   1. The Two Loans!B13              0.0015
   1. The Two Loans!C13              0.0045
   1. The Two Loans!B14              150000
   1. The Two Loans!C14              450000
   1. The Two Loans!B18              0.045
   1. The Two Loans!B19              0.006
   1. The Two Loans!B20              0.08

   Result: 215 and 633 bp of revenue, 132.5 and 438.3 net, 33.1 and 36.5 per cent on capital, a tie at a PD of
   4.36 per cent, and 12.9 bp of compression absorbed on an early exit with no protection.

2. Sizing_And_Exit_Test_Blank.xlsx, two deals

   Chapter 18, the office loan (the book states no lending limits; those shown are Chapter 5's)

   1. The deal!C5        NOI today               6.2
   1. The deal!C6        Market value            96
   1. The deal!C7        Price paid              92
   1. The deal!C8        Acquisition costs       3
   1. The deal!C9        Capex within term       3
   1. The deal!C10       Loan requested          57
   1. The deal!C11       Stressed rate           0.06
   1. The deal!C12       Amortisation a year     0
   1. The deal!C15       Max LTV                 0.6
   1. The deal!C16       Max LTC                 0.65
   1. The deal!C17       Min ICR                 2.0
   1. The deal!C18       Min debt yield          0.09
   3. The exit test!C5   Balance at maturity     57
   3. The exit test!C6   NOI at maturity, good   6.4
   3. The exit test!C7   NOI at maturity, bad    4.6
   3. The exit test!C8   Exit yield, good        0
   3. The exit test!C9   Exit yield, bad         0
   3. The exit test!C11  Refi: min debt yield    0.1
   3. The exit test!C12  Refi: max LTV           0
   3. The exit test!C13  Refi: min ICR           0
   3. The exit test!C14  Refi: rate              0
   4. Downside!C5        NOI in the stress       4.4
   4. Downside!C6        Value in the stress     78
   4. Downside!C7        Balance after sweep     51
   4. Downside!C8        Recovery, enforced sale 70
   4. Downside!C9        Costs not in row 8      0
   4. Downside!C10       Printed break-even      0.33

   Result: LTV 59.4%, LTC 60.0% on 95 and 58.2% with the 3 of capex, cover 1.81x, debt yield 10.9%;
   refinancing 64 and 46, a gap of 11; in the downside 44, a shortfall of 13 on the loan and 7 on the
   51 balance; break-even at a 40.8% decline. The printed 33% needs 6.7 of unstated costs (sheet 4, row 21).
   At Chapter 5's 2.0x cover the loan would be 51.7: Chapter 18 calls 1.8x adequate.

   Chapter 5, the sizing example (sheet 4 is not used)

   1. The deal!C5        NOI today               7
   1. The deal!C6        Market value            100
   1. The deal!C7        Price paid              95
   1. The deal!C8        Acquisition costs       3
   1. The deal!C9        Capex within term       0
   1. The deal!C10       Loan requested          60
   1. The deal!C11       Stressed rate           0.05
   1. The deal!C12       Amortisation a year     0
   1. The deal!C15       Max LTV                 0.6
   1. The deal!C16       Max LTC                 0.65
   1. The deal!C17       Min ICR                 2.0
   1. The deal!C18       Min debt yield          0.09
   3. The exit test!C5   Balance at maturity     60
   3. The exit test!C6   NOI at maturity, good   7.2
   3. The exit test!C7   NOI at maturity, bad    5.4
   3. The exit test!C8   Exit yield, good        0
   3. The exit test!C9   Exit yield, bad         0
   3. The exit test!C11  Refi: min debt yield    0.09
   3. The exit test!C12  Refi: max LTV           0
   3. The exit test!C13  Refi: min ICR           2.0
   3. The exit test!C14  Refi: rate              0.05

   Result: 60, 63.7, 70 and 77.8, loan to value binds at 60. At maturity the bad case refinances 60 on
   debt yield alone and 54 on the chapter's own 2.0x cover at 5 per cent: a gap of 6.
