Resources

Blog-style articles that help you understand derivatives better by pricing them in Excel.
If you choose a category from the list on the left and click on the appearing envelope icon, you will be notified by email when a new article is posted in that category.

Includes articles relating to working with interest rates and their derivatives in Excel through Deriscope

Subcategories from this category:

Libor Transition, Swaptions, OIS

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
  2073 Hits
  0 Comments
2073 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
  1545 Hits
  0 Comments
1545 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
  2262 Hits
  0 Comments
2262 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
  1912 Hits
  0 Comments
1912 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
  1429 Hits
  0 Comments
1429 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
  2485 Hits
  0 Comments
2485 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.There, I had defined the Carry and Roll-Down CR as:CR = PV(CFinterm) + PV(T) - PV(T0)where CFinterm stands for the cash flows received between today's date T0 and some future horizon date T (including T, but not T0)PV(CF...
Continue reading
  8091 Hits
  0 Comments
8091 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
  2586 Hits
  0 Comments
2586 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
  5268 Hits
  0 Comments
5268 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
  12824 Hits
  0 Comments
12824 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, which is defined as the total amount earned (realized + unrealized) by holding a swap up to a...
Continue reading
  38000 Hits
  0 Comments
38000 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
  5495 Hits
  0 Comments
5495 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
  15395 Hits
  0 Comments
15395 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
  18503 Hits
  0 Comments
18503 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.An OIS contract is very similar to a plain vanilla interest rate swap, the only difference being that each payment in the floating leg is calculated according to a floating number F that equa...
Continue reading
  31802 Hits
  0 Comments
31802 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
  12930 Hits
  0 Comments
12930 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
  19062 Hits
  0 Comments
19062 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
  15771 Hits
  0 Comments
15771 Hits
0 Comments

Excel Builder and Cash Flow Viewer for Non-Standard Interest Rates Swaps

cover
Building, pricing and analyzing even non-standard interest rate swaps in Excel becomes a simple exercise when the Deriscope interface to the open source QuantLib analytics library is employed.We have already encountered a simple interest rate swap contract in the Yield Curve Building in Excel using Swap Rates article, where vanilla swaps were used ...
Continue reading
  8428 Hits
  0 Comments
8428 Hits
0 Comments

Time for a coffee break? Understanding Time and its implications on Interest Rates

Cover
With this article I want to give you an intuitive feeling of the concept of interest rate and also show you how to work with various types of interest rates – such as a compounded interest rate - in Excel as accurately as market professionals do.Contrary to the four-dimensional space-time of relativistic physics, financial stochastic processes evol...
Continue reading
  6239 Hits
  0 Comments
6239 Hits
0 Comments