EXCEL Templates

This file contains the following examples:

• Calibration and parameter estimation of the Cox, Ingersoll and Ross (CIR) model, simple discretisation representation, using Least Squares Method
• Calibration and parameter estimation of the Cox, Ingersoll and Ross (CIR) model, covariance equivalent discretisation representation, using Least Squares Method

Interest rate models that use multiple factors and require estimates as well as configuration of drift, volatility for multiple factors, as in the case of the Heath, Jarrow, Merton (HJM) no arbitrage model.

The EXCEL file illustrates how to build a one factor interest rate model. This includes:

1. Defining the input cells (including initial zero yield curve term structure, initial volatility term structure, required tenor, length of intervals and valuation date)
2. Defining the calculation cells (including state price lattices, short rate tree, prices, yields and yield volatilities)
3. Defining the output cells (including median rates and sigma cells)
4. Setting up a Solver Function that links all the cells so that when run it determines the values for the output cells, i.e. the median rates and the volatilities.

A working example of that contains the following calculations:

• Present value of cash flow
• Price of Income Bond from first principles
• Price of Income Bond (using excel formula)
• Macaulay Duration using formula
• Macaulay Duration using excel formula
• Modified Duration using formula
• Modified Duration using excel formula
• Effective Duration
• Effective Convexity
• Approximate Price Change (%) using Duration - %increase in YTM
• Approximate Price Change (%) using Duration & Convexity Correction - %increase in YTM
• Approximate Price Change (%) using Duration - %decrease in YTM
• Approximate Price Change (%) using Duration & Convexity Correction - %decrease in YTM

The excel template used to generate a detailed credit portfolio portion of the ICAAP report. From transition matrices to provision and reserves stress testing, from profitability analysis to segment by segment breakdown of the credit portfolio.

Portfolio VaR – EXCEL Example is a detailed calculation sheet that demonstrates the calculation of VaR for a portfolio of six instruments comprising of 3 foreign exchange contracts (EUR, AUD and JPY) and three commodities (WTI, Gold and Silver). Before calculating the VaR for the portfolio, the metric is calculated for each instrument within the portfolio using the Simple Moving Average Variance Covariance Approach and the Historical Simulation Approach. It shows how a graph of Trailing Volatilities is constructed and the calculation of a crude estimate of the VaR number using the maximum volatility from this trailing volatility series.

The Asset Liability Management (ALM) EXCEL Examples includes 4 EXCEL files that present fully worked out procedures for the following ALM measurement tools:

• Market Value of Equity
• Earnings at Risk
• Cost to Close – Interest Rate Risk perspective
• Cost to Close – Liquidity Risk perspective

The “Calculating VaR for Futures and Options” EXCEL file demonstrates a methodology for calculating the Value at Risk (VaR) measure for futures and options.

• The methodology employs a Monte Carlo Simulator to first generate a terminal prices series for a period of 365 days.
• This is repeated for 100 scenarios.
• For each scenario, payoff or intrinsic value 365-day series is calculated using these terminal prices and strike prices for a futures contract as well as a call option and a put option.
• For each scenario, the price or discounted value series is calculated from the payoffs derived for each instrument.
• Average price series across all 100 scenarios is determined for each instrument.
• Return series is derived from the average price series for each instrument.
• Volatilities are calculated for each instrument.
• VAR metrics are calculated using the Variance Covariance Simple Moving Average and Historical simulation approaches for various confidence levels for each instrument.

One of the most pertinent questions in risk management has been: How much do you stand to lose, over a certain period and with a certain probability? What is that number and what does it stand for? The number being referred to here is Value at Risk (VaR). VaR uses historical market trends and volatilities to estimate the likelihood that a given portfolio's losses will exceed a certain amount.

The file demonstrates the calculation of the VaR measure using the following approaches:

1. Variance-covariance simple moving average approach
2. Variance-covariance exponentially weighted moving average approach
3. Historical simulation approach

These approaches are used to calculate individual VaR measures for various instruments such as a commodity, currency pair, equity shares and fixed income product.  They are also used to determine the VaR number for the portfolio as a whole. The calculation includes:

• Determination of return series from historical prices series
• Determination of volatilities (for Variance covariance approaches)
• Determination of VaR based on volatilities (for Variance covariance approaches), confidence level and holding period

The file demonstrates an example of how a Pre-Settlement Risk (PSR) limit is set for an FX forward contract and a Futures contract on WTI (crude oil) respectively.

These Value-at-Risk (VaR) based counterparty limits are calculated by:

1. Deriving a return series from historical price series
2. Deriving a volatility measure from the return series
3. Determining a worst case price shock based on the volatility measure, a confidence level and the days to settlement of the contract
4. Determining the PSR limit in absolute terms as well as in relation to the original contract value at inception

The excel file contains worked-out detailed examples for the following:

1. Forward Prices
2. Relationship between spot rates and forward rates
3. Yield to Maturity (YTM)
4. Forward Rate Agreements (FRAs)
5. Forward Exchange Rates
6. Value of a Forward Contract

The Black-Derman-Toy term structure model was developed by Fischer Black, Emmanuel Derman and William Toy in 1990. It is an example of a No-Arbitrage model. It assumes that all security prices and rates depend on only one single factor- the short rate.

The excel file demonstrates how the results from the Black Derman Toy model may be used to price bonds and options.

Examples illustrating the calculation of the following example metrics:

• Holding period return
• Beta with respect to market indices
• Jensen’s Alpha (including the test of significance for alpha)
• Sharpe Ratio
• Treynor Ratio
• Value at Risk (Simple Moving Average Example)
• Put Premium
• Correlation Coefficient (including significance testing and rules of thumb analysis)
• Portfolio volatility
• Volatility Trend Analysis

Examples illustrating the pricing of the following options using an alternate more efficient methodology by Mark Broadie:

• European calls and put options
• American calls and put options
• Knock in and knock out (sudden death) options

This file presents an example of Peer Group Ratio Analysis for a financial institution. It is a supporting file of the PDF course “Credit Analysis - Financial Institution” (not included in this purchase).

Course on pricing interest rate swaps and cross currency swaps divided into three separate sections that address basics of interest rate swaps, term structure modeling and boot strapping and mark to market and valuation.

In the third module of the course we look at the step by step methodology for calculating the value of interest rate options, in particular caps and floors. We also look at how the values of other derivative products may be calculated such as accrual swaps, commodity linked notes and range accrual notes.

The file illustrates the construction of a spot rate term structure and a forward rate term structure.

1. Par term structures are obtained from the interbank rates and treasury rates.
2. Cash flows (coupon and principal payments) of coupon bearing bonds are determined from the par term structures.
3. Cash flows are stripped so that each individual cash flow may be considered a zero coupon bond structure.
4. Individual cash flows are discounted, summed and equated to the par values of bonds to determine the zero coupon curve (spot rate) term structure.
5. Forward curve term structure is derived from bootstrapping the derived zero coupon curve term structure.
6. A graphical illustration of the resulting spot and forward term structures is also given.

Course on pricing interest rate swaps and cross currency swaps divided into three separate sections that address basics of interest rate swaps, term structure modeling and boot strapping and mark to market and valuation. 

Continuing from Module I, module II presents specific examples and a step by step procedure of how to determine the value or price of interest rate swaps in particular coupon and basis swaps and cross currency swaps in particular fixed-for-fixed , floating-for-floating and amortizing floating-for-floating currency swaps.

The file contains examples illustrating the pricing of options using the conventional or traditional Binomial Trees approach. In particular the following options are priced:

1. American Call Option
2. American Put Option
3. European Call Option
4. European Put Option

The file also illustrates how Greeks (i.e. sensitivity measures) for each of these options are calculated from the constructed binomial trees.

The Monte Carlo Simulation course contains:

• Simulated commodity prices generated using Black Schole’s Terminal Price
• Random numbers, zts, obtained by normally scaling Excel’s RAND() function 
• A path of prices for 10 time steps
• Terminal Prices for 25 different scenarios 

The Monte Carlo Simulation course contains:

• Simulated currency rates generated using Black Schole’s Terminal Price
• Random numbers, zts, obtained by normally scaling Excel’s RAND() function 
• A path of rates for 10 time steps
• Terminal rates for 25 different scenarios 

The Monte Carlo Simulation course contains:

• Simulated equity prices generated using Black Schole’s Terminal Price
• Random numbers, zts, obtained by normally scaling Excel’s RAND() function 
• A path of prices for 10 time steps
• Terminal Prices for 25 different scenarios 

This EXCEL file presents a variation of our Monte Carlo Simulation model for commodity prices. In the conventional model the random numbers used in the model are obtained by normally scaling EXCEL’s RAND() function. In this version however the random numbers are obtained from the commodity’s historical price series.

The Principal Component Analysis (PCA) excel file illustrates how PCA is used to determine the number of workable factors for the Heath-Jarrow-Merton (HJM) interest rate model. This includes:

• Data selection and adjustment
• Construction of the Covariance, eigenvector and diagonal matrices
• Setting up and running of Solver functionality to obtain solutions for eigenvectors and eigenvalues
• Determination of  number of component/ factors to be used in the HJM model
• Determination of functional forms for selected eigenvectors
• Determination of weights for functional forms through derived volatility calibration