Ioannis Rigopoulos

Detailed profile at my About page

Ioannis Rigopoulos

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
Ioannis Rigopoulos

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
Ioannis Rigopoulos

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
  570 Hits
  0 Comments
570 Hits
0 Comments
Ioannis Rigopoulos

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
  767 Hits
  0 Comments
767 Hits
0 Comments
Ioannis Rigopoulos

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
Ioannis Rigopoulos

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
Ioannis Rigopoulos

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
Ioannis Rigopoulos

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
  4833 Hits
  0 Comments
4833 Hits
0 Comments
Ioannis Rigopoulos

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
Ioannis Rigopoulos

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
  3599 Hits
  0 Comments
3599 Hits
0 Comments
Ioannis Rigopoulos

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
Ioannis Rigopoulos

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
Ioannis Rigopoulos

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
  2097 Hits
  0 Comments
2097 Hits
0 Comments
Ioannis Rigopoulos

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
  8467 Hits
  0 Comments
8467 Hits
0 Comments
Ioannis Rigopoulos

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
Ioannis Rigopoulos

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
Ioannis Rigopoulos

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
  2625 Hits
  0 Comments
2625 Hits
0 Comments
Ioannis Rigopoulos

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
Ioannis Rigopoulos

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
  4316 Hits
  0 Comments
4316 Hits
0 Comments
Ioannis Rigopoulos

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
  1726 Hits
  0 Comments
1726 Hits
0 Comments
Ioannis Rigopoulos

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
  10122 Hits
  0 Comments
10122 Hits
0 Comments
Ioannis Rigopoulos

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
  2902 Hits
  0 Comments
2902 Hits
0 Comments
Ioannis Rigopoulos

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

cover
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 special URL. For hundreds of businesses and individuals who had relied for years on Yahoo's benevolent free service, this single action meant only one thing: Instant death!If all you w...
Continue reading
  929 Hits
  0 Comments
929 Hits
0 Comments
Ioannis Rigopoulos

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
  2772 Hits
  0 Comments
2772 Hits
0 Comments
Ioannis Rigopoulos

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
  5767 Hits
  0 Comments
5767 Hits
0 Comments
Ioannis Rigopoulos

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
  5873 Hits
  0 Comments
5873 Hits
0 Comments
Ioannis Rigopoulos

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.As I have explained in my related article, QuantLib is capable of solving the respective parametric equations treating the model parameters as unknown.Due to the...
Continue reading
  2042 Hits
  0 Comments
2042 Hits
0 Comments
Ioannis Rigopoulos

Accessing and Manipulating Historical Data from Yahoo Finance in Excel

cover
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 with these data for free.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 bel...
Continue reading
  4376 Hits
  0 Comments
4376 Hits
0 Comments
Ioannis Rigopoulos

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.The main feature of an Asian option is that it involves the average of the realized price...
Continue reading
  3829 Hits
  0 Comments
3829 Hits
0 Comments
Ioannis Rigopoulos

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
  6856 Hits
  0 Comments
6856 Hits
0 Comments
Ioannis Rigopoulos

Credit Default Swap (CDS) Pricing in Excel using QuantLib

cover
Free and open source QuantLib supports the precise valuation of Credit Default Swaps (CDS) in Excel. A CDS contract specifies N payment times T1, T2, …, TN at which, counterparty A (the Protection Seller) receives a pre-agreed fixed amount from counterparty B (the Protection Buyer) provided that a pre-defined credit event – such as the default of s...
Continue reading
  4356 Hits
  0 Comments
4356 Hits
0 Comments
Ioannis Rigopoulos

Over 30 Bond Risk Management Functions in Excel: Clean & Dirty Price, Yield, Duration, Convexity, BPS, DV01, Z-spread etc

cover
Free and open source QuantLib is capable of calculating several risk measures associated with the pricing of bonds and allows you to get in Excel quantities like clean and dirty price, duration, convexity, BPS, DO01, Z-spread etc.I have already showed you how to build a yield curve out of clean bond prices using either a parametric or non...
Continue reading
  2738 Hits
  0 Comments
2738 Hits
0 Comments
Ioannis Rigopoulos

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
  4057 Hits
  0 Comments
4057 Hits
0 Comments
Ioannis Rigopoulos

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
  2144 Hits
  0 Comments
2144 Hits
0 Comments
Ioannis Rigopoulos

Parametric Yield Curve Fitting to Bond Prices: The Nelson-Siegel-Svensson method

cover1
When it comes to building a yield curve out of bond prices, QuantLib can handle both non-parametric and parametric methods, both deliverable to Excel through Deriscope.The former have been demonstrated at my previous article.The latter impose a parametric form on some mathematical quantity implied by the produced yield curve.The following parametri...
Continue reading
  16100 Hits
  0 Comments
16100 Hits
0 Comments
Ioannis Rigopoulos

Yield Curve Building in Excel using Bond Prices (QuantLibXL vs Deriscope)

cover
With this article I want to show you how to create a bond yield curve in Excel, i.e. a yield curve out of bond prices, using the open source QuantLib analytics library. I will present both alternative spreadsheet interfaces to QuantLib, which are the QuantLibXL and Deriscope.The main use of a yield curve is that it allows us to calculate the p...
Continue reading
  4025 Hits
  0 Comments
4025 Hits
0 Comments
Ioannis Rigopoulos

Yield Curve Building in Excel using Deposits, Futures and Swaps

cover
With this article I want to show you how to create a yield curve in Excel using the open source QuantLib analytics library, when the input market data are a mixture of deposit rates, futures prices and swap rates.I have already written how you may build a yield curve using a single type of market instruments, such as deposits, futures or swaps...
Continue reading
  2429 Hits
  0 Comments
2429 Hits
0 Comments
Ioannis Rigopoulos

Yield Curve Building in Excel using Swap Rates

cover
With this article I want to show you how to create a yield curve in Excel using the open source QuantLib analytics library, when the input market data are swap rates. I will also show you how to apply dual bootstrapping when an exogenous yield curve is present.For short term maturities – typically less than a year – the yield curve may be built out...
Continue reading
  7295 Hits
  0 Comments
7295 Hits
0 Comments
Ioannis Rigopoulos

Yield Curve Building in Excel using Futures

cover
With this article I want to show you how to create a yield curve in Excel using the open source QuantLib analytics library, when the input market data are futures prices. The futures convexity will be taken into account.I explained how you may build a yield curve in Excel out of forward rates in my previous article.In reality, forward rates are sel...
Continue reading
  3053 Hits
  0 Comments
3053 Hits
0 Comments
Ioannis Rigopoulos

Yield Curve Building in Excel using Forward Rates

cover
With this article I want to show you how to create a yield curve in Excel using the open source QuantLib analytics library, when the input market data are forward rates.My previous article focused on building a yield curve in Excel out of deposit rates in general and Libor rates in particular.These rates cover the short range of the maturity s...
Continue reading
  2711 Hits
  0 Comments
2711 Hits
0 Comments
Ioannis Rigopoulos

Yield Curve Building in Excel using Deposit (LIBOR) Rates

cover
With this article I want to show you how to create a yield curve in Excel using the open source QuantLib analytics library, when the input market data are deposit rates – such as Libor rates -, which are a special type of interest rates called zero rates. As I describe in my article about interest rates, a deposit rate - effectively the interest ra...
Continue reading
  4790 Hits
  0 Comments
4790 Hits
0 Comments
Ioannis Rigopoulos

Beyond Black Scholes: American Option Price Dependence on Dividend Payment Time

cover
With this article I want to show you how to create and price American options on an underlying that pays dividends – such as American stock options expiring after the ex-dividend date - in Excel using the open source QuantLib analytics library.In my previous article I showed you how to calculate the fair price of an American option on an under...
Continue reading
  2976 Hits
  0 Comments
2976 Hits
0 Comments
Ioannis Rigopoulos

Beyond Black Scholes: American Options without Dividends

cover1
With this article I want to show you how to create and price American options on a non-dividend-paying underlying – such as American stock options - in Excel using the open source QuantLib analytics library.America has been traditionally touted as the "land of choice" and American Options honor their name by granting their holders an additional cho...
Continue reading
  2417 Hits
  0 Comments
2417 Hits
0 Comments
Ioannis Rigopoulos

Beyond Black Scholes: European Options with Discrete Dividends

cover
With this article I want to show you how to create and price European options on an underlying that pays discrete dividends – such as European stock options - in Excel using the open source QuantLib analytics library.In my previous article I presented an overview of the QuantLib models that can be used in Excel towards pricing the simplest non-line...
Continue reading
  2280 Hits
  0 Comments
2280 Hits
0 Comments
Ioannis Rigopoulos

Introduction to Deriscope – Part 5: Live Feeds

cover
Overview Deriscope enables you to receive in Excel live feeds from several different providers.These feeds include real time or almost real time prices of financial securities (stocks, ETFs, indices, currencies, cryptocurrencies, futures and options), bid/offer quotes and sizes, time series of historical prices and statistical indicators, comp...
Continue reading
  9954 Hits
  0 Comments
9954 Hits
0 Comments
Ioannis Rigopoulos

Introduction to Deriscope – Part 4: Spreadsheet

FormSplitRanges
In this article I show you how to manipulate the formulas created by the wizard in order to build functional Excel spreadsheets.  Deriscope Formula Syntax Rules​The main spreadsheet formula that Deriscope exposes to Excel is ds(r1, r2, …) where r1, r2, … are ranges containing primarily key-value pairs.Also available are the dsi and dsv variati...
Continue reading
  1832 Hits
  0 Comments
1832 Hits
0 Comments
Ioannis Rigopoulos

Introduction to Deriscope – Part 3: Pricing a Stock Option

SelectPriceScreenshot
In my previous article I showed you how to create a Stock Option object in Excel and how to access the list of functions that apply to that object.As I pointed out, the most important of these functions is the Price, which calculates the fair price of the calling object.The Price function is a so called Local function, in the sense that it is invok...
Continue reading
  1353 Hits
  0 Comments
1353 Hits
0 Comments
Ioannis Rigopoulos

Introduction to Deriscope – Part 2: Creating a Stock Option

Initial
In my previous article I listed the main reasons for using Deriscope.Now I will show you how to use Deriscope to create a Stock Option object. Rather than typing the formula by hand, I will ask the wizard to create the formula for me. After I start Excel and press on the Enable and Show Wizard button inside the Deriscope ribbon tab, the Deriscope t...
Continue reading
  1838 Hits
  0 Comments
1838 Hits
0 Comments
Ioannis Rigopoulos

Introduction to Deriscope – Part 1: Why Deriscope?

cover
Deriscope is described by … Wikipedia at https://en.wikipedia.org/wiki/fascinatingnewproducts/bestever/deriscope as a "wizard-guided Excel Add-In dedicated to the pricing of Financial Derivatives". Well, it is a bit early for such a Wikipedia entry given Deriscope's recent birth date of August 2017. But the above description still applies and makes...
Continue reading
  3107 Hits
  0 Comments
3107 Hits
0 Comments
Ioannis Rigopoulos

Beyond Black Scholes: European Options without Dividends

option-signs
Options are the simplest non-trivial financial derivatives around.They are part of the curriculum of every university course on Finance for a good reason: They are everywhere! They are traded on regulated exchanges around the world, change hands over the counter between … consenting adults, enhance or "infect" all sorts of contracts as "embedd...
Continue reading
  1175 Hits
  0 Comments
1175 Hits
0 Comments
    Previous    
1 2