The Monte Carlo Simulation in Banks

Simplified Example in MS Excel and Practical Approach in German Savings Banks

Scientific Essay, 2010

29 Pages


Table of Content

List of Figures

List of Abbreviations

1. Introduction
1.1 Reasoning and Motivation
1.2 Structure of the Article

2. Risks in the Banking Sector
2.1 Definition of Risk
2.2 Structuring Risks in the Banking Sector

3. Measuring Risk with the Value at Risk
3.1 Definition of the Value at Risk
3.2 Meaning of the VaR for Risk Management in Banks
3.3 Structuring the Types of VaR Models

4. Modelling Credit Risk
4.1 Determinants for Modelling Credit Risk
4.2 Combining the Input Factors
4.3 Distribution of Credit Risk

5. The Monte Carlo Simulation
5.1 Basic Idea of the Monte Carlo Simulation
5.2 Migration Metrics by Random Scenarios
5.3 Discussing Advantages and Disadvantages of the Monte Carlo Approach

6. Development of a Simplified Monte Carlo Tool at the Example of a Bond Portfolio
6.1 Describing the Model
6.2 Setting up the Excel Sheet
6.3 Programming the Monte Carlo in Excel VBA
6.4 Analysing and Interpreting the Results

7. Monte Carlo Models in the German Banking Sector
7.1 General Overview
7.2 CreditPortfolioView: The Solution of the Savings Bank Sector

8. Final Conclusion and Critical Outlook


List of Figures

Fig. 1: Structuring risks in the banking sector

Fig. 2: Structuring and discussing VaR approaches

Fig. 3: Determinants for modelling Credit Risk

Fig. 3: Density function of credit risk and underestimation by using a normal distribution

Fig. 4: Basic Idea of the random number approach

Fig. 5: Excel sheet of a simplified Monte Carlo approach

Fig. 6: Program code of a VBA Excel Monte Carlo Approach

Fig. 7: Models to evaluate credit risk

Fig. 8: Ideal CPV model

Fig. 9: CPV in the German savings bank sector

List of Abbreviations

illustration not visible in this excerpt

1. Introduction

1.1 Reasoning and Motivation

Evaluating credit risk in banks becomes more and more important as the structured investment into credit risk is the most important value generator but also the most risky investment in German banks. In the recent past, banks had many losses in the credit portfolio. Further, the MaRisk[1], the second pillar of Basel II, require adequate solutions to quantify credit risk in the German banking sector. Last, the actual financial crisis shows that many banks are not able to measure credit risk resulting of more complex assets as ABS[2].

As a consequence, modelling credit risk with sophisticated VaR[3] tools in an objective way becomes more and more important as well. In theory and in practice, the Monte Carlo approach has established itself as the best solution to measure the credit risk. Current literature describes this approach in a very abstract way. Concrete examples of modelling or hints to realize these approaches in practice are not found very often. The main aim of this article is to describe the functionality of the Monte Carlo simulation at an easy example constructed with MS[4] Excel and to discuss it in a critical way.

1.2 Structure of the Article

The article is divided into 8 sections. After the introduction, the second section deals with the general definition of risk and the structure of risks occurring in the banking sector. Section 3 defines and explains the theoretical background of the Value at Risk approach. Next, section 4 discusses the basic factors for modelling credit risk. Section 5 deals with the theoretical status quo of the Monte Carlo simulation. A critical analysis of the approaches is implemented as well. While the first five sections are generated by analysing current literature, section 6 evaluates a simplified MS Excel program to make the core ideas of the Monte Carlo approach clearer. Section 7 describes the solution of the German savings bank sector. Section 8 as the last part sums up the main results and discusses the Monte Carlo approaches in the context of the actual financial crisis in Germany.

2. Risks in the Banking Sector

2.1 Definition of Risk

Whenever the discussion about risk models occurs, the first step is to define risk and to structure, what kind of risks exist. In general, risk is the positive or negative unexpected difference from an expected value[5]. In the banking sector, the positive difference is called chance, so only the negative difference from the expected value remains[6]. This leads to the result that expected changes from the actual value of an asset are not defined as risk. They can be calculated ex ante and can be priced into the asset. An example is the so called spread in the corporate bond market. It is defined as the difference between the yield of the bond and the maturity equivalent risk free rate, for example US treasuries or German Bundesschatzbriefe. The spread becomes higher, if the credit risks in the corporate bond increases. Actually, the spreads do not show the real credit risks a bond has. The financial crisis and the mistrust in the banking sector led to spreads that are too high.

2.2 Structuring Risks in the Banking Sector

According to Schierenbeck[7], risks in the banking sector can be divided into financial and operational risks. This leads to the structure defined in figure 1.

Fig. 1: Structuring risks in the banking sector

illustration not visible in this excerpt

Source : Schierenbeck (2001), pp. 5; Wiedemann (2005), p. 9; BaFin[8] (2007, MaRisk), AT 2.2, summarized in Reuse (2008), p. 6.

First, the liquidity risks have to be mentioned. In a simplified way, it can be defined as the risk that a bank cannot pay its obligations[9]. Up to 2007, it was not very important in the German banking sector, but actual developments at the financial markets have shown that illiquidity can be a real and important problem. The German banking supervisors had implemented the risk management of liquidity risk in the MaRisk[10] in 2005, but the processes did not function in many banks as the market of complex structured products crashed. Even though the main focus of this article is credit risk, it has to be kept in mind that liquidity risk and credit risk occurred in a complex combination in the actual crisis[11].

Second, the market price risk as a part of the financial success risks occurs. In theory, it is defined as the danger of losses of a financial product resulting of the change in the market prices[12]. The more practical definition is the unexpected loss of a product or portfolio[13] as market price risk always will always occur, if an official price is offered. Classical shares, bonds derivates and raw materials, but even the whole bank assets including the liability side can be subsumed under this risk.

Third, the operational risk occurs in the banking sector. It can be defined as the losses resulting from inefficient processes, errors in operating systems as IT or the mistakes humans do[14].

Last, credit risk has to be mentioned. It remains the most important risk in the German banking sector. Credit risk subsumes all risks that depend on the creditworthiness of the debtor[15]. Credit risk has two possible sub definitions. While the classical loss risks only measures the default of a debtor, the modern form measures changes in the degree of creditworthiness, even if this change does not lead directly to a default[16]. Therefore, the rating plays an important role. This can be shown at the example of corporate bonds: the worser the rating and thus the creditworthiness becomes, the higher is the spread – and the resulting present value of the bond decreases. This article will focus in the classical default risk as it is easier to model and explains the idea of the Monte Carlo approach much better. But before this, the theoretical basis, the VaR, has to be defined.

3. Measuring Risk with the Value at Risk

3.1 Definition of the Value at Risk

The VaR can be defined as the unexpected loss that will not be exceeded within a certain time under the assumption of a defined security level, called confidential level.[17] This definition leads to several factors that influence the VaR. In general, it can be stated that the VaR will increase, if the disposition period becomes longer and the confidential level gets higher[18]. The model used to measure the risk influences the VaR as well, but this depends on the risk category and not onto the basic assumptions to measure risk[19].

3.2 Meaning of the VaR for Risk Management in Banks

First, the VaR makes risks comparable. As shown in the definition, the main advantage of the VaR is that it makes risks comparable if consistent disposition periods and confidential levels are chosen. In bank controlling, risks are normally aggregated onto a 1 year disposition horizon with confidential level of at least 99%[20]. This leads to the effect that correlations can be implemented in order to evaluate an optimal asset allocation[21]. Without the VaR and the comparability of risks, an optimized risk/return management with the main focus on value oriented management would not be possible.

Second, the VaR leads to the maximal risks a bank can face. The sum of all VaR must not exceed the economic capital of a bank[22]. The main aim is that the bank survives, even if the risks occur simultaneously, perhaps by using correlation effects. Therefore, the economic capital must be higher than all VaR limits. Otherwise it will decrease and after a certain time, the central demands by German law – having a solvability coefficient higher that 8%[23] will be broken. Insolvency or illiquidity may be the consequences.

Third, the VaR is a possibility to reserve Risk Weighted Assets for the SolvV. In some risk categories it is possible to use internal models to quantify the required equity for the SolvV[24]. Normally, the high sophisticated models should lead to lower capital requirements, so the VaR can be used to get a better SolvV ratio. This leads to the effect that a bank has more free equity that can be used for further risky investments or as an additional reserve.


[1] Mindestanforderungen an das Risikomanagement der Kreditinstitute – Minimum requirements for risk management.

[2] Asset Backed Securities.

[3] Value at Risk.

[4] Microsoft.

[5] Cf. Woll (1996), p. 605.

[6] Cf. Rolfes (1999), p. 29, Reuse (2006.07-08), p. 366 and Reuse (2008), p. 5.

[7] Cf. Schierenbeck (2001), p. 4.

[8] Bundesaufsicht für Finanzdienstleistungen.

[9] For a more detailled definition of liquidity risk cf. Albert (2007), pp. 6.

[10] Cf. BaFin (2009, MaRisk), BTR 3.

[11] Theoretically discussed in Wimmer/Meyer (2006), p. 343.

[12] Cf. Rolfes (1999), p. 47.

[13] Cf. Reuse (2008), p. 7.

[14] Cf. Pfeiffer (2006), p. 446.

[15] Cf. Wimmer/Meyer (2006), p. 342.

[16] Cf. Schierenbeck (2001), p. 256.

[17] Cf. Schierenbeck (2001), p. 17, discussed in Reuse (2006.07-08), p. 366.

[18] Cf. Reuse (2006.07-08), p. 366.

[19] Cf. Rolfes (1999), p. 120.

[20] Cf. Bimmler/Mönke (2004), p. 34.

[21] Discussed in Reuse (2006), pp. 429.

[22] Legally required by the MaRisk. Cf. BaFin (2009, MaRisk), AT 4.1.

[23] Cf. §3 SolvV (2009).

[24] Cf. §§313 SolvV (2009).

Excerpt out of 29 pages


The Monte Carlo Simulation in Banks
Simplified Example in MS Excel and Practical Approach in German Savings Banks
Masaryk University  (Fakultät für Wirtschaft und Verwaltung)
Catalog Number
ISBN (eBook)
ISBN (Book)
File size
814 KB
Value at Risk, Monte Carlo, Credit Risk, Bank, CreditPortfolioView, CreditRisk+, CreditMetrics, Excel Tool
Quote paper
Svend Reuse (Author), 2010, The Monte Carlo Simulation in Banks , Munich, GRIN Verlag,


  • No comments yet.
Read the ebook
Title: The Monte Carlo Simulation in Banks

Upload papers

Your term paper / thesis:

- Publication as eBook and book
- High royalties for the sales
- Completely free - with ISBN
- It only takes five minutes
- Every paper finds readers

Publish now - it's free