Detailed profile at my About page

Fully Functional Excel Spreadsheet for SOFR Swaption Trading with Lognormal, Normal and Shifted Lognormal SABR Vol Calibration

cover
Setting up an interest rate swaption trading desk is a complex endeavour. First, the type of the underlying interest rate should be defined, and it can be either a still existing ibor rate such as EURIBOR or JPY TIBOR or – in the case of LIBOR that has been discontinued as of June 30, 2023 - an equivalent ARR (Alternative Reference Rate) such as a ...
Continue reading
  780 Hits
  0 Comments
780 Hits
0 Comments

Pricing Currency Swaps with different Collateral Currencies in Excel. The GBP SONIA vs USD SOFR case

cover
For a review of cross currency swaps and how to build an implied discounting yield curve of the foreign currency from given market basis spreads, read my post Currency Swaps and Basis Curves in Excel, where both the market swaps and the to-be-priced bespoke swap involve the exchange between 3-month USD LIBOR and 3-month EUR LIBOR collateralized thr...
Continue reading
  1863 Hits
  0 Comments
1863 Hits
0 Comments

Yield Curve Building in Excel with Central Bank Meeting Dates and STIR OIS Trading Arbitrage Opportunities: The GBP Sonia case

cover
One of the central banks' duties is to ensure the cost of money (aka interest rates) is optimal for the economy. If a) the economy is overheated and needs to slow down to avoid a future burst or b) inflation rates are too high due to increased consumer demand, higher interest rates will a₁) have a cooling effect on the economy by raising the c...
Continue reading
  2851 Hits
  0 Comments
2851 Hits
0 Comments

Using the Bootstrapped Market SOFR Caplet Normal Vol Surface to Price in Excel Interest Rate Caps/Floors on Backward/Forward Looking SOFR Term Rates

coverNew
In my earlier posts titled Overnight Index Swap (OIS): Pricing and Understanding using Excel and Overnight Index Swap (OIS): Observation Lags, Lookbacks, Rate Cutoffs and step-by-step Pricing in Excel I provided a detailed description of overnight index swaps known as OIS, of which the floating leg index in each payment period was defined...
Continue reading
  4756 Hits
  0 Comments
4756 Hits
0 Comments

Pricing FX Options in Excel using Risk Reversal and Butterfly Market Quotes. The USD / Indian Rupee Case

cover
Back in 2017 I had shown in a 13-min video titled Option pricing in Excel with Implied Volatility Surface using QuantLib how an option on the EURO STOXX 50 index can be priced using not a single vol quote, but rather a table of market volatilities spanning several option expiries and strikes.On this post I will turn my attention to the pricing of v...
Continue reading
  3436 Hits
  0 Comments
3436 Hits
0 Comments

Sonia OIS Relative Carry and Roll-Down in Excel. Has Bloomberg got it Wrong?

cover
I have discussed the generic concepts of Carry and Roll-Down in relation to the expected cash flows of any financial instrument in my post titled Carry and Roll-Down of USD Interest Rate Swaps in Excel with Bloomberg Comparison. The emphasis there was placed on their absolute (dollar) definition and a USD Libor interest rate swap was used as exampl...
Continue reading
  2866 Hits
  0 Comments
2866 Hits
0 Comments

Bootstrapping in Excel a Yield Curve to perfectly fit Bloomberg Price/Yield Quotes of US Treasury Bills, Notes and Bonds

cover
I have already described the bootstrapping method for building a yield curve from various instrument types in the yield curve articles category. In particular, my post on Yield Curve Building in Excel using Bond Prices (QuantLibXL vs Deriscope) demonstrates how this is done in practice using a simplified theoretical setup consisting of three m...
Continue reading
  8333 Hits
  0 Comments
8333 Hits
0 Comments

Overnight Index Swap (OIS): Observation Lags, Lookbacks, Rate Cutoffs and step-by-step Prising in Excel

cover
I have covered in quite exhausting detail the mathematical description of an Overnight Index Swap (OIS) in my earlier post about Pricing and Understanding OIS using Excel.Since then, OIS have increased in significance due to the cessation of Libor that affected primarily the USD and GBP currencies to the effect that today hardly anyone still trades...
Continue reading
  4657 Hits
  0 Comments
4657 Hits
0 Comments

Libor Transition Impact on Portfolio Pricing: A Comparative Study with and without the published Bloomberg Spread Adjustments

Cover
Everybody these days seems to ask the same question: On D-Day when LIBOR will cease to exist and pricing will rely solely on risk-free reference rates like SOFR or SONIA and the Spread Adjustments published by Bloomberg, what will the impact on portfolio pricing be?The mechanics of pricing a single USD interest rate swap with and without the ISDA-B...
Continue reading
  3142 Hits
  0 Comments
3142 Hits
0 Comments

Pricing and Risk Management in Excel of Inhomogeneous Trading Book containing Different Types of USD Interest Rate Swaps

Cover
In an earlier post with the title Trading Blotter and Book Risk Management of USD Interest Rate Swaps in Excel: Example of a Book with 10,000 Trades, I explained how to calculate the price and risk of a fictitious portfolio of up to 10,000 vanilla fixed-to-floating USD interest rate swaps that all involved the 3-month USD LIBOR index. Apart from th...
Continue reading
  2406 Hits
  0 Comments
2406 Hits
0 Comments

Libor Cessation: Price and Risk of existing Vanilla Interest Rate Swaps by applying the ISDA Fallback Protocol

cover
Today, the sum of the notional amounts of all financial products referencing the USD LIBOR is estimated to $400 trillion. These are diverse products ranging from derivatives such as interest rate swaps to consumer products such as student loans and home mortgages. About $170 trillion are in outstanding swaps, of which one third have maturities beyo...
Continue reading
  3381 Hits
  0 Comments
3381 Hits
0 Comments

Turn-of-the-Month Effect in SOFR Curve built out of 1- & 3- Month Futures

cover
In my previous article about building a SOFR yield curve in Excel using QuantLib and Deriscope I was confronted with an unexpected result concerning the short term – less than two years – section of the curve that exhibited a strong oscillatory behavior.In this post I will show how to smooth out this front section of the curve and investigate ...
Continue reading
  5267 Hits
  0 Comments
5267 Hits
0 Comments

How to build a SOFR Yield Curve in Excel using QuantLib and Deriscope

Cover
The markets expect that Libor will be gradually phased out as more investors and lenders prefer financial products tied to the Secured Overnight Financing Rate (SOFR) rather than the USD London Inter-bank Offered Rate (LIBOR).The main reason for this ongoing exodus is the expected cessation of the daily announcements of the interbank lending rates ...
Continue reading
  19873 Hits
  0 Comments
19873 Hits
0 Comments

PnL Explained in Excel when trading USD Interest Rate Swaps

cover
 In one of my earlier posts I have shown how to use Deriscope to calculate the Carry and Roll of a single interest rate swap. In the current post I will describe the Profit and Loss (PnL) as a mathematical quantity that is defined in a way like that of the Carry and Roll-Down. Table Of Contents​ PnL Definition and Relationship with the Ca...
Continue reading
  11521 Hits
  0 Comments
11521 Hits
0 Comments

Dependence of USD Interest Rate Swaps Price & Risk on Modelling Assumptions affecting the Curve Building

cover
In my post about USD Interest Rate Swaps in Excel, I have explained in detail how to calculate the price and risk of a single USD interest rate swap using a multi-curve approach and actual market data from Bloomberg as of 22 May 2019.A comparative analysis between the single-curve and multi-curve approach has been laid out in my post focusing on sw...
Continue reading
  3096 Hits
  0 Comments
3096 Hits
0 Comments

Pricing of Cross-Currency Collateralized Swaps using OIS vs non-OIS Discounting: The Mexican case.

cover
Last week I described the unique problem faced by interest rate swap traders when the collateral is kept in a currency different than the one where the swaps are denominated. You may want to visit that post for details on how the appropriate discounting (basis) curve in the Mexican case is constructed out of several market rates that include t...
Continue reading
  6835 Hits
  0 Comments
6835 Hits
0 Comments

Building in Excel the Implied Risk-Free Discounting Curve when the Collateral is kept in another Currency. Example: Mexico, where no Local OIS Market exists.

cover
Before the 2007-08 financial crisis, all swap cash flows were discounted with the same yield curve used in "forecasting" the future Libor rates and thus the amounts of the floating cash flows.At any given time, this yield curve was constructed so that it reproduced the market prices of the swaps traded at that time.After its construction, the yield...
Continue reading
  6629 Hits
  0 Comments
6629 Hits
0 Comments

Using Bloomberg Tenor Basis Swap Spreads in Excel to calculate 1M USD Libor Forward Rates

cover
Until the 2007-08 financial crisis, forward interest rates of any tenor had been calculated off one single yield curve. In the US, traders had been building one USD yield curve out of market-traded deposits, futures and fixed-to-3M-Libor swaps and used that curve for all purposes involving interest rate calculations, such as extracting discount fac...
Continue reading
  15063 Hits
  0 Comments
15063 Hits
0 Comments

Carry and Roll-Down of USD Interest Rate Swaps in Excel with Bloomberg Comparison

cover2
In my previous two posts I have shown how to calculate the price and DV01 of a single interest rate swap and how to do so with a book containing thousands of swaps.Now I turn my attention to the calculation of the Carry and Roll-Down of a single swap. Table Of Contents​ Carry and Roll-Down Definition Deriscope Computation Method Bloomberg Math...
Continue reading
  46130 Hits
  0 Comments
46130 Hits
0 Comments

Trading Blotter and Book Risk Management of USD Interest Rate Swaps in Excel: Example of a Book with 10,000 Trades.

cover
In my previous post about USD Interest Rate Swaps in Excel, I explained how to calculate the price and risk of a single USD interest rate swap using actual market data from Bloomberg as of 22 May 2019.In the current post, I will make use of the same market data to calculate the prices and DV01s (both flat and bucket) of two different swap collectio...
Continue reading
  7140 Hits
  0 Comments
7140 Hits
0 Comments

USD Interest Rate Swap: Cash Flows and DV01 in Excel using Bloomberg Market Data

cover
In this post I will make use of the realistic yield curves I built in Excel out of Bloomberg OIS, deposit, futures and Libor swap rates as of May 22, 2019 towards calculating the price and producing the detailed cash flows, flat DV01 and maturity-dependent DV01s of a bespoke forward starting 3-year swap.You may want to visit the respective article,...
Continue reading
  20632 Hits
  0 Comments
20632 Hits
0 Comments

OIS Discounted USD Libor Curve Production in Excel for Front Office Trading

cover
I have already explained in my earlier yield curve building post how one can use the Excel Deriscope addin to derive the discount factors and zero rates implied by any given market rates of deposits, futures and swaps.At another post about dual bootstrapping, I calculated in Excel the price of an actual 50-year EUR collateralized swap held by ...
Continue reading
  15488 Hits
  0 Comments
15488 Hits
0 Comments

Almost Corporate-Ready Unlimited-Currency FX Option Portfolio Pricing with and without Excel

cover
The question whether Excel can be trusted for pricing and risk managing derivative transactions seems to be a never ending one.If you are curious about my own opinion on this matter, you may jump to the end of this article. In the sequel, I will confront this issue through the real-world case of pricing and managing a book of FX options.In my last ...
Continue reading
  5497 Hits
  0 Comments
5497 Hits
0 Comments

Valuing a Portfolio of Multi-Currency FX Options and Producing its Value at Risk in Excel using Deriscope

cover
The topic here is not about simple option pricing but rather about dealing with the complexity introduced by the simultaneous existence of several different currencies in the context of calculating the Price and Value at Risk of a portfolio of European FX options.If you are not familiar with the basics of European option pricing in Excel using Deri...
Continue reading
  6886 Hits
  0 Comments
6886 Hits
0 Comments

Floating Rate Notes (FRN) in Excel: Understanding Duration, Discount Margin and KRD

cover
Deriscope exports in Excel over 30 functions that deal with bonds, as I have described in an earlier document about bond risk management functions.In spite of the fact that the referred functions apply to all types of bonds, including interest rate and inflation linked bonds, one should be cautious when the referenced bond does not pay a fixed rate...
Continue reading
  21268 Hits
  0 Comments
21268 Hits
0 Comments

Combining Randomized Quasi Monte Carlo (Sobol) and Parallel Processing (Multithreading) when Pricing Derivatives in Excel

cover
The beauty of Monte Carlo simulation is that it can be used to price any European financial derivative contract, of which the terminal payoff is expressed as a function of D terminal underlying factors by simulating the terminal values of these factors as of the contract's maturity date.It turns out, the successful execution of a Monte Carlo simula...
Continue reading
  8175 Hits
  0 Comments
8175 Hits
0 Comments

Monte Carlo Pricing of any European Structured Product in Excel: Revisiting the Morgan Stanley Trigger Plus 2024 Note

cover
In my previous article I showed how one can use Deriscope in Excel to calculate the price of a particular type of a structured product that pays the minimum of two equity indices observed at some specified future terminal time. As example was used the Trigger Plus note issued by Morgan Stanley on April 1, 2019, which is based on the value of t...
Continue reading
  6538 Hits
  0 Comments
6538 Hits
0 Comments

Perfect Bloomberg Price Match of an Interest Rate Swap in Excel by using Dual Bootstrapping

cover
Up until the financial crisis of 2008, the price calculation of an interest rate swap involved only the so-called Libor curve. The latter was essentially the discount factors (or equivalently zero rates or forward rates) implied by market-traded instruments, such as deposits, futures, forwards and swaps. The Libor curve was used to derive everythin...
Continue reading
  22683 Hits
  0 Comments
22683 Hits
0 Comments

Pricing Structured Products in Excel: The Morgan Stanley Trigger Plus 2024 Note

cover
Various structured products can be precisely priced in Excel using Deriscope and its underlying QuantLib analytics. In this article I will focus on the Trigger Plus product issued by Morgan Stanley on April 1, 2019. It is based on the value of the worst performing of the Dow Jones Industrial Average and the Russel 2000 Index due April 4, 2024. It o...
Continue reading
  11164 Hits
  0 Comments
11164 Hits
0 Comments

Currency Swaps and Basis Curves in Excel

cover
​The basic concepts of spot fx rates, forward fx contracts, fx swaps and the construction of foreign yield curves out of fx forward rates have been described in detail in my previous fx rates article.While these instruments cover the short end of the maturity spectrum – typically about a year -, the tenor of so-called currency swaps (also known as ...
Continue reading
  18619 Hits
  0 Comments
18619 Hits
0 Comments

FX Spots, Forwards, Swaps and Curves in Excel

cover
​Assume you possess Nd units of a currency DOM regarded as domestic currency. For example, you live in the US and hold 1,000 USD, ie. Nd = 1,000 and DOM = USD.For whatever reasons, you want to replace this money with their equivalent number of units Nf of another currency FOR regarded as foreign currency. For concreteness think of FOR like if it we...
Continue reading
  45771 Hits
  0 Comments
45771 Hits
0 Comments

Bond Key Rate Duration (KRD) in Excel: Calculating and Understanding

cover
You are all familiar with the concept of bond Duration, which tells us how much a bond's price changes if its yield goes up or down by a small amount.  In this post I will look into a less common but related concept that goes by the name Key Rate Duration. Table Of Contents​ Understanding the Duration ConceptDefinition and Rationale of Ke...
Continue reading
  20231 Hits
  0 Comments
20231 Hits
0 Comments

Risk free Yield Curve Building in Excel using Negative Overnight Index Swap (OIS) Rates

cover
Long are the dates when all-purpose risk free yield curves were produced out of deposits, futures and swaps. Since the credit crisis of 2007/08, banks realized they should use different curves for different purposes. In the intervening years, quants have proven mathematically that uncollateralized contingent flows should be discounted using a curve...
Continue reading
  8606 Hits
  0 Comments
8606 Hits
0 Comments

Overnight Index Swap (OIS): Pricing and Understanding using Excel

cover
Overnight Index Swaps (OIS) may be priced in Excel using the free and open source derivatives analytics QuantLib library through the Deriscope Excel interface.  Table Of Contents​ Overnight Index Swap (OIS) Description Mathematical Calculation of the OIS Present ValueCreating a 5-year OIS object in Excel in less than 18 seconds Getti...
Continue reading
  39342 Hits
  0 Comments
39342 Hits
0 Comments

How to compute the VaR: Step-by-Step Excel Guide

cover
The purpose of this article is to show you step-by-step how you can calculate the Value at Risk (VaR) of any portfolio by generating all simulation samples in the spreadsheet. This is great for understanding what's going on but it becomes too complex and slow when the number of samples generated by the simulation exceeds 100.If you don't ...
Continue reading
  28830 Hits
  0 Comments
28830 Hits
0 Comments

Pricing of Risk Scaling Options in Excel

cover
You are all familiar with simple European call options that are securities promising to pay their holders at some agreed future time T the difference S(T) – K between the price S(T) realized by a monitored underlying S at time T and a fixed amount K, under the condition that S(T) is greater than K. The last long sentence may be compressed to t...
Continue reading
  3910 Hits
  0 Comments
3910 Hits
0 Comments

Market Data in Excel from IEX

cove_20181001-153146_1
Important Note: Live Feeds are now part of the XL Feeds Excel Add-In. Visit the XL Feeds web site for details. Some or all of the information below may still apply on the new Add-In.IEX (Investors Exchange) is a new United States national stock exchange launched in New York in September 2016. Since February 2017 IEX publishes free of...
Continue reading
  7708 Hits
  0 Comments
7708 Hits
0 Comments

Market Data in Excel from TrueFX

cover
Important Note: Live Feeds are now part of the XL Feeds Excel Add-In. Visit the XL Feeds web site for details. Some or all of the information below may still apply on the new Add-In. TrueFX is a provider of real time forex rates between the most liquid currencies. Both current prices and bid/offer quotes are streamed as quoted by various brokers.&n...
Continue reading
  7020 Hits
  0 Comments
7020 Hits
0 Comments

Market Data in Excel from Alpha Vantage

cover
Important Note: Live Feeds are now part of the XL Feeds Excel Add-In. Visit the XL Feeds web site for details. Some or all of the information below may still apply on the new Add-In.Alpha Vantage is a provider of live and historical prices of global stocks, indices and forex rates (both fiat and crypto) as well as time series of statistical in...
Continue reading
  10754 Hits
  0 Comments
10754 Hits
0 Comments

Market Data in Excel from Barchart

cover
Important Note: Live Feeds are now part of the XL Feeds Excel Add-In. Visit the XL Feeds web site for details. Some or all of the information below may still apply on the new Add-In. Barchart is – among else - a provider of live and historical prices of global stocks, indices, mutual funds, futures, options and forex rates (both fiat and crypt...
Continue reading
  6175 Hits
  0 Comments
6175 Hits
0 Comments

Market Data in Excel from Yahoo Finance

cover
Important Note: Live Feeds are now part of the XL Feeds Excel Add-In. Visit the XL Feeds web site for details. Some or all of the information below may still apply on the new Add-In.Yahoo Finance is a provider of market information that includes live and historical prices of global stocks, indices, mutual funds, futures, options, commodities a...
Continue reading
  24836 Hits
  0 Comments
24836 Hits
0 Comments

Market Data in Excel from World Trading Data

cover
Important Note: Live Feeds are now part of the XL Feeds Excel Add-In. Visit the XL Feeds web site for details.*********************************************************************************************** Deprecated! This service provider has been shut down! ******************************************************************************************...
Continue reading
  8256 Hits
  0 Comments
8256 Hits
0 Comments

Yahoo Finance Live Feeds in Excel after their API Discontinuation in November 2017

cover
Important Note: Live Feeds are now part of the XL Feeds Excel Add-In. Visit the XL Feeds web site for details. Some or all of the information below may still apply on the new Add-In. On the fateful Wednesday of November 1st, 2017 Yahoo decided to stop their – until then – free service of delivering real time market data as a text stream through a s...
Continue reading
  3476 Hits
  0 Comments
3476 Hits
0 Comments

Using Quantos to gain exposure on Foreign Assets without the Currency Risk: Yes, but be careful!

cover
A common usage of quanto call options is to benefit from the price appreciation of a foreign asset and on the same time stay immune on exchange rate variations.A European style quanto call option on a foreign asset S with strike K is always cash settled and pays its holder on expiry time T the amount max{S(T) – K, 0} converted into domestic currenc...
Continue reading
  8392 Hits
  0 Comments
8392 Hits
0 Comments

USD Swaption Pricing in Excel using SABR Stochastic Volatility and Market Vol Cube from CME

cover
The pricing of exotic interest rate products cannot ignore the so called market volatility cube that is made daily available by several swaption brokers.Traders often use the SABR Stochastic volatility model in order to estimate vols off the provided grid.In this article I will show you how to price an out-of-the-money swaption by applying SABR cal...
Continue reading
  14550 Hits
  0 Comments
14550 Hits
0 Comments

USD Swaption Pricing in Excel using the Bachelier Model and Market Normal Vols from CME

cover
The Chicago Mercantile Exchange (CME) clears European swaption trades on 3-month USD LIBOR since April 2016 and has thus become the first major exchange that lists Over-The-Counter (OTC) interest rate products with optionality.The standardized swaption contracts have 5 different expiries - 1M, 3M, 6M, 1Y, 2Y – and 7 underlying swap tenors - 1Y, 2Y,...
Continue reading
  21487 Hits
  0 Comments
21487 Hits
0 Comments

Parametric Yield Curve Fitting to Bond Prices under constraints: The National Bank of Georgia case

cover
Both the Nelson Siegel method and its Svensson extension are very popular among central and other banks when the time spectrum of interest rates needs to be derived from market bond prices. If you are interested in non-parametric methods favored by relative value traders as they provide an exact fit to observed bond prices, these have been demonstr...
Continue reading
  7174 Hits
  0 Comments
7174 Hits
0 Comments

Accessing and Manipulating Historical Data from Yahoo Finance in Excel

cover
Important Note: Live Feeds are now part of the XL Feeds Excel Add-In. Visit the XL Feeds web site for details. Some or all of the information below may still apply on the new Add-In.Yahoo Finance displays historical data for stock prices, indices, bond yields, fx rates, commodity prices etc on their website and allow anyone to download a text file ...
Continue reading
  15326 Hits
  0 Comments
15326 Hits
0 Comments

Asian Option Pricing in Excel using QuantLib: Monte Carlo, Finite Differences, Analytic models for Arithmetic and Geometric Average. Example with live EUR/USD rate

cover
Asian options come in different flavors as described below, but to the extent they have European exercise rights they can be priced by QuantLib using primarily Monte Carlo, but under certain circumstances using also Finite Differences or even analytic formulas.  Table Of Contents​ Asian Option Description Creating all four types of Asian ...
Continue reading
  11486 Hits
  0 Comments
11486 Hits
0 Comments

Swaption Pricing in Excel: 14 Free QuantLib Models plus Implied Volatility Surface and Cube

cover
Most people are unaware of the fact that free and open source QuantLib comes with a great variety of modelling approaches when it comes to pricing an interest rate European swaption in Excel that surpasses what is offered by expensive commercial products.In fact, 14 different modelling approaches are implemented, whereby the Black approach does not...
Continue reading
  18205 Hits
  0 Comments
18205 Hits
0 Comments