SIGNIFICANT RISK TRANSFER - 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.
Portfolio_Template.xlsx has no blank twin: it arrives empty, with the book's portfolio as a worked example.

Capital_Model_Blank.xlsx

   Portfolio!B5                      520000000
   Portfolio!C5                      0.0009
   Portfolio!D5                      0.35
   Portfolio!E5                      3.4
   Portfolio!F5                      (leave empty: not an SME)
   Portfolio!G5                      16
   Portfolio!B6                      720000000
   Portfolio!C6                      0.0021
   Portfolio!D6                      0.38
   Portfolio!E6                      3.8
   Portfolio!F6                      (leave empty: not an SME)
   Portfolio!G6                      24
   Portfolio!B7                      880000000
   Portfolio!C7                      0.0038
   Portfolio!D7                      0.4
   Portfolio!E7                      4.1
   Portfolio!F7                      (leave empty: not an SME)
   Portfolio!G7                      32
   Portfolio!B8                      780000000
   Portfolio!C8                      0.0067
   Portfolio!D8                      0.42
   Portfolio!E8                      4.3
   Portfolio!F8                      (leave empty: not an SME)
   Portfolio!G8                      35
   Portfolio!B9                      560000000
   Portfolio!C9                      0.0121
   Portfolio!D9                      0.44
   Portfolio!E9                      4
   Portfolio!F9                      38
   Portfolio!G9                      33
   Portfolio!B10                     380000000
   Portfolio!C10                     0.0224
   Portfolio!D10                     0.45
   Portfolio!E10                     3.7
   Portfolio!F10                     24
   Portfolio!G10                     30
   Portfolio!B11                     160000000
   Portfolio!C11                     0.041
   Portfolio!D11                     0.45
   Portfolio!E11                     3.2
   Portfolio!F11                     15
   Portfolio!G11                     14
   Portfolio!B21                     0.135
   Portfolio!B25                     0.07
   Portfolio!B26                     0.05
   Portfolio!B27                     5

   Result: RWA 3,077.1 million, density 76.93 percent, KIRB 6.51 percent, N 166, capital 415.4 million, cover 19.95x and 3.99x.

Tranche_Capital_Blank.xlsx

   SEC-IRBA!B5                       0.06505021195590245
   SEC-IRBA!B6                       166.2721302536988
   SEC-IRBA!B7                       0.40615
   SEC-IRBA!B8                       5
   SEC-IRBA!B9                       0
   SEC-IRBA!B10                      0.07
   SEC-IRBA!B11                      0
   SEC-IRBA!B12                      0
   SEC-IRBA!B13                      4000000000
   Capital released!B5               0.07
   Capital released!B6               0.05
   Capital released!B7               3077076597.795123
   Capital released!B8               14034720.0
   Capital released!B9               0.135
   Capital released!B10              1
   Capital released!B11              0
   Capital released!B12              0

   Result: junior 1244.11 percent, senior 35.34 percent, capital released 223.9 million (299.2 under STS); floor reached at 9.74, nothing released below 3.54 percent.

Bank_Economics_Blank.xlsx

   Bank!B5                           4000000000
   Bank!B6                           3077076597.795123
   Bank!B7                           14034720
   Bank!B8                           0.135
   Bank!B9                           0.115
   Bank!B10                          0.105
   Bank!B11                          0.025
   Bank!B12                          266000000
   Bank!B13                          14000000
   Bank!B14                          0.3533579258578688
   Bank!B15                          3720000000
   Bank!B16                          0.0245
   Bank!B17                          0.003
   Bank!B18                          0.25
   Bank!B45                          12.441127396765491
   Bank!B72                          0.20353676380531813

   Result: net cost 27.9 million, 12.47 percent per euro, return on equity 12.99 to 17.25, ceilings 12.91 and 14.59, 9.5 million for the wrong box.

Investor_Return_Blank.xlsx

   Investor!B5                       4000000000
   Investor!B6                       0.07
   Investor!B7                       0.95
   Investor!B8                       0.0018223875
   Investor!B9                       1
   Investor!B10                      0.105
   Investor!B11                      0.025
   Investor!B12                      2
   Investor!B13                      0.08
   Investor!B14                      1.5
   Investor!B15                      0.0625
   Investor!B16                      5
   Investor!B17                      1
   Investor!B18                      0.075
   Stress!B4                         0.00350868
   Stress!B5                         0.3
   Stress!B6                         0.6

   Result: 10.39 percent unlevered, 16.60 levered, floor 7.61, break-evens 4.17x and 3.51x, the loss charged twice 2.61 points.

Bargaining_Range_Blank.xlsx

   Inputs!B5                         4000000000.0
   Inputs!B6                         3077076597.795123
   Inputs!B7                         14034720.0
   Inputs!B8                         0.135
   Inputs!B9                         0.115
   Inputs!B10                        0.0245
   Inputs!B11                        0.003
   Inputs!B12                        0.25
   Inputs!B13                        0.06505021195590246
   Inputs!B14                        166.27213025369883
   Inputs!B15                        0.40615
   Inputs!B16                        5
   Inputs!B17                        0.07
   Inputs!B18                        0.05
   Inputs!B19                        0.105
   Inputs!B20                        0.025
   Inputs!B22                        0.0018223875
   Inputs!B23                        2
   Inputs!B24                        0.08
   Inputs!B25                        0.075

   Result: range 7.61 to 12.91, width 5.30; levers 5.52, 13.12 and 17.47; optimum 9.75 percent at 6.85, return on equity 37.73.

