Canadian Imperial Bank of Commerce Financial Model is a financial model and valuation of a North American Major Diversified Bank that is regulated by Office of the Superintendent of Financial Institutions (OSFI). The model is ideal for investment banking, equity research, bank and financial institutions finance professionals, valuation and transaction experts. The model includes the following features:
- An annual 3-statement financial model of the bank including: a detailed banking industry revenue schedule, loan, net charge-off and interest income schedule, investment securities and interest income schedule, deposits and interest expense schedule, provisions for credit losses (PCL) and allowance for loan losses schedule, non-interest expense schedule, depreciation and amortization schedule, detailed income tax schedule, debt, wholesale funding and the corresponding interest expense schedule, shareholder’s equity schedule, risk weighted assets (RWA) schedule, regulatory capital and regulatory capital ratios schedule, covenants, statistics and financial measures schedule, income statement, cash flow statement and balance sheet for a five year forecast period.
- A thorough and detailed assumption tab for loans, deposits, investment securities, debt, equity, tax, depreciation and amortization, risk weighted assets (RWA), PCL, net charge-off, regulatory capital and risk inputs.
- A comprehensive description of the banking industry metrics, lingo, definitions and methodologies such as efficiency ratio, loan loss ratio, return on common shareholder’s equity (ROE), net interest margin, return on average assets and return on interest earning assets, total shareholder return, effective tax rate, economic capital, risk weighted assets, common equity tier 1 (CET1), tier 1 and total capital ratios, leverage ratio, leverage ratio exposure, liquidity coverage ratio, regulatory capital, provisions for credit losses, allowance for loan losses, wholesale funding and fundamental of banking industry and bank balance sheet.
- Historical financial statements of CIBC and the resulting credit statistics from 2015 to 2019 and the banking industry specific financial measures, value measures, asset quality and liquidity measures and corresponding performance charts and dashboard.
- Valuation of the bank under three approaches: intrinsic valuation, relative valuation and residual income approach.
- Calculation of potential dividends defined as levered free cash flow (“Free Cash Flow to Equity” or ”FCFE”) and calculation of reinvestment in the bank defined as the year over year change of regulatory capital.
- Quantification of risk measures for purposes of valuation including beta calculation and cost of equity calculation throughout the forecast period.
- Calculation of term growth rate, term return on equity (ROE) and term retention rate.
- Relative valuation of the bank using P/B multiples of peers and major Canadian banks.
- Residual income schedule and calculation of residual income using excess returns and existing value of book equity during the high growth period (5 years), convergence period (3 years) and terminal year.
- Implied share price derivation under intrinsic, relative and residual income valuation approaches.
- Scenario analysis for Base, Best and Worst Case loan portfolio growth rates for residential mortgages, personal loans and credit cards, business and government loans as well as loan portfolio interest income rate for residential mortgages, personal loans and credit cards, business and government loans.
- A summary dashboard of company financial report, market data, valuation and annual financial statements.
- The tabs of the models are: Cover, Summary, Assumptions, Scenarios, Historicals, Model, Valuation, Residual Income and Notes.
- The initiation report as of Q3/2020 for the bank and the print ready format of the financial model are included with the package. The research report describes the banking specific financial and performance measures and regulatory requirements as well as intrinsic and relative valuation of this financial institution, the operation and historical performance of the bank, COVID-19 and macroeconomic impact on the banking and financial sector for the foreseeable future as well as corporate governance issues in full details.
- Please refer here for a guideline on how to value banks.
Canadian Imperial Bank of Commerce Financial Model
- This financial model is a circular, 3-statement model with many other features explained in the product description. Please ensure prior to opening the excel sheet, iterative calculations within the excel workbook environment has been enabled under: File/Options/Formulas/Enable iterative calculations.
- Set the Calculation options under File/Options/Formulas to ‘Automatic except data tables’. In case of the existence of data tables in the models, press F9 to refresh and update the table calculations.
- The workbook contains a Macro in the form of the product Disclaimer which should be accepted by the user; hence, please enable macros in the workbook. The financial model itself contains no macros.
- Please ensure the above steps are implemented prior to opening the excel sheet.
- If at any point #Value! appears in the Model and the results, toggle the circularity switch on the Assumptions tab to ON and then OFF to address the problem. This switch sets all interest expenses and interest incomes to zero when turned ON.
- If any changes are applied to the assumption inputs in the model, toggle through the Scenario Switch on the Assumptions tab to Base, Best and Worst Case so that the Summary tab results are updated.
- All blue font in the model are hardcoded inputs, black font is calculation, red font is the references to the blue inputs on the Assumptions tab, and any orange cells are values sourced from Refinitiv.