On this page you can review some excel files and analysis for valuing interest rate swaps, exchange rate swaps and various different real options. The files can be used to value of a swap or an option over time so you can evaluate the cost of terminating the contract. The first set of file are various files on working with options models, futures prices and forward interest rates.
Valuing Interest Rate Swaps when Interest Rates Change
My good friend asked me about swap breakage costs and if I had any models. I told him I had a few old models that demonstrate how to calculate the value of an interest rate swap and exchange rate swap. Some of the excel techniques are horrible in the files below, but if you are interested in the general approach to computing the value of a swap after interest rates and/or exchange rates change, maybe this could help.
I created an exercise where you practice some discounting on a discrete and on a continual basis and derive the value of a fixed rate to floating rate swap with different interest rates. In this exercise you assume that you know the future spot interest rate (like the future LIBOR 3 month rate). The next exercise demonstrates how to find the rate. The two screenshots below illustrate what you should see when you open the file. The first screenshot is the blank exercise file and the second screenshot illustrates the completed exercise.
Excel File with Exercise on How to Compute the Value of an Interest Rate Swap Given Interest Rates
The file below shows how to construct spot rates from the yield on bonds with different maturities. The process is a bootstrapping method where you first find the yield to maturity on a short-term government bond. Then you use this rate to find the implied spot rate on the next maturity of a bond (e.g. a 3 month and a six month bond). Then once you have the maturity for the 6 month bond you continue the process. Because you want monthly rates, in this file you need some interpolation and I used a very crappy old fashion method. One day I will update the file. But the key is that you can use the file to come up with spot interest rates and forward interest rates that are used in computing the swap value. The screen shots illustrate a couple of pages from the file (sorry about the old interest rate data). The first screenshot shows the inputs for yields from different bonds on the first page of the workbook. The second file shows the way you press a stupid button to do the interpolation. The third screenshot shows the resulting spot rates and forward rates.
If you want to get up to date interest rates you can used the second file below that has links to the FRED interest rate data. One day I will make the whole thing automated.
Excel File with Term Structure of Interest Rates using Bond Yields and Deriving Spot Rates
The interest rate database is available for download by pressing the button below. The interest rate database attached to the button may not be current to the latest month. To update the database you can follow the instructions in the section below.
The next file is an example of computing swap value once you have the interest rates. It is not an exercise file, but a file where you can press a goal seek and derive the swap rate. After you have the swap rate you can then compute how the value of the swap changes if the interest rate changes. The screenshot below demonstrates how to you can press a button and get the swap value. You can then change interest rates and see how the value of the interest rate swap changes. The first screenshot and the the first file demonstrate the case with periodic compounding of interest rates. The second screenshot and the second file show the case with continual compounding.
The next screenshots and files demonstrate how to compute the value of an exchange rate swap that depends on the interest rates in two countries and the exchange rates in two countries. As with the interest rate swaps you need forward interest rate and forward exchange rate data making the valuation somewhat more complex. The first screenshot demonstrates what you should see when you open the first file and where you can see the value of the exchange rate swap at the bottom of the bottom of the blue section (sorry about the stupid colours — I did this a long time ago). The second screenshot and file is similar to the first file that values the exchange rate swap, but it applies continual compounding.
Excel File that Demonstrates Value of Exchange Rate Swap that Applies Continual Compounding Formulas
The screenshot below illustrates the process for computing the forward rate for a commodity price where you can store the commodity.
Using Option Models for Evaluating Credit Spreads, PD and LGD
A few years ago it was popular to try and measure credit spreads using option pricing theory as debt can be considered a sold put option. The credit spread is the option premium or price, the termination date of the option is the duration of the debt and the volatility of cash flows drives the value of the option.
The first file below illustrates how you can use the option pricing method to either start with a credit spread and derive the probability of default or start with volatility and back compute the credit spread. This is not very practical but is a good way to think about what should really drive the value of an option.
Financial Model Used to Illustrate Results of Merton Model with Monte Carlo Simulation Using Macro
Excel File with Merton Model for Computing Credit Spreads with Completed Formulas and Analysis
Option Pricing Model Exercises
Excel File with Demonstration of Creating Option Arbitrage with Mix of Risk Free Securities
You can see that I have not yet transferred the files from the old website. I will do this and explain what is in the files.
Real Option Analysis
The second set of files addresses real options. The order of the files associated with real options correspond to the discussion in Chapter 5 of the Valuation Mirage text. The file below contains exercises that walk through the mechanics of measuring the value of the option to expand an investment
File that walks through how to simulate exploration or research option in various stages with given probabilities to cancel at different stages. You input stage lengths, probabilities of the stages being successful and the costs of each stage as well as the ultimate cash flow if the whole project is successful. You can then evaluate the relative importance of each expenditure.
File that simulates development option to cancel with volatility and mean reversion. The tricky part is to evaluate the value of the investment project in different scenarios without looking ahead.
File that simulates delay option at after development. The difficult part is re-evaluating the question of whether the project should be delayed and re-computing the economics of the project with different delay periods.
File that simulates the option to cancel at different stages of construction and opeartion. The general idea is similar to contracting option except that it must be evaluated on a dynamic basis which makes the proceess more complex.
File that walks through how to create Black-Scholes and Black model for standard financial options.
File that includes the binomial tree approach to modelling options.