Resources

Blog-style articles that help you understand derivatives better by pricing them in Excel.

All available articles about Derivatives Pricing in Excel through Deriscope.

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
  181 Hits
  0 Comments
181 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
  1139 Hits
  0 Comments
1139 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
  569 Hits
  0 Comments
569 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
  766 Hits
  0 Comments
766 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
  1503 Hits
  0 Comments
1503 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
  886 Hits
  0 Comments
886 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
  1165 Hits
  0 Comments
1165 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
  4832 Hits
  0 Comments
4832 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
  1940 Hits
  0 Comments
1940 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 know...
Continue reading
  3598 Hits
  0 Comments
3598 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
  15580 Hits
  0 Comments
15580 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.While there exist several different Duration definitions that produce slightly different results, the one that is most closely related to the Key Rate Duration is the Modified Duration D, which may be...
Continue reading
  5384 Hits
  0 Comments
5384 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
  2096 Hits
  0 Comments
2096 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
  8465 Hits
  0 Comments
8465 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
  5961 Hits
  0 Comments
5961 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
  1199 Hits
  0 Comments
1199 Hits
0 Comments

Market Data in Excel from IEX

cove_20181001-153146_1
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 charge their trading data in real time. These include live and historical prices of over 8,000 US stocks and mutual funds plus financial information and news.If all you want is a very quick a...
Continue reading
  2624 Hits
  0 Comments
2624 Hits
0 Comments

Market Data in Excel from TrueFX

cover
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. If all you want is a very quick and uncomplicated way (three mouse clicks!) to simply get live feeds in your spreadsheet, check out the 30 seconds videos below:Getting easily live...
Continue reading
  1665 Hits
  0 Comments
1665 Hits
0 Comments

Market Data in Excel from Alpha Vantage

cover
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 indicators. If all you want is a very quick and uncomplicated way (three mouse clicks!) to simply get live feeds in your spreadsheet, check out the 30 seconds videos below:Getting...
Continue reading
  4315 Hits
  0 Comments
4315 Hits
0 Comments

Market Data in Excel from Barchart

cover
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 crypto). If all you want is a very quick and uncomplicated way (three mouse clicks!) to simply get live feeds in your spreadsheet, check out the 30 seconds videos below:Getting easily...
Continue reading
  1724 Hits
  0 Comments
1724 Hits
0 Comments

Market Data in Excel from Yahoo Finance

cover
Yahoo Finance is a provider of market information that includes live and historical prices of global stocks, indices, mutual funds, futures, options, commodities as well as forex (both fiat and crypto) and US treasury bond rates. If all you want is a very quick and uncomplicated way (three mouse clicks!) to simply get live feeds in y...
Continue reading
  10119 Hits
  0 Comments
10119 Hits
0 Comments

Market Data in Excel from World Trading Data

cover
World Trading Data is a provider of live and historical prices of global stocks, indices, US mutual funds and forex rates. If all you want is a very quick and uncomplicated way (three mouse clicks!) to simply get live feeds in your spreadsheet, check out the 30 seconds videos below:Getting easily live stock prices in ExcelGetting easily l...
Continue reading
  2900 Hits
  0 Comments
2900 Hits
0 Comments