Optimization of VaR and Trading Liquidity Risk in Excel: Unwinding a Position Optimally
If a trader needs to unwind a large position, he or she is exposed to: the potential loss from price movements, and trading liquidity risk The trader may wish to minimize the Value-at-Risk (VaR) after considering the trading costs (or cost of liquidation). What is the...
Modeling Value-at-Risk (VaR) and Conditional Value-at-Risk (CVaR) in Excel (Historical, Gaussian, and Cornish-Fisher)
Value-at-risk (VaR) is a popular risk measure used in financial institutions to measure the risk in their portfolios. It measures the minimum loss within an interval period at a given probability (e.g. 1% or 5% being the commonly used figure). For example, if a...
Binomial Option Pricing Model with Excel VBA (for European Options)
The tree-based method is an easy-to-implement model for option pricing, and it can be used to value about any type of options (American options, barrier options, digital options, Asian options, etc). In this tutorial video, I will implement the popular Cox, Ross, and...
Modeling Interest Rate Immunization: Managing the Interest Rate Risk of a Single Liability
How do bond managers target their bond portfolios to meet a liability payment sometime in the future? Given that bond value fluctuates due to changes in interest rates and spreads, how do we maximize the chances that the bond portfolio will be sufficient to cover for...
Finding Option Implied Volatility using Goal Seek in Excel
When we model a problem, we will usually have a set of inputs, feed it into the model and we get the output. There are also times, when we have a target output and we want to know what the input value (out of all the other inputs) would be in order to achieve that...
Portfolio Optimization using Solver in Excel
Let's say you have a client who wants to construct a stock portfolio, and she chose the following stocks: Apple (AAPL) Boeing Airlines (BA) Netflix (NFLX) Tesla (TSLA) Your client has stated that the objective of maximizing the Sharpe ratio of her portfolio. Her...
Monte-Carlo Simulation for Wealth Planning in Excel
I was inspired to write this piece after teaching a reading in the CFA Level 3 syllabus for the June 2020 exam. In Reading 28 on Overview of Private Wealth Management, it touched on a term called "Capital Sufficiency Analysis" (or Capital Needs Analysis), which is a...
Building a Black-Scholes-Merton (BSM) Option Pricing Calculator (with Greeks)
Sometime back, my student asked me for help in building a calculator in Excel for pricing options using the Black-Scholes (or what they call as Black-Scholes-Merton) model. So, I thought of doing this tutorial to show how anyone with Excel can build their own BSM...