
190 Chapter 5
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
BACDEFHG
Cash as percent of deposits 5.0%
Interest on cash 0.0%
Interest on other investment securities 6.19%
Interest on mortgages 8.13%
Interest on other earning assets 6.71%
Interest on loans 8.77%
Interest on deposits 3.51% 3.60% 3.80% 3.90% 4.00% 4.20%
Interest on short-term borrowing 4.72%
Interest on long-term debt 6.38%
Loan loss as % of outstanding loans 0.30%
Noninterest income as % of interest income 25.30%
Noninterest expense as % of net interest income 70.00% <-- was 74.35%, but Large Bank analyst thinks this will go down to 70%
Growth rate of net loans 6%
Growth rate of deposits 5%
Depreciation rate 10%
Income tax rate 35%
Dividend payout ratio 60%
Balance sheets
2006
Financial
statements
2007 2008 2009 2010 2011
Cash 386,832 400,042 420,044 441,046 463,098 486,253
<-- =$B$2*G40
Other investment securities, including
money market instruments
[balance sheet plug]
2,190,106 942,851 949,564 952,879 952,403 947,709
<--
=IF(G26+G28+G29+G30+G35+G37>G47,
0,G47-(G26+G28+G29+G30+G35+G37))
Mortgage loans held for sale 18,953 18,953 18,953 18,953 18,953 18,953 <-- =F28
Other earning assets 21,444 21,444 21,444 21,444 21,444 21,444 <-- =F29
Loans, net 5,869,914 6,222,109 6,595,435 6,991,161 7,410,631 7,855,269 <-- =F30*(1+$B$17)
Land 38,099 38,099 38,099 38,099 38,099 38,099
Other fixed assets 282,956 356,728 396,364 440,405 489,339 543,709 <-- =G34+G35
Accumulated depreciation 156,754 192,427 232,063 276,104 325,038 379,408 <-- =F34-G69
Net fixed assets and land 164,301 164,301 164,301 164,301 164,301 164,301 <-- =F35
Other assets 360,087 360,087 360,087 360,087 360,087 360,087 <-- =F37
Total Assets 9,011,637 8,129,786 8,529,828 8,949,872 9,390,918 9,854,016 <-- =G37+G35+SUM(G26:G30)
Deposits 7,619,842 8,000,834 8,400,876 8,820,920 9,261,966 9,725,064 <-- =F40*(1+$B$18)
Short-term borrowings
[balance sheet plug]
251,687 0 0 0 0 0
<-- =IF(G38>G40+SUM(G42:G46),G38-
G40-SUM(G42:G46),0)
Long-term indebtedness 2,826 2,826 2,826 2,826 2,826 2,826 <-- =F42
Other liabilities 126,126 126,126 126,126 126,126 126,126 126,126 <-- =F43
Shareholders' Equity
Stock 489,322 489,322 489,322 489,322 489,322 489,322
Accumulated retained earnings 521,834 585,807 646,865 709,962 775,863 844,684 <-- =F46+G74
Total Liabilities and Shareholders' Equit
y
9,011,637 8,129,786 8,529,828 8,949,872 9,390,918 9,854,016 <-- =SUM(G40:G44)
Income statements
2007 2008 2009 2010 2011
Interest income
Interest on cash balances 0 0 0 0 0 <-- =$B$3*(G26+F26)/2
Interest on other investment securities 96,965 58,570 58,881 58,968 58,808 <-- =$B$4*(G27+F27)/2
Interest on mortgage loans 1,541 1,541 1,541 1,541 1,541 <-- =$B$5*(G28+F28)/2
Interest on other earning assets 1,439 1,439 1,439 1,439 1,439 <-- =$B$6*(G29+F29)/2
Interest on net loans 530,235 562,049 595,772 631,519 669,410 <-- =$B$7*(G30+F30)/2
Total interest income 630,180 623,599 657,633 693,467 731,198 <-- =SUM(G52:G56)
Interest expense
Interest on deposits -280,829 -302,432 -335,195 -361,217 -389,003 <-- =-F9*G40
Interest on short-term borrowings 0 0 0 0 0 <-- =-$B$10*G41
Interest on long-term borrowing -180 -180 -180 -180 -180 <-- =-$B$11*G42
Total interest expense -281,010 -302,612 -335,375 -361,397 -389,183 <-- =SUM(G60:G62)
Net interest income 349,170 320,987 322,257 332,070 342,015 <-- =G57+G63
Provision for loan loss -18,138 -19,226 -20,380 -21,603 -22,899 <-- =-$B$13*(G30+F30)/2
Noninterest income 159,436 157,771 166,381 175,447 184,993 <-- =$B$14*G57
Noninterest expenses -244,419 -224,691 -225,580 -232,449 -239,411 <-- =-$B$15*G65
Depreciation and amortization -35,673 -39,636 -44,040 -48,934 -54,371 <-- =-$B$20*G33
Income before income tax 246,049 234,841 242,678 253,465 264,699 <-- =SUM(G65:G68)
Provision for income tax 86,117 82,194 84,937 88,713 92,645 <-- =$B$21*G70
Net income 159,932 152,646 157,741 164,753 172,054 <-- =G70-G71
Dividends 95,959 91,588 94,645 98,852 103,233 <-- =$B$22*G72
Retained earnings 63,973 61,059 63,096 65,901 68,822 <-- =G72-G73
SMALL BANK
Pro forma model