Pricing European Swaptions with Excel

This article introduces introduces interest-rate options,or Swaptions, and provides a pricing spreadsheet. They are popular with institutions that have cash-flow requirements which are affected by interest rates.

A swap is a financial instrument in which two parties exchange cash flow streams. For example, borrowers at a floating rate can swap to a fixed rate to make costs predictable.

A swaption is simply an option that gives the holder the right (but not the obligation) to exchange one cash flow stream for another. They are often described by FRA notation; for example, a 2×3 swaption gives the holder an option that matures in two years, with the right to enter a three-year swap.  The time to maturity is known as the period (in this case, two years) and the length of the swap is called the tenor (in this case, three years)

Two types are generally traded.

  • A payer swaption. These are similar to a call option on a bond and gives the holder the right to enter a swap as a fixed-rate payer and the floating-rate receiver. These are exercised if the fixed rate is greater than the strike rate.
  • A receiver swaption. These are similar to a put option on a bond and gives the holder the right to enter a swap as a floating rate-payer and a fixed-rate receiver. These are exercised if the fixed rate is less than the strike rate.
The floating rate is often the LIBOR spot rate.

Additionally, swaptions have several different styles.

  • European (which can only be exercised at maturity). These are usually valued with the Black model (which offers a closed-form analytical solution), and are the most common type.  The assumption of constant, however, volatility limits its validity
  • American (which can be exercised at or before maturity). These are more difficult to value, and several techniques have been proposed. For example, two-factor stochastic methods, and trinomial trees
  • Bermudan (which can only be exercised at specific dates before maturity). The pricing of this variant is complex; this paper summarizes several numerical methods.
A swaption straddle is a trading strategy that involves buying a payer and receiver option on the same floating rate.  If the floating rate falls, the holder receives the fixed rate. However, if the floating rate rises, the holder pays the fixed rate.  If the floating changes by only a small amount, the cost of purchasing the payer and receiver option outweighs any gains.

Pricing a European Swaption in Excel

European Swaption in Excel

This Excel spreadsheet employs the Black (1976) model to price European interest rate options. All of the calculations are exposed to ensure clarity.

Download Excel Spreadsheet to Price European Swaption (Black, 1976)

One thought on “Pricing European Swaptions with Excel

  1. Isn’t a payer swaption more similar to a put option on a bond? Payer swaptions increase in value when rates rise and a put on a bond also increases in value when rates rise since the bond price decreases with a rise in rates. Or, am I not looking at this correctly? Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 6 + 10 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)