THE REAL ESTATE WORKOUT - BLANK MODEL SET: THE BOOK'S OWN INPUTS

Type these back into the blank files and the book's results come back. Cell references are sheet!cell.

Harbour_Quay_The_Four_Options_Blank.xlsx  (Chapters 1 to 7)

   1. Assumptions!B4                 6.2       Net operating income at origination (EUR m)
   1. Assumptions!B5                 5.15      Net operating income today (EUR m)
   1. Assumptions!B6                 0.045     Market yield at origination
   1. Assumptions!B7                 0.0665    Market yield today
   1. Assumptions!B9                 82.5      Loan outstanding (EUR m)
   1. Assumptions!B10                0.0225    Margin
   1. Assumptions!B11                0.0385    Base rate
   1. Assumptions!B12                1.8       Interest cover covenant
   1. Assumptions!B13                0.65      Loan to value covenant
   1. Assumptions!B15                2.25      Years to complete an enforcement sale
   1. Assumptions!B16                0.18      Forced-sale discount to market value
   1. Assumptions!B17                0.055     Enforcement costs, as a share of gross proceeds
   1. Assumptions!B18                0.021     Holding costs per year, as a share of value
   1. Assumptions!B19                0.09      Discount rate
   1. Assumptions!B21                3         Extension term (years)
   1. Assumptions!B22                0.015     Margin uplift
   1. Assumptions!B23                0.01      Extension fee, as a share of the loan
   1. Assumptions!B24                0.015     Amortisation per year, as a share of the loan
   1. Assumptions!B25                5.15      Business plan income, year 1 (EUR m)
   1. Assumptions!B26                5.45      Business plan income, year 2 (EUR m)
   1. Assumptions!B27                5.95      Business plan income, year 3 (EUR m)
   1. Assumptions!B28                0.0625    Exit yield in three years
   1. Assumptions!B29                4.5       Letting capex the plan requires (EUR m)
   1. Assumptions!B30                3         New sponsor equity offered (EUR m)
   1. Assumptions!B31                0.025     Sale costs on the exit
   1. Assumptions!B33                0.72      Cents on the euro offered
   1. Assumptions!B34                0.5       Years to completion of a payoff
   1. Assumptions!B36                0.03      Discount to market value in a cooperative sale
   1. Assumptions!B37                0.9       Years to complete a cooperative sale
   1. Assumptions!B38                0.025     Sale costs

   Result: enforcement 46.42, extension 80.33, payoff 56.89, consensual sale 66.42; indifference point 58.7 cents; break-even probability 47%.

The_Enforcement_Model_Blank.xlsx  (Chapters 3, 15, 24, 28)

   1. Assumptions!B4                 5.15      Net operating income (EUR m)
   1. Assumptions!B5                 0.0665    Market yield
   1. Assumptions!B6                 82.5      Loan outstanding (EUR m)
   1. Assumptions!B8                 0.18      Forced-sale discount to market value
   1. Assumptions!B9                 2.25      Years to complete the sale
   1. Assumptions!B10                0.055     Transaction costs, as a share of gross proceeds
   1. Assumptions!B11                0.021     Holding costs per year, as a share of value
   1. Assumptions!B12                0.09      Discount rate
   1. Assumptions!B14                0.5       Years to completion of a payoff

   Result: present value 46.42, recovery 56.3%, share pledge 73.5%, waiting break-even 73 bp.

The_Extension_Model_Blank.xlsx  (Chapters 4, 21, 22, 26)

   1. Assumptions!B4                 82.5      Loan outstanding (EUR m)
   1. Assumptions!B5                 0.0225    Margin
   1. Assumptions!B6                 0.0385    Base rate
   1. Assumptions!B7                 0.0665    Market yield today
   1. Assumptions!B8                 0.09      Discount rate
   1. Assumptions!B10                5.15      Net operating income, year 1 (EUR m)
   1. Assumptions!B11                5.45      Net operating income, year 2 (EUR m)
   1. Assumptions!B12                5.95      Net operating income, year 3 (EUR m)
   1. Assumptions!B13                4.5       Letting capex required (EUR m)
   1. Assumptions!B14                3         New sponsor equity (EUR m)
   1. Assumptions!B15                0.0625    Exit yield in three years
   1. Assumptions!B16                0.025     Sale costs on exit
   1. Assumptions!B18                3         Term (years)
   1. Assumptions!B19                0.015     Margin uplift
   1. Assumptions!B20                0.01      Fee, as a share of the loan
   1. Assumptions!B21                0.015     Amortisation per year, as a share of the loan
   1. Assumptions!B22                1.8       Interest cover covenant, as reset
   1. Assumptions!B23                0.65      Loan to value covenant, as reset
   1. Assumptions!B25                0.72      Payoff offered (cents on the euro)
   1. Assumptions!B26                0.5       Years to completion of a payoff
   1. Assumptions!B28                70        Notional (EUR m)
   1. Assumptions!B29                0.0295    Fixed rate paid
   1. Assumptions!B30                0.033     Forward curve for the residual term
   1. Assumptions!B31                1.75      Years remaining
   1. Assumptions!B33                0.5       Share of interest guaranteed
   1. Assumptions!B34                8         Cap (EUR m)
   1. Assumptions!B35                0.6       Share actually collectable
   1. Assumptions!B36                1.5       Years to judgment and collection

   Result: present value 80.33, recovery 97.4%, balance at exit 86.74, break-even exit yield 6.69%, guarantee 3.98.

Diligence_and_the_Decision_Record_Blank.xlsx  (Chapter 26, Appendix A)

   3. Committee paper!B14            5.15      Net operating income today (EUR m)
   3. Committee paper!B15            0.0665    Market yield today
   3. Committee paper!B16            82.5      Loan outstanding (EUR m)
   3. Committee paper!B17            0.18      Forced-sale discount
   3. Committee paper!B18            2.25      Years to complete an enforcement sale
   3. Committee paper!B19            0.055     Enforcement costs, share of gross proceeds
   3. Committee paper!B20            0.021     Holding costs per year, share of value
   3. Committee paper!B21            0.09      Discount rate
   3. Committee paper!B22            0.72      Payoff offered (cents on the euro)
   3. Committee paper!B23            0.5       Years to completion of a payoff
   3. Committee paper!B24            0.061     All-in rate today (margin plus base)
   3. Committee paper!B25            0.015     Extension margin uplift
   3. Committee paper!B26            0.01      Extension fee, share of the loan
   3. Committee paper!B27            0.015     Amortisation per year, share of the loan
   3. Committee paper!B28            5.15      Business plan income, year 1 (EUR m)
   3. Committee paper!B29            5.45      Business plan income, year 2 (EUR m)
   3. Committee paper!B30            5.95      Business plan income, year 3 (EUR m)
   3. Committee paper!B31            4.5       Letting capex (EUR m)
   3. Committee paper!B32            3         New sponsor equity (EUR m)
   3. Committee paper!B33            0.0625    Exit yield on the extension
   3. Committee paper!B34            0.025     Sale costs on exit
   2. Decision record!B14            82.5      Loan outstanding
   2. Decision record!B15            46.42     Enforcement present value
   2. Decision record!B16            80.33     Extension present value
   2. Decision record!B17            56.89     Payoff present value
   2. Decision record!B18            0.5874    Indifference point (cents)

   Result: switching values 94.9, 0.47 years, 0.6%, 10.29%, 5.01%.

