CMBS AND CRE CLOS - 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.
Steps of grids and tables (severities, years, advance rates, carrying values, appraisal steps, scenario rows) are kept in the blank files.

Deal_Model_Blank.xlsx

   Loan tape!A5                            Harborline Center
   Loan tape!B5                            Office
   Loan tape!C5                            92000000
   Loan tape!D5                            0.0615
   Loan tape!E5                            0.61
   Loan tape!F5                            1.48
   Loan tape!G5                            10
   Loan tape!H5                            0
   Loan tape!I5                            0
   Loan tape!A6                            Cedar Ridge Apartments
   Loan tape!B6                            Multifamily
   Loan tape!C6                            78500000
   Loan tape!D6                            0.0585
   Loan tape!E6                            0.64
   Loan tape!F6                            1.35
   Loan tape!G6                            10
   Loan tape!H6                            0
   Loan tape!I6                            0
   Loan tape!A7                            Northgate Logistics Park
   Loan tape!B7                            Industrial
   Loan tape!C7                            74000000
   Loan tape!D7                            0.0605
   Loan tape!E7                            0.55
   Loan tape!F7                            1.82
   Loan tape!G7                            10
   Loan tape!H7                            0
   Loan tape!I7                            0
   Loan tape!A8                            The Willoughby Hotel
   Loan tape!B8                            Hotel
   Loan tape!C8                            68000000
   Loan tape!D8                            0.0705
   Loan tape!E8                            0.58
   Loan tape!F8                            1.95
   Loan tape!G8                            5
   Loan tape!H8                            30
   Loan tape!I8                            0
   Loan tape!A9                            Stonebridge Commons
   Loan tape!B9                            Retail
   Loan tape!C9                            63500000
   Loan tape!D9                            0.0645
   Loan tape!E9                            0.595
   Loan tape!F9                            1.61
   Loan tape!G9                            10
   Loan tape!H9                            30
   Loan tape!I9                            3
   Loan tape!A10                           Marlowe Tower
   Loan tape!B10                           Office
   Loan tape!C10                           61000000
   Loan tape!D10                           0.0635
   Loan tape!E10                           0.635
   Loan tape!F10                           1.42
   Loan tape!G10                           10
   Loan tape!H10                           0
   Loan tape!I10                           0
   Loan tape!A11                           Ashcroft Flex
   Loan tape!B11                           Industrial
   Loan tape!C11                           61000000
   Loan tape!D11                           0.0615
   Loan tape!E11                           0.575
   Loan tape!F11                           1.66
   Loan tape!G11                           10
   Loan tape!H11                           0
   Loan tape!I11                           0
   Loan tape!A12                           Palmetto Storage Portfolio
   Loan tape!B12                           Self-storage
   Loan tape!C12                           55000000
   Loan tape!D12                           0.062
   Loan tape!E12                           0.57
   Loan tape!F12                           1.74
   Loan tape!G12                           10
   Loan tape!H12                           0
   Loan tape!I12                           0
   Loan tape!A13                           Riverbend Industrial
   Loan tape!B13                           Industrial
   Loan tape!C13                           52000000
   Loan tape!D13                           0.06
   Loan tape!E13                           0.54
   Loan tape!F13                           1.88
   Loan tape!G13                           10
   Loan tape!H13                           0
   Loan tape!I13                           0
   Loan tape!A14                           Grand Union Retail
   Loan tape!B14                           Retail
   Loan tape!C14                           48500000
   Loan tape!D14                           0.0655
   Loan tape!E14                           0.62
   Loan tape!F14                           1.52
   Loan tape!G14                           10
   Loan tape!H14                           30
   Loan tape!I14                           0
   Loan tape!A15                           Aspen Court Apartments
   Loan tape!B15                           Multifamily
   Loan tape!C15                           46000000
   Loan tape!D15                           0.0595
   Loan tape!E15                           0.65
   Loan tape!F15                           1.31
   Loan tape!G15                           10
   Loan tape!H15                           0
   Loan tape!I15                           0
   Loan tape!A16                           Fenwick Business Park
   Loan tape!B16                           Office
   Loan tape!C16                           44000000
   Loan tape!D16                           0.066
   Loan tape!E16                           0.66
   Loan tape!F16                           1.28
   Loan tape!G16                           7
   Loan tape!H16                           30
   Loan tape!I16                           0
   Loan tape!A17                           Lakeshore Suites
   Loan tape!B17                           Hotel
   Loan tape!C17                           41000000
   Loan tape!D17                           0.0725
   Loan tape!E17                           0.6
   Loan tape!F17                           1.86
   Loan tape!G17                           5
   Loan tape!H17                           25
   Loan tape!I17                           0
   Loan tape!A18                           Tanner Mills Mixed-Use
   Loan tape!B18                           Mixed-use
   Loan tape!C18                           38500000
   Loan tape!D18                           0.063
   Loan tape!E18                           0.585
   Loan tape!F18                           1.55
   Loan tape!G18                           10
   Loan tape!H18                           0
   Loan tape!I18                           0
   Loan tape!A19                           Kingsway Distribution
   Loan tape!B19                           Industrial
   Loan tape!C19                           36000000
   Loan tape!D19                           0.061
   Loan tape!E19                           0.53
   Loan tape!F19                           1.9
   Loan tape!G19                           10
   Loan tape!H19                           0
   Loan tape!I19                           0
   Loan tape!A20                           Beaumont Plaza
   Loan tape!B20                           Retail
   Loan tape!C20                           33000000
   Loan tape!D20                           0.067
   Loan tape!E20                           0.645
   Loan tape!F20                           1.44
   Loan tape!G20                           10
   Loan tape!H20                           30
   Loan tape!I20                           0
   Loan tape!A21                           Silverton Apartments
   Loan tape!B21                           Multifamily
   Loan tape!C21                           31500000
   Loan tape!D21                           0.0605
   Loan tape!E21                           0.63
   Loan tape!F21                           1.38
   Loan tape!G21                           10
   Loan tape!H21                           0
   Loan tape!I21                           0
   Loan tape!A22                           Oakfield Corporate Center
   Loan tape!B22                           Office
   Loan tape!C22                           29000000
   Loan tape!D22                           0.0685
   Loan tape!E22                           0.68
   Loan tape!F22                           1.22
   Loan tape!G22                           5
   Loan tape!H22                           30
   Loan tape!I22                           0
   Loan tape!A23                           Verona Storage
   Loan tape!B23                           Self-storage
   Loan tape!C23                           25500000
   Loan tape!D23                           0.0625
   Loan tape!E23                           0.56
   Loan tape!F23                           1.7
   Loan tape!G23                           10
   Loan tape!H23                           0
   Loan tape!I23                           0
   Loan tape!A24                           Pinecrest Retail Strip
   Loan tape!B24                           Retail
   Loan tape!C24                           22000000
   Loan tape!D24                           0.0675
   Loan tape!E24                           0.615
   Loan tape!F24                           1.49
   Loan tape!G24                           10
   Loan tape!H24                           30
   Loan tape!I24                           0
   Loan tape!B28                           0.0003
   Loan tape!B29                           0.00015
   Loan tape!B30                           0.064
   Loan tape!B31                           340000
   Capital structure!A5                    A-1
   Capital structure!B5                    AAA
   Capital structure!C5                    48000000
   Capital structure!D5                    Yes
   Capital structure!A6                    A-2
   Capital structure!B6                    AAA
   Capital structure!C6                    112000000
   Capital structure!D6                    Yes
   Capital structure!A7                    A-3
   Capital structure!B7                    AAA
   Capital structure!C7                    165000000
   Capital structure!D7                    Yes
   Capital structure!A8                    A-SB
   Capital structure!B8                    AAA
   Capital structure!C8                    47000000
   Capital structure!D8                    Yes
   Capital structure!A9                    A-4
   Capital structure!B9                    AAA
   Capital structure!C9                    190000000
   Capital structure!D9                    Yes
   Capital structure!A10                   A-5
   Capital structure!B10                   AAA
   Capital structure!C10                   138000000
   Capital structure!D10                   Yes
   Capital structure!A11                   A-S
   Capital structure!B11                   AAA
   Capital structure!C11                   75000000
   Capital structure!A12                   B
   Capital structure!B12                   AA-
   Capital structure!C12                   55000000
   Capital structure!A13                   C
   Capital structure!B13                   A-
   Capital structure!C13                   40000000
   Capital structure!A14                   D
   Capital structure!B14                   BBB
   Capital structure!C14                   35000000
   Capital structure!A15                   E
   Capital structure!B15                   BBB-
   Capital structure!C15                   20000000
   Capital structure!A16                   F
   Capital structure!B16                   BB
   Capital structure!C16                   22500000
   Capital structure!A17                   G
   Capital structure!B17                   B-
   Capital structure!C17                   15000000
   Capital structure!A18                   H
   Capital structure!B18                   NR
   Capital structure!C18                   37500000
   Capital structure!B26                   F
   Liquidations!B4                         0.0025
   Liquidations!B5                         0.01
   Liquidations!B6                         0.0155
   Liquidations!B7                         0.9
   Liquidations!B8                         4
   Liquidations!B9                         36
   Liquidations!A13                        Marlowe Tower
   Liquidations!B13                        22
   Liquidations!C13                        47500000
   Liquidations!D13                        45200000
   Liquidations!A14                        Oakfield Corporate Center
   Liquidations!B14                        17
   Liquidations!C14                        18500000
   Liquidations!D14                        17600000
   Liquidations!A15                        Beaumont Plaza
   Liquidations!B15                        14
   Liquidations!C15                        26900000
   Liquidations!D15                        25700000
   Stress!C3                               Base case
   Stress!C5                               0.12
   Stress!D5                               0.4
   Stress!C6                               0.03
   Stress!D6                               0.12
   Stress!C7                               0.1
   Stress!D7                               0.3
   Stress!C8                               0.04
   Stress!D8                               0.15
   Stress!C9                               0.12
   Stress!D9                               0.35
   Stress!C10                              0.04
   Stress!D10                              0.15
   Stress!C11                              0.08
   Stress!D11                              0.25
   Stress!F3                               Office repricing
   Stress!F5                               0.35
   Stress!G5                               0.55
   Stress!F6                               0.03
   Stress!G6                               0.12
   Stress!F7                               0.1
   Stress!G7                               0.3
   Stress!F8                               0.04
   Stress!G8                               0.15
   Stress!F9                               0.12
   Stress!G9                               0.35
   Stress!F10                              0.04
   Stress!G10                              0.15
   Stress!F11                              0.12
   Stress!G11                              0.3
   Stress!I3                               Rate shock at maturity
   Stress!I5                               0.3
   Stress!J5                               0.12
   Stress!I6                               0.2
   Stress!J6                               0.04
   Stress!I7                               0.25
   Stress!J7                               0.08
   Stress!I8                               0.3
   Stress!J8                               0.05
   Stress!I9                               0.2
   Stress!J9                               0.1
   Stress!I10                              0.2
   Stress!J10                              0.05
   Stress!I11                              0.25
   Stress!J11                              0.08
   Stress!L3                               Broad recession
   Stress!L5                               0.3
   Stress!M5                               0.5
   Stress!L6                               0.08
   Stress!M6                               0.22
   Stress!L7                               0.25
   Stress!M7                               0.4
   Stress!L8                               0.1
   Stress!M8                               0.25
   Stress!L9                               0.3
   Stress!M9                               0.45
   Stress!L10                              0.1
   Stress!M10                              0.25
   Stress!L11                              0.2
   Stress!M11                              0.35
   Stress!O3                               Severe
   Stress!O5                               0.5
   Stress!P5                               0.65
   Stress!O6                               0.12
   Stress!P6                               0.28
   Stress!O7                               0.4
   Stress!P7                               0.5
   Stress!O8                               0.15
   Stress!P8                               0.3
   Stress!O9                               0.4
   Stress!P9                               0.55
   Stress!O10                              0.15
   Stress!P10                              0.3
   Stress!O11                              0.3
   Stress!P11                              0.45
   Stress!G15                              6
   Stress!G16                              6
   Stress!G17                              9
   Stress!G18                              5
   Stress!G19                              4
   Stress!B22                              45.36945322050212
   Stress!B23                              2
   Surveillance!B4                         4

   Result: realized loss 44.71 million, 4.47 per cent; H written off, G 7.21 million; reconciliation closes; stress losses 2.34, 5.67, 1.94, 7.78, 15.42 per cent.

Loss_Severity_Bridge_Blank.xlsx

   Bridge!B4                               61000000
   Bridge!B5                               45200000
   Bridge!B6                               0.0635
   Bridge!B7                               22
   Bridge!B8                               4
   Bridge!B9                               47500000
   Bridge!B10                              0.9
   Bridge!B11                              0.01
   Bridge!B12                              0.0025
   Bridge!B13                              0.0155
   Bridge!B14                              0.00045

   Result: severity 25.90 to 37.24 per cent (40.29 without the reduction); break-even 5.86 per cent a year (8.48).

ARA_And_Control_Blank.xlsx

   ARA and ASER!B4                         0.9
   ARA and ASER!B5                         0.00045
   ARA and ASER!B6                         0.06291574999999998
   ARA and ASER!B7                         0.25
   ARA and ASER!B9                         37500000
   ARA and ASER!C9                         H
   ARA and ASER!B10                        15000000
   ARA and ASER!C10                        G
   ARA and ASER!B11                        22500000
   ARA and ASER!C11                        F
   ARA and ASER!A14                        Marlowe Tower
   ARA and ASER!B14                        61000000
   ARA and ASER!C14                        0.0635
   ARA and ASER!D14                        4
   ARA and ASER!E14                        47500000
   ARA and ASER!F14                        22
   ARA and ASER!A15                        Oakfield Corporate Center
   ARA and ASER!B15                        28024570.38328121
   ARA and ASER!C15                        0.0685
   ARA and ASER!D15                        4
   ARA and ASER!E15                        18500000
   ARA and ASER!F15                        17
   ARA and ASER!A16                        Beaumont Plaza
   ARA and ASER!B16                        31859483.27085127
   ARA and ASER!C16                        0.067
   ARA and ASER!D16                        4
   ARA and ASER!E16                        26900000
   ARA and ASER!F16                        14

   Result: reductions 39.92 million; 217,173 a month against a 196,612 coupon; control passes at 28.13 million, to G.

B_Piece_Return_Blank.xlsx

   Cash flows!B4                           75000000
   Cash flows!B5                           0.06291574999999998
   Cash flows!B6                           0.125
   Cash flows!B7                           30000000
   Cash flows!B8                           5
   Cash flows!B9                           2
   Grid!B3                                 1000000000

   Result: 45.37 cents; the grid of Chapter 18; the deal as it happened 8.02 per cent (year 5).

CRE_CLO_Equity_Blank.xlsx

   Equity!B4                               0.043
   Equity!B5                               0.0325
   Equity!B6                               0.0035
   Equity!B7                               0.8
   Equity!B8                               0.018
   Equity!B9                               0.0025
   Equity!B10                              0.0015
   Equity!B20                              1.155

   Result: 13.35 per cent before losses, 2.67 per cent break-even loss; the test fails at 76.00, 25.33, 15.20, 10.86 per cent.

Refinancing_Gap_Blank.xlsx

   Your loan!B5                            39849083.91210616
   Your loan!B6                            4316311.755737804
   Your loan!B7                            0.0725
   Your loan!B8                            0.095
   Your loan!B9                            0.65
   Your loan!B10                           1.25
   Your loan!B11                           0.0715
   Your loan!B12                           30
   Your loan!B13                           0.0025
   Your loan!B14                           6
   Your loan!B15                           0.0155
   Your loan!B16                           0.4
   Your loan!B17                           0.01
   Your loan!B18                           44000000
   Your loan!B19                           0.02
   Your loan!B20                           2
   Your loan!B21                           0.25

   Result: gap 1.15 million (2.89 per cent), loan-to-value binding; no loss above a 31.89 per cent discount.

