Try ResolutionPro today

ResolutionPro is an analytical toolkit for Excel for derivatives valuation and risk management.

Includes;

    • derivative pricing calculators 
    • full function documentation
    • scenario analysis
    • examples

Free demo version is available.

describe the image

Browse by Tag

Follow Me

Subscribe by Email

Your email:

DerivativePricing.com Blog

Current Articles | RSS Feed RSS Feed

Resolution Pro gets a makeover

  
  
  
Resolution

Since the Resolution Pro product was first developed and released to the financial world in 2002 there have been several iterations of Windows operating systems and Microsoft Office. For some time the development team at Hedgebook Ltd (formerly Resolution Financial Software) has been working hard to ensure that the Resolution Pro derivative pricing and valuation Excel addin remains at the forefront of the complex end of the financial product spectrum. The compatibility bugs of Resolution Pro with more recent versions of Windows and MS Office have been addressed and resolved, while the look and feel of the product has also been aligned with the latest versions of Excel. So what has changed?

-          Compatible with all versions of Excel (2002 or newer). The Resolution Pro installer automatically detects what the latest version of Excel installed on your computer is and installs the appropriate versions of the files.

-          Compatible with Windows XP, Vista, 7 or 8.

-          The installer automatically registers the addin with Excel. If you create a new workbook from a template, save it and open it up later all the combo-boxes will be filled with the correct dropdowns and all the functions will calculate without having to re-initiate the addin. The Ribbon or Menu Bar will always be available. This can be manually disabled if the user prefers.

-          The time to load the addin is quicker.

-          We have simplified the shopping cart options when a new user has trialled Resolution Pro and is ready to purchase. More tailored options can be accommodated – simply get in touch at enquiry@myhedgebook.com. Purchase is made via the Licensing button on the Resolution Pro Excel ribbon/menu after downloading the product.

-          Payment for the Resolution Pro license can now be made on line through a Paypal account or with a credit card via Paypal, simplifying and speeding up the process to put the power of Resolution Pro into users’ hands.

Existing licensed users should contact us via email to obtain new license/activation codes to replace old ones. Note that if you have custom templates that have been previously created, you will have to update the links: Data Ribbon->Edit Links->Change Source and point it to Resolution.xlam. Furthermore, it is recommended that old versions of Resolution should be uninstalled before installing the new version as side by side installs can be problematic.

What hasn’t changed within Resolution Pro is the ease of use and the accuracy of the functions and templates that allow users to intuitively value, stress and model a vast library of derivative products. Whether using one of the 84 pre-designed templates or embedding the functions into users’ own spreadsheets and models, the product continues to offer the flexibility to cater to any user requirement. Furthermore, Resolution Pro is fully supported by in depth help files which are just a click away.

Interest Rate Swap Tutorial, Part 5 of 5, building your swap curve

  
  
  
Resolution

This is the fifth in a series of articles that will go from the basics about interest rate swaps, to how to value them and how to build a zero curve.

  1. Introduction to Interest Rate Swaps
  2. Fixed legs
  3. Floating legs
  4. Swap Curve building Part I
  5. Swap Curve building Part II

Swap Curve

In the final article in this series, we will continue to build out our discount factor curve using longer dated par swap rates. Par Swap rates are quoted rates that reflect the fixed coupon for a swap that would have a zero value at inception. 

Let look at our zero curve that we have built so far using LIBOR rates.

zero curve

We are now going to build out this curve out to 30 years using par swap rates. These rates are as of Nov 10, 2011, and reflect USD par swap rates for semi-annual LIBOR swaps. The daycount convention is 30/360 ISDA.

par swap rates

Also keep in mind that these rates reflect the settlement conventions, so the one year rate is for an effective date of Nov 14, 2011 and termination of Nov 14, 2012. If we were to price a one year swap from the curve we have built so far, we can derive the 6mo discount factor, but we are currently missing the 1year factor. Since we know the swap should be worth par if we receive the principal at maturity, then the formula for a one year swap is:

1 year par swap rate resized 600

Notice that the T's would be adjusted for holidays & weekends and are calculated using the appropriate discount factor. We can rearrange our formula to solve for df(1year).

swap bootstrapping

Using our example data:

discount factor par swap

We calculate the missing discount factor as: 0.99422634. But, this for a swap which settles on November 14th, and we are building our curve as of November 10th. So we need to multiple this by the discount factor for November 14th to present value the swap to November 10th. So the discount factor we use in our curve for Nov 14, 2012 is 0.9942107.

We continue by calculating discount factors for all the cashflow dates for our par swap rates. The next step is to calculate the discount factor for May 14, 2013. Our first step is to calculate a par swap rate for this date as it is not an input into our curve. We linear interpolate a rate between our 1 year and 2 year rates. 

1.5 year par swap rate = 1 year + (2 year - 1 year)/365 x days 

= .58% + (.60%-.58%)/365 x 181 = 0.589918%

We now can solve for the missing discount factor, continuing our bootstrapping through the curve.

zero curve construction

 You can download ResolutionPro for a free trial to use our curve building functions and for interest rate swap pricing. 

describe the image

 

Spread options

  
  
  
Resolution

Spread options have a payoff determined by the difference between the prices of two assets and a fixed strike price. These options are often used in commodity markets to hedge the spread between two different underlyings such as WTI vs BRENT oil contracts, can be known as Crack Spread options if there were on Oil vs a derived product such as Heating Oil. 

Similarly, for power generators they may use Spark Spread Options to price options that are based on the the various inputs that can be used to create electrictity.

Spread Option Payoff

spread option payoff

Where;

S1 = Asset 1

S2 = Asset 2

X = Strike

Spread option valuation

Resolution uses the Kirk (1995) approach to value Spread options. If you'd like to try the Spread Option Calculator in ResolutionPro, click here

where

The volatility of  can be approximated by

where

c = Price of European call

p = Price of European put

F1 = Price on futures contract one

F2 = Price on futures contract two

X = Strike price

T = Time to maturity

r = risk free rate

= Volatility of future one

= Volatility of future two

= Correlation between the two contracts

N = The cumulative normal distribution function

Interest Rate Swap Tutorial, Part 4 of 5, swap curve construction

  
  
  
Resolution

swap curveThis is the fourth in a series of articles that will go from the basics about interest rate swaps, to how to value them and how to build a zero curve.

  1. Introduction to Interest Rate Swaps
  2. Fixed legs
  3. Floating legs
  4. Swap Curve building Part I
  5. Swap Curve building Part II

Zero Curve

In the previous articles we described basic swap terminology, created coupon schedules and calculated fixed and floating coupon amounts. We also present valued our cashflows and calculated forward rates from our Zero Curve. A zero curve is a series of discount factors which represent the value today of one dollar received in the future. 

In this article we are going to build up the short end of our discount factor curve using LIBOR rates. 

Here are the rates we are going to use. They represent USD Libor as of November 10, 2011.

ON

0.1410%

T/N

0.1410%

1W

0.1910%

2W

0.2090%

1M

0.2490%

2M

0.3450%

3M

0.4570%

4M

0.5230%

5M

0.5860%

6M

0.6540%

7M

0.7080%

8M

0.7540%

9M

0.8080%

10M

0.8570%

11M

0.9130%

Our first step will be to calculate the start & end dates for each of our LIBOR. Our TN settles in one day, and the other rates all settle in two days. We also will need to calculate the exact number of days in each period. Keep in mind that November 12th was a Saturday so our TN rate ends on the Monday, November 14th. 

libor curve

Our formula for converting rates (simple interest) to discount factors is

simple interest discount factor

Where R is our LIBOR rates and T is our time calculated by the appropriate daycount convention, which in this case is Actual/360.

So our first discount factor reflecting the overnight rate is:

overnight rate

which equals: 0.999996083348673.

Bootstrapping

For our subsequent rates, they settle in the future. So when we calculate their discount factors, we will need to discount again from their settle date. See the image below to see the time frame each rate represents.

zero curve bootstrapping

Because we need the previous discount factors to calculate the next discount factor in our curve, the process is known as bootstrapping

To calculate the discount factor for TN:

TN rate LIBOR

Which equals; 0.999988250138061 x 0.999996083348673 = 0.999984333532754

We continue the process for each time period, to build up the short end of our curve.

libor discount factors

We have shown how to convert LIBOR rates into a discount factor curve, while taking into consideration the settle dates of the LIBOR rates. 

Next Article: Building the long end of the curve using Par Swap Rates.

Quanto Options

  
  
  
Resolution

What are Quanto options?

Quanto options (or cross-currency derivatives) are cash options that have an underlying asset denominated in a "foreign" currency, but settle in a the "domestic" currency at a fixed exchange rate. This limits the foreign exchange exposure for the holder of the option. 

For example, an option on the Nikkei stock index held by an investor in the USA. The underlying index and strike would be in JPY but the investor would receive USD based on the fixed exchange rate should the option be in-the-money at expiry. 

Quanto option pricing formula

Option value in domestic currency

quanto options

Option value in foreign currency

quanto options

where

quanto options

where

S* = Underlying asset price in foreign currency.

X* = Delivery price in foreign currency.

r = Domestic risk free rate.

rf = Foreign risk free rate.

q = Instantaneous proportional dividend payout rate of the underlying asset.

E = Spot exchange rate in units of domestic currency per unit of foreign currency.

E* = Spot exchange rate in units of the foreign currency per unit of domestic currency.

= Volatility of the underlying asset.

= Volatility of the domestic exchange rate.

= Correlation between asset and the domestic exchange rate.

 

ResolutionPro has Quanto option calculators. Try the free trial today.

free-trial

Swaptions

  
  
  
Resolution

swaptionsWhat is a swaption?

A swaption provides the holder with the right, but not the obligation, to enter into a specific swap deal on a future date (or set of dates). As such, swaptions provide an alternative to forward swaps in the same way that currency options provide an alternative to forward FX deals. While the forward swap does not involve any up-front cost, it does obligate the counterparty to enter the swap agreement even when it turns out that the counterparty would be worse off by doing so. Swaptions on the other hand require the payment of an initial premium but give the holder discretion as to whether the option will be exercised.

Example use of a Swaption

To see the benefit of the flexibility provided by a swaption, consider the situation faced by company A that needs to borrow $10M at LIBOR plus 100 basis points in six months time and intends to enter into a swap agreement at that time to effectively convert the floating rate loan into a fixed loan. The uncertainty that faces company A is that they do not know what the appropriate fixed rate for the swap will be when the deal is eventually made. The effective funding cost is therefore also uncertain and will remain so until the swap is entered in six months time.

One way to eliminate this uncertainty is to purchase an appropriately structured swaption. Lets say that company A could enter into a swaption agreement that gives them the right to receive LIBOR and pay a fixed rate of 7.5% starting in six months time. If the swap rate in six months time turns out to be greater than 7.5%, then company A would be best suited by exercising their option to enter into a swap where they are only required to pay 7.5%. Conversely, if the market swap rate turns out to be lower than 7.5%, then company A should allow the swaption to expire and simply enter into a swap at the lower fixed rate. Either way, the fixed rate of the desired swap is guaranteed to be no higher than 7.5% and company A are therefore assured that the net funding cost of the proposed loan will not exceed 8.5%.

The reduction in uncertainty that is associated with the swaption has value to company A, and a range of option pricing models can be used to estimate an exact swaption premium. However, before we consider a mathematical representation of the model, we might note that the value of the swaption to company A will be higher when:

1. the fixed rate of the swap prescribed by the swaption contract is substantially lower than the fixed rate that is currently expected to prevail in the market at the time the swaption will expire. This will allow company A to pay a lower fixed rate than the likely prevailing market rate when the swap is initiated, and is obviously valuable from company A's point of view. In standard option pricing terms, this is a situation where a call option is well in the money

2. the uncertainty surrounding the fixed rate that will prevail in the market when the swaption matures is greater. The greater the uncertainty, the higher the value of an instrument that is structured to eliminate that uncertainty. Like standard options, this factor is simply reflecting the positive relationship between the option value and the volatility of the underlying asset.

Types of Swaptions

For valuation purposes, we also need to distinguish between the two broad types of interest rate swaptions. An option that gives the right to pay fixed and receive floating is called a payer swaption, while those that give the right to receive fixed and pay floating are described as receiver swaptions. In general option pricing terms, the former swaption type can be thought of as a call option, and the receiver swaption treated as a put option.

European Swaption - a swaption where the holder has the right to enter into a swap as of a single fixed date.

Bermudan Swaption - a swaption where the holder has the right to enter into a swap determined by a set schedule of defined exercise dates.

Cancellable swap - a swap with an embedded Bermudan swaption where the holder can cancel the swap on a one of the set exercise dates. 

Interest Rate Swap Tutorial, Part 3 of 5, Floating Legs

  
  
  
ResolutionThis is the third in a series of articles that will go from the basics about interest rate swaps, to how to value them and how to build a zero curve.interest rate swap
  1. Introduction to Interest Rate Swaps
  2. Fixed legs
  3. Floating legs
  4. Swap Curve building Part I
  5. Swap Curve building Part II

Interest Rate Swap Example

For our example swap we will be using the following inputs:
  • Notional: $1,000,000 USD
  • Coupon Frequency: Semi-Annual
  • Fixed Coupon Amount: 1.24%
  • Floating Coupon Index: 6 month USD LIBOR
  • Business Day Convention: Modified Following
  • Fixed Coupon Daycount: 30/360
  • Floating Coupon Daycount: Actual/360
  • Effective Date: Nov 14, 2011
  • Termination Date: Nov 14, 2016
  • We will be valuing our swap as of November 10, 2011.
In the previous article we generated our schedule of coupon dates and calculated our fixed coupon amounts.

Calculating Forward Rates

To calculate the amount for each floating coupon we do the following calculation:


Floating Coupon = Forward Rate x Time x Swap Notional Amount


Where:


Forward Rate = The floating rate determined from our zero curve (swap curve)
Time = Year portion that is calculated by the floating coupons daycount method.
Swap Notional = The notional amount set in the swap confirmation.


In the next couple articles we will go through the process of building our zero curve that will be used for the swap pricing. In the meantime we will use the following curve to calculate our forward rates and discount our cashflows.

swap zero curve


The numbers at each date reflect the time value of money principle and reflect what $1 in the future is worth today for each given date.


Let's look at our first coupon period from Nov 14, 2011 to May 14, 2012. To calculate the forward rate which is expressed as a simple interest rate we use the following formula:
simple interest formula
where:

forward rate discount factor


Solving for R
forward rate formula

 

 

In our example we divide the discount factor for May 14, 2012 by the discount factor for Nov 14, 2011 to calculate DF.


 0.9966889 / 0.9999843 = 0.9967046

T is calculated using Actual/360. The number of days in our coupon period is 182. 182/360 = 0.505556

R = (1 - 0.9967046) / (0.9967046 x 0.505556) = 0.654%

Our first coupon amount therefore is:

Floating Coupon = Forward Rate x Time x Swap Notional Amount

 

$ 3,306.33 = 0.654% x 0.505556 x $1,000,000

 

Below is a table with our forward rate calculations & floating coupon amounts for the rest of our coupons.

 

swap forward rates

The final step to calculate a fair value for our complete swap is to present value each floating coupon amount and fixed coupon amount using the discount factor for the coupon date.

 

Present Value of Net Coupon is
(Floating Coupon Amount - Fixed Coupon Amount) x Discount Factor

interest rate swap

Our net fair value of this swap is $ 0.00 as of November 10, 2011. 

 

So far in this tutorial we have gone through basic swap terminology, fixed leg coupon calculations, calculating forward rates for floating leg coupon calculations and discounted our cashflows to value a swap.

What to know about IFRS 7 and IFRS 9.

  
  
  
Resolution

IFRS, International Financial Reporting Standards, has a mission of increasing financial statement readability and disclosure requirements. Profit and loss reporting plus risk management strategies play essential roles in both IFRS 7 and IFRS 9 rules.

 derivatives accounting

IFRS 7 applies to properly disclosing financial transactions, for both recognized and non-recognized financial instruments, on the financial statement. Information must allow readers the ability to assess the performance and position of instruments. The entity decides the amount of detail needed to properly comply with the rule, but must take into consideration the risk of overburdening the reader with meaningless details or having relevant details hidden within an aggregate amount.

 

IFRS 7 requires entities to combine financial instruments into appropriate categories on the financial statement. Instruments are categorized according to disclosure nature and characteristics of the instrument. Having instruments properly classified decreases reader confusion while helping with the reconciliation process.

 

Properly disclosing risks plays an essential role in providing relevant financial information to users. Risk includes market, interest, foreign exchange, price, credit and financial. IFRS 7 requires entities to perform a sensitivity analysis in order to assess market risk on all aspects of business, but states that entities can perform a different sensitivity analysis on each individual financial instrument. Interest risks arise when financial instruments have interest listed on the balance sheet, but other instruments not being included in the aggregate asset total. Interest for each financial instrument must be reflected at fair market value and any fluctuation explanations given within the financial statement footnotes.

 

Foreign exchange risks involves financial instruments being denominated in a foreign currency. This IFRS 7 states that the foreign exchange risk does not apply to non-monetary instruments or instruments denominated in the foreign currency.

 

Entities can disclose price risks involving increases or decreases in prices of raw materials or the market price of equity instruments. Derivatives including interest rate swaps and forward contracts are affected by price risks.

 

Credit risks involve fair market value fluctuations of the entities financial liabilities. IFRS 7 states that entities need to value the instruments at year-end rates, accumulating all instruments into one aggregate figure. If an alternative method of valuation is used, the entity must disclose it in the financial statement footnotes. A full explanation of variances must also be disclosed in the financial statement footnotes.

 

If an entity delivers or relinquishes a financial instrument that contains numerous embedded derivatives whose values depend upon each other and the said instrument also contains components of other assets and liabilities, IFRS 7 states that the entity must disclose the involved characteristics in the financial statements.

 

IFRS 7 states that when derivative contracts including credit derivatives, foreign currency contracts and interest rate swaps reach completion, if the asset has been valued at the fair market rate, the highest credit risks exposure will be equal to the instrument's carrying amount.

 

To make financial statements easier to understand to readers, this IFRS rule suggests that entities may want to divide lump sum cash flows into derivative and nonderivative instruments. This would better match cash inflows with outflows and more fully comply with GAAP requirements.

 

IFRS 9 replaced IAS 39 and must be implemented beginning January 1, 2013. This rule centers on making the measuring and classifying of financial instruments simpler. Specifically, the third phase of IFRS 9 involves hedge accounting, both macro and general. 

Current methods of reporting hedge accounts are reconsidered by IFRS 9. This rule requires entities to perform a reconciliation of credit derivatives used in determining and managing an entity's credit exposure. Full disclosure of the reconciliation between fair value and nominal amount of the derivatives is required.

Interest Rate Swap Tutorial, Part 2 of 5, Fixed Legs

  
  
  
Resolution

This is the second in a series of articles that will go from the basics about interest rate swaps, to how to value them and how to build a zero curve.interest rate swap

  1. Introduction to Interest Rate Swaps
  2. Fixed legs
  3. Floating legs
  4. Swap Curve building Part I
  5. Swap Curve building Part II

Interest Rate Swap Fixed Legs

Now that we know the basic terminology and structure of a vanilla interest rate swap we can now look at constructing our fixed leg of our swap by first building our date schedule, then calculating the fixed coupon amounts.

For our example swap we will be using the following inputs:

  • Notional: $1,000,000 USD
  • Coupon Frequency: Semi-Annual
  • Fixed Coupon Amount: 1.24%
  • Floating Coupon Index: 6 month USD LIBOR
  • Business Day Convention: Modified Following
  • Fixed Coupon Daycount: 30/360
  • Floating Coupon Daycount: Actual/360
  • Effective Date: Nov 14, 2011
  • Termination Date: Nov 14, 2016
  • We will be valuing our swap as of November 10, 2011.

Swap Coupon Schedule

First we need to create our schedule of swap coupon dates. We will start from our maturity date and step backwards in semi-annual increments. The first step is to generate our schedule of non-adjusted dates.

swap coupon dates unadjusted

Then we adjust our dates using the modified following business day convention.

swap coupon dates adjusted

Note that all the weekend coupon dates have been brought forward to the next Monday.

Swap Fixed Coupon Amounts

To calculate the amount for each fixed coupon we do the following calculation:

Fixed Coupon = Fixed Rate x Time x Swap Notional Amount

Where:

Fixed Rate = The fixed coupon amount set in the swap confirmation.

Time = Year portion that is calculated by the fixed coupons daycount method.

Swap Notional = The notional amount set in the swap confirmation.

Below is our date schedule with the Time portion calculated using the 30/360 daycount convention. More on daycounts can be found in this document titled Accrual and Daycount conventions.

Note the coupons which are not exactly a half-year due to the business day convention. If our business day convention was no-adjustment all the time periods would have been 0.5. This is a difference between swaps and bonds, as bonds will generally not adjust the coupon amounts for business day conventions, they will simply be 1/(# coupon periods per year) x coupon rate x principal. 

swap schedule with daycount

The coupon amount for our first coupon will be 1.24% x 1,000,000 x 0.50 = $6,200.00. Below are the coupon amounts for all of the coupons.

swap coupon schedule

Now that we know our coupon amounts, to find the current fair value of the fixed leg we would present value each coupon and sum them to find the total present value of our fixed leg. To do this we calculate the discount factor for each coupon payment using a discount factor curve which represents our swap curve. We will build our discount factor curve later in this tutorial series.  

Next Article: Swap floating legs including calculating forward rates.

Interest Rate Swap Tutorial, Part 1 of 5, terminology

  
  
  
Resolution

This is the first in a series of articles that will go from the basics about interest rate swaps, to how to value them and how to build a zero curve.

  1. Introduction to Interest Rate Swaps
  2. Fixed legs
  3. Floating legs
  4. Swap Curve building Part I
  5. Swap Curve building Part II

Introduction to Interest Rate Swaps

interest rate swaps explained

An interest rate swap is where one entity exchanges payment(s) in change for a different type of payment(s) from another entity. Typically, one party exchanges a series of fixed coupons for a series of floating coupons based on an index, in what is known as a vanilla interest rate swap. 

The components of a typical interest rate swap would be defined in the swap confirmation which is a document that is used to contractually outline the agreement between the two parties. The components defined in this agreement would be:

Notional -  The fixed and floating coupons are paid out based on what is known as the notional principal or just notional. If you were hedging a loan with $1 million principal with a swap, then the swap would have a notional of $1 million as well. Generally the notional is never exchanged and is only used for calculating cashflow amounts. 

Fixed Rate - This is the rate that will be used to calculate payments made by the fixed payer. This stream of payments is known as the fixed leg of the swap

Coupon Frequency - This is how often coupons would be exchanged between the two parties, common frequencies are annual, semi-annual, quarterly and monthly though others are used such as based on future expiry dates or every 28 days. In a vanilla swap the floating and fixed coupons would have the same frequency but it is possible for the streams to have different frequencies. 

Business Day Convention - This defines how coupon dates are adjusted for weekends and holidays. Typical conventions are Following Business Day and Modified Following. These conventions are described in detail here.

Floating Index - This defines which index is used for setting the floating coupons. The most common index would be LIBOR. The term of the index will often match the frequency of the coupons. For example, 3 month LIBOR would be paid Quarterly while 6 month LIBOR would be paid Semi-Annually. 

Daycount conventions - These are used for calculating the portions of the year when calculating coupon amounts. We'll explore these in more detail in our discussions on fixed and floating legs. Details of different daycounts can be found here.

Effective Date - This is the start date of a swap and when interest will start accruing on the first coupon.

Maturity Date - The date of the last coupon and when the obligations between the two parties end. 

 

Next Article: Constructing fixed legs including calculating coupon amounts

 

All Posts