This webpage describes modelling and analysis of financial companies including banks and insurance companies. The issue of modelling beginning with assets and evaluating returns on investments is covered. In addition the subject of equity valuation is addressed as well as holding a constant capital structure. The valuation using a market to book analysis along with ROE is also covered. I start by discussing banks and then move to insurance companies.
Valuation and Modelling Analysis of Banks
Unique aspects of the process of valuation and modelling of a bank is demonstrated with a simple model. You can download the model by pressing the button below. The financial model of a bank should begin with financial statements including a balance sheet and an income statement like any corporate finance model. Except, in the case of a bank, the format is somewhat different with the presentation of loans, deposits and different types of equity capital. The screenshot below the button with the file illustrates the starting point of the financial modelling process for a bank.
Once the financial statements are established, you can work on developing assumptions for a bank. In the screenshot for the exercise file, I have hilighted the driver of growth and the requirement to keep equity to capital at a given level.
Valuation and Modelling Analysis of Insurance Companies
The power point slides work through modelling and valuation issues for insurance companies. I begin by discussing some stock price and cost of capital issues and then move to modelling and valuation.
Stock Prices and WACC
The graphs below introduce the valuation and modelling of insurance companies by presenting selected stock prices and valuation ratios. The stock prices come from the stock price file that you can download below. You can note the betas and the returns that have been earned by different companies. The graphs use different starting periods. Note that the volatility of the insurance companies is greater than the S&P 500 and the beta is generally greater than 1.0.
To see how much B.S. there is in measurement of WACC, I present the results of an absurd website that is supposed to give you the WACC for different industries.
Retrieving Financial Data, Multiples and Comparative Data
The file below extracts financial data on insurance companies that allows you to compare multiples for different insurance companies and to get data that can be the basis for a financial model. I present a couple examples of extracting data from this file below. The file is different from other files that extract data for companies like Amazon and EDF because of the accounting for insurance companies. It was a real pain to make the file for insurance companies from market watch and it works like the other files that download financial data. The first two screenshots demonstrate how you can create an analysis. But the most important thing in the file that you can download below for insurance companies is the ability to retrieve financial data to begin creating a financial model. The data you can retrieve is illustrated on the fourth screenshot below.
Creating Value for an Insurance Company
Before describing the financial modelling process for insurance companies I thought it would be a good idea to explain how the process for reserve accounting works. To do this I start with a very simple example that includes the following:
- Think of a single policy.
- Assume the present value of claims is the same as the present value of premiums
- You can use a discount rate that reflects the return on investments made between the receipt of premiums and payment of claims.
- Value comes from a positive present value of premiums; this occurs when the present value of writing new policies is positive.
To understand financial statements of insurance companies discuss an analysis of reserves, investment and accounting for a single policy. In this case there is no profit on the policy. At a 5% discount rate, the NPV of the premiums are equal to the NPV of the claims. I have made a little file with an analysis of reserves so that you can see how they work. You can download the file below. In this case it is assumed that the premium has a 1% premium relative to the present value of the claims. The starting point is illustrated below where the nominal outflow for the claim is assumed to be 5,000.
The next two screenshots demonstrate how the reserves are included on the balance sheet and how they build up to the ultimate liability. Using the premium example and the outflow the balance sheet and cash flow are demonstrated on the second screenshot. Of course for real companies the outflow for the claim is unknown and there are many premiums that continue.
- When new insurance business is written, provisions and reserves will increase. The insurance reserve is a significant liability and should undoubtedly be part of valuation.
- Hopefully, the net present value of writing new policies is positive. This means the PV of premiums is greater than the PV of future cash outflows.
- If the investments from premiums above cash outflows and the estimate of reserves which are a financial liability drive value, you must account of interest income and changes in the reserve liability. This implies use of equity cash flow rather free cash flow to the firm should be used for valuing insurance companies.
- The free cash flows to equity holders are discounted at the cost of equity.
Download file below with simple example of reserves.
Financial Modelling Process for an Insurance Company Using a Simple Case
As for the financial modelling process of a bank, the valuation and modelling of an insurance company is first demonstrated with a very simple model. You can download the simple insurance company model by pressing the button below. The financial model of an insurance company should begin with financial statements including a balance sheet and an income statement like any corporate finance model. Except, in the case of an insurance company, the format is somewhat different with the presentation of premiums, reserves and different types of equity capital. The screenshot below the button with the file illustrates the starting point of the financial modelling process for an insurance company. The screenshot also demonstrates that a historic switch is very helpful as usual for corporate models.
Once the financial statements are established, you can work on assumptions. In the screenshot for the exercise file, I have hilighted the driver of growth and the requirement to keep equity to capital at a given level. The screenshot with the assumptions demonstrate that you can develop a series of assumptions from historic financial statements.
Case Study of Aflac Case Study
The case study demonstrates how to use the database file to create base financial statements and how to automate assumptions. You can easily change the company and select a different basis for the historical statements as explained below.
Return on Risk Adjusted Capital and Bank Modelling and Analysis
The videos and files below demonstrate issues associated with computing the return on risk adjusted capital for banks and financial institutions. To make this calculation you much input the probability of default for loans. There are a lot of lookup and interpolate functions in this file. To compute probability of default, this file uses various different financial ratios and other qualitative judgements. The interpolate lookup function is used both for computing credit scores from financial ratios and also to compute probability of default and loss given default from the credit scores.
Financial Ratio Analysis Large Banks.xlsm