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

Type these back into the blank files and the book's results come back (corrected where the book was wrong).
Cell references are sheet!cell. Percentages are given as decimals (0.0525 = 5.25%).

Alder_Point_The_Four_Values_Blank.xlsx  (Chapters 1 to 3, 5, 11, 15 and 16)

   Inputs!B7                         6,420,000
   Inputs!B8                         5,900,000
   Inputs!B9                         120,000
   Inputs!B10                        0.0525
   Inputs!B11                        0.0675
   Inputs!B12                        0.04
   Inputs!B13                        91,500,000
   Inputs!B17                        72,000,000
   Inputs!B18                        0.061
   Inputs!B30                        0.685
   Inputs!B34                        12,000,000
   Inputs!B35                        74,000,000
   Inputs!B36                        0.03
   Inputs!B37                        120,000,000
   Inputs!B38                        12
   Inputs!B39                        0.07
   Inputs!B43                        64,000,000
   Inputs!B44                        4,480,000
   Inputs!B45                        900,000
   Inputs!B46                        20
   Inputs!B47                        0.08
   Inputs!B48                        14,000,000
   Four Values!C15                   0.9629629629629629

   Result: recovery 82,204,444; strike 105,109,489; fair sale-and-leaseback price 114,225,435, overpayment 5,774,565.

Sizing_and_the_Binding_Test_Blank.xlsx  (Chapter 4)

   Inputs!B7                         6,300,000
   Inputs!B8                         120,000,000
   Inputs!B9                         0.0525
   Inputs!B10                        72,000,000
   Inputs!B11                        0.061
   Inputs!B15                        0.65
   Inputs!B16                        1.2
   Inputs!B17                        0.08
   Inputs!B21                        200
   Inputs!B22                        5,000,000
   Sensitivity and Cures!B31         110,000,000
   Sensitivity and Cures!B32         5,700,000
   When the ranking flips!B6         0.0525
   When the ranking flips!C6         0.07
   When the ranking flips!D6         0.065
   When the ranking flips!B7         0.6
   When the ranking flips!C7         0.65
   When the ranking flips!D7         0.6
   When the ranking flips!B8         0.061
   When the ranking flips!C8         0.075
   When the ranking flips!D8         0.065
   When the ranking flips!B9         0.65
   When the ranking flips!C9         0.75
   When the ranking flips!D9         0.7
   When the ranking flips!B10        1.2
   When the ranking flips!C10        1.3
   When the ranking flips!D10        1.25
   When the ranking flips!B11        0.08
   When the ranking flips!C11        0.095
   When the ranking flips!D11        0.085

   Result: binding test loan-to-value on a 7.7% fall; debt yield second at 8.6%; cover 1.43x, 1.08x after 200 bp.

The_Loan_Tape_and_the_Waterfall_Blank.xlsx  (Chapters 8 to 10)

   Loan Tape, rows 6 to 43, columns B, C and F to R: copy them from the book's workbook
   The_Loan_Tape_and_the_Waterfall.xlsx, sheet Loan Tape (38 loans, sorted by balance).

   Tranches and Loss!C16             0.45
   Tranches and Loss!D16             1
   Tranches and Loss!C17             0.35
   Tranches and Loss!D17             0.45
   Tranches and Loss!C18             0.27
   Tranches and Loss!D18             0.35
   Tranches and Loss!C19             0.2
   Tranches and Loss!D19             0.27
   Tranches and Loss!C20             0.15
   Tranches and Loss!D20             0.2
   Tranches and Loss!C21             0.06
   Tranches and Loss!D21             0.15
   Tranches and Loss!C22             0
   Tranches and Loss!D22             0.06
   Tranches and Loss!I16             AAA
   Tranches and Loss!I17             AA
   Tranches and Loss!I18             A
   Tranches and Loss!I19             BBB
   Tranches and Loss!I20             BB
   Tranches and Loss!I21             B
   Tranches and Loss!I22             NR
   Tranches and Loss!D7              0.307
   Tranches and Loss!D8              0.25
   Tranches and Loss!D31             82,200,000
   Tranches and Loss!D32             60,000,000
   Tranches and Loss!D33             0.25
   Waterfall and Control!C29         70,000,000
   Waterfall and Control!C30         0.9
   Waterfall and Control!C32         2,200,000
   Waterfall and Control!C45         0.25

   Result: WA loan-to-value 58.4%, top five 30.7%, 73% maturing in years 3 and 4; case 3 takes class G and 18.7% of class F; appraisal reduction 11,200,000.

Unsmoothing_and_the_Bid_Blank.xlsx  (Chapters 16 to 18)

   Inputs!B7                         486,000,000
   Inputs!B8                         498,000,000
   Inputs!B9                         291,000,000
   Inputs!B10                        0.6
   Inputs!B14                        0.1
   Inputs!B15                        17,550,000
   Inputs!B19                        120,000,000
   Inputs!B20                        72,000,000
   Inputs!B25                        0.015
   Inputs!B26                        0.06
   Inputs!B30                        5,900,000
   Inputs!B31                        120,000
   Inputs!B32                        0.0525
   Inputs!B33                        520,000
   Inputs!B34                        12
   Inputs!B35                        0.07

   Result: true gross 468,000,000; real discount 0.85%; fully adjusted premium 2.5%.

