Risk Model.
Risk model. All you can read & build quick reference guide to spreadsheets.
When was the last time you had to build a risk model in Excel? Do you remember wishing for a step by step guide? A model template? A translation of the differential equation from continuous to discrete spreadsheet friendly implementation? A risk spreadsheet that you could actually follow? From Option pricing to Value at risk; from Asset Liability Management to Treasury profitability analysis Excel spreadsheets simplify our lives and make it possible for us to generate and test answers and analysis.
We do this by providing high level views of spreadsheet models; discrete spreadsheet friendly implementations and quite frequently a step by step guide to building a model.
The risk model spreadsheet guide is for do it yourself (DIY) types. If you enjoy building and testing models in Excel, you will find quite a few quick reference posts. If you don’t this is probably the last page you would want to spend time on.
The collection represents everything we have written on building risk, portfolio and banking spreadsheet models over the last three years. The content is structured and organized based on topic popularity. The posts are generally short on text and words and long on content and value. The earlier editions are all in byte sized pieces and sometimes a bit awkward, the later editions are lengthier and easier to read and follow.
Enjoy.
Figure 1 Option Pricing using Monte Carlo Simulation – Pricing a lookback option
Risk model spreadsheets Commodity pricing models – Oil & Gold
A guide to calculating trailing volatility and correlation followed by a short post that tries to tackle the Gold bubble question? Is Gold over valued? What can the relative pricing model tell us?
 Crude Oil Mispricing Model – Commodity Prices and a study in Trailing Correlations
 Relative Gold Price Model – Forecasting the price of gold
Figure 2 WTI Crude Oil daily return series
Risk model spreadsheets Asset Liability Management
A short guide to Asset Liability Management reporting templates, assumptions and conventions including a review of building Maturity and Liquidity gaps.
 Asset Liability Management – Earnings at Risk
 Asset Liability Management – Fall in Market Value of Equity
 Convexity & Duration calculator for US Treasuries
 Fixed Income Investment Portfolio Management & Optimization Case Study
 Asset Liability Management Case Study: Understanding ALM using simple training cases
 Building Maturity Liquidity profiles for Deposits and Advances book for ALCO (ALM), ICAAP IAS 30 reporting
Figure 3 ALM Model
Risk model spreadsheets – Value at Risk, Correlation, Volatility
Volatility and value at risk. Cases, examples and sample exams.
Figure 4 Trailing volatility
Value at Risk
 Calculating Value at Risk (VaR): Step by Step Guide & Case study
 Value at Risk, Histograms and risk management in Excel
 Comparing VaR models, metrics and methods
 Calculating Conditional Value at Risk (CVaR) or Expected Shortfall – VaR and beyond
 Solved Solution – Value at Risk (VaR) Margin Lending Prime Brokerage Case Study
 Calculating Value at Risk for Options and Futures
Correlation
 Correlation – Times series data graphs –Prices & More
 Correlation – Scatter Plots
 Correlation – Correlation coefficient, r
 Correlation –Trailing Correlations
 Correlation – Relative Price Graphs
Market Risk Metrics
 Holding Period Return
 Beta with respect to market indices
 Jensen’s Alpha
 Sharpe and Treynor Ratios
 Put Premium
 Portfolio Volatility
 Volatility Trend Analysis
Risk model spreadsheets – Monte Carlo Simulation
Using Monte Carlo Simulation to simulate oil prices, market prices, fuel consumption and fuel hedging results.
Figure 5 Simulating the distribution
 Building your first Monte Carlo (MC) simulator model for simulated equity prices in Excel
 Monte Carlo Simulation Model How to – First pass
 Monte Carlo Simulation how to – Linking financial model to the simulation
 Monte Carlo Simulation – Simulating returns by replacing the normal distribution with historical returns
 Monte Carlo simulation methods – Tweaking the distribution
 Monte Carlo Simulation – Variance Reduction procedures: Antithetic Variable Technique & Quasi Random sequences

Figure 6 Simulation Model inputs
Risk model spreadsheets Option Greeks – Delta Hedging
If the Greeks had you confused, we have the perfect Rosetta Stone for you. Bring Monte Carlo Simulation and good old fashioned accounting together to decode Delta, Gamma, Vega, Theta and Rho.
Figure 7 Delta hedging & greeks
 Understanding Delta Hedging for options using Monte Carlo Simulation
 Dynamic Delta Hedging – Extending the Monte Carlo simulation model to Put contracts
 Dynamic Delta Hedging – Calculating Cash P&L (Profit & Loss) for a Call Option writer
 Hedging Gamma & Vega – The higher order Greeks hedge optimization Excel spreadsheet
 Delta Hedging applications for Rho, Rebalancing frequency & Implied Volatility
 Option Greeks – Dissection Theta and time premiums for call options
Risk model spreadsheets – Interest Rate models – CIR, BDT, HJM
Forecast interest rates using one and multifactor models in Excel
Interest Rate Models – CIR
Interest Rate Models – BDT
 Define Input Cells
 Define Output Cells
 Construct short rate binomial tree
 Construct State Price Lattices
 Calculate Prices from Lattice
 Calculate Yields & Yield volatility from Lattice
 Define & Set Solver Function & Results
Interest Rate Models – HJM

How to conduct a Principal Component Analysis in Excel

How to construct an HJM Interest Rate Model in Excel
Risk model spreadsheets – Option pricing
The big, thick, fat collection of posts on Option pricing
 Forward prices and rates
 Computational Finance: Basics: Calculating forward prices in Excel – Part I
 More Forward Rates Lessons: How to calculate Forward Rates – Calculations walk through
 Forward lessons: Derivative pricing: How to calculate the value of a forward contract in Excel
 Pricing Exotics
 Option Pricing – Pricing Exotic Options using Monte Carlo simulators
 Pricing Ladder Options using a Monte Carlo Simulator
 Modeling the Term structure, zero curve and forward curve
 Fixing the term structure
 Calculating the zero curve
 Calculating the forward curve
 Pricing Swaps – Part 1
 Calculating the MTM of the Swap
 Pricing a Cross Currency Swap – Floating for Floating structure
 Pricing a Cross Currency Swap – Amortizing and Indexed Term sheets
 Pricing Swaps – Part 2
 Interest Rate (IRS) & Currency Swaps (CCS) Value at Risk (VaR) with Historical Simulation – Excel Model walk through
 Practice Test Exam Question and Solution – Bootstrapping Zero and Forward Curves Case Study
 Practice Exam Test Question and Partial Solution – Pricing and MTM of Interest Rate Swaps (IRS)
