finance excel project

Project_TemplateforCapitalRegs_2of3_S2018.xlsx

Balance Sheet

Capital Adequacy Project Spring 2018
	Home National Bank
	Balance Sheet
	12/31/17
	(in thousands)
	Assets			Amount	x Risk Weight	= RWA
		Cash		$ 151,600
		Cash Items in the Process of Collection		2,400
		Due from Fed		12,500
		Due from other banks		750,500
		Securities:
			U.S. T-bills	5,500
			U.S. GNMA securities, long-term	420,500
			Repos secured by federal agencies	12,600
			General obligations municipal bonds	170,000
			Municipal revenue bonds	130,250
			Federal Reserve stock	12,000
		Loans:
			Commercial loans	4,525,600
			Consumer loans	1,252,000
			Residential mortgages, category 1, loan-to-value ratio 75%	2,604,000
			Residential mortgages, category 2, loan-to-value ratio 95%	150,000
			Loans to sovereigns, OECD CRC rated 2	12,600
			Loans to foreign banks, OECD CRC rated 3	1,256,200
			Nonperforming loans	50,350
			Loan loss reserve	(150,600)
		OREO		12,500
		Premises and equipment		460,000
			Total Assets	$ 11,840,500
	Liabilities
		Deposits		$ 8,629,200
		Subordinated Debt (> 5 yrs.)		546,300
		Other Liabilities		1,795,000
		Total Liabilities		10,970,500
	Equity capital
		Common Stock, at par		$ 205,000
		Surplus (all related to common stock)		50,000
		Noncumulative Perpetual Preferred Stock, qualifying		250,000
		Cumulative Preferred stock, nonqualifying		15,000
		Retained Earnings		350,000
		Total Equity capital		870,000
			Total Liabilities and Equity capital	$ 11,840,500

Capital Adequacy Project Spring 2018
	Home National Bank
	Balance Sheet
	12/31/17
	(in thousands)
	Off Balance Sheet Items:			Amount	x Conversion Factor	= Credit Equivalent Amount	x Risk Weight	= RWA
		U.S. Government Counterparty:
			Loan commitmentsA:
			< 1 year	$ 200
			1-5 year	1,200
			Standby letters of credit:
			Performance-related	150
			Direct-credit substitute	50
		U.S. Depository Institutions Counterparty:
			Loan commitmentsA:
			< 1 year	2,000
			> 1 year	4,500
			Standby letters of credit:
			Performance-related	100
			Direct-credit substitute	57,800
			Commercial letters of credit	4,000
		State and Local Government Counterparty:
		(revenue municipals)
			Loan commitmentsA:
			>1 year	1,000
			Standby letters of credit:
			Performance-related	126,700
		Corporate Customer Counterparty:
			Loan commitmentsA:
			< 1 year	3,750,000
			>1 year	2,798,000
			Standby letters of credit:
			Performance-related	125,700
			Direct-credit substitute	578,000
			Commercial letters of credit	98,000
		Sovereign Counterparty:
			Loan commitmentsA, OECD CRC rated 1:
			< 1 year	125,000
			>1 year	1,356,000
			Loan commitmentsA, OECD CRC rated 2:
			< 1 year	75,000
			>1 year	100,000
			Loan commitmentsA, OECD CRC rated 7:
			>1 year	20,000
		Interest rate market contracts:
			< 1 year (notional amount)C	7,000
			> 1-5 year (notional amount)C	12,000
			> 5 year (notional amount)B	25,000
			Current exposure
		Foreign Exchange rate market contracts:
		(current exposure assumed to be zero)
			< 1 year (notional amount)C	5,000
			> 1-5 year (notional amount)B	8,000
			Current exposure
		A	non-cancellable
		B	Current exposure ('000s) = $3,000
		C	Current exposure ('000s) = $0