In a previous post we discussed the new paradigm of Swap pricing throughout the use of collateralization. We now propose a simple VBA code to price a Cross-Currency Swap. 


A Cross Currency Swap is similar to a simultaneous position into two Bonds with two different currencies. Notionals will be exchanged at Swap Start Date and then will be paid back at maturity. Like in any Bond position coupons will be paid or received in between.

As we will see Cross Currency Swaps are very useful when it comes to hedge a Bond issued in a foreign currency. Let’s take the example of the Cross Currency Swap priced in the VBA spreadsheet in attachment.

Standard Use Case

A corporate treasurer of a European industrial company has issued a 30M Bond for 7 years with a 5% coupon in USD for a specific investment in the United States. The decision has been done in January 2010 for a forward starting investment in June 2010 ending in June 2017. The treasurer will then borrow this money in the USD Bond Market and pay the interests on a semi-annual basis.

However, as the treasurer will pay coupons in USD he will be exposed to EUR/USD movements. In case we wants to hedge that position, he can buy Fx Forwards contracts, but if he wants to optimize his cash flows, he can enter into a Cross-Currency Swap with an investment bank under the following terms and conditions:

Trade Date: 25th of January 2010

Start Date: 30th of June 2010

Maturity Date: 30th of June 2017

Notional Amount: 30M USD, 21.246M EUR (converted according to an agreed FX Rate)

Fixed Rate: 5% Semi-Annually (to be received)

Float Rate: 6M Euribor + 1.2% (to be paid)

As of 25th of January 2010, the NPV is fair (<0.05% of Notional). At each date the USD Cash Flows from the Bond will be transfered into EUR and paid as a spread over 6M Euribor. The Treasurer is now completely hedged against FX risk.

Pricing Methodology

There are two main ways to price a Cross Currency Swap, the FX Curve method and the Adjusted-Discount Curve method. We will only dwell on the first method in this article and in the spreadsheet.

When using the FX Curve method, the Cash Flows in one currency (that is not the Discount currency), will be converted into the Discount currency thanks to FX Forwards and then discounted accordingly. In the previous trade, if the Discount currency is the USD then the EUR Cash Flows will be converted into USD according to the FX Forward Curve and then discounted with the USD Curve.

The FX Forward Curve is likely to be built according to FX Swaps and Cross Currency Basis Swaps contracts quoted on the market.

If we translate the previous definition into equation then the previous trade can be priced such as:  

(1)   \begin{equation*} $[DF_{USD}(MatDate)-DF_{USD}(StartDate)].Notional_{USD}+\sum_{i}{R.YF_{i-1,i}.DF_{USD}(T_{i})}=$ $[DF_{USD}(MatDate).Fx(MatDate)_{EUR/USD}-DF_{USD}(StartDate).Fx(StartDate)_{EUR/USD}].Notional_{EUR}+\sum_{i}{[Fw(T_{i-1},T_{i})+Spread].YF_{i-1,i}.Fx(T_{i})_{EUR/USD} .DF_{USD}(T_{i})})$ \end{equation*}

We can see immediately that the Cross Currency Swap is sensitive to the move in interest rates as well as FX rates. However, due to the Notional exchange at maturity the risk in a FX curve shift is much more prominent than the risk in interest rates. This consideration will be particularly important when it comes to calculate the Expected Exposure of a Cross Currency Swap. The latter will have a very different profile comparing a standard IRS as a significant part of the risk will remain until maturity and the final Notional exchange.

The spreadsheet

The spreadsheet has two VBA functions, one for the CCS NPV and another for the Cash Flows. Fixed/Fixed or Float/Float structures are supported by the functions. To keep things simple the Cash Flows function is only supporting the case where two legs have the same frequency, however one can easily improve the function to get this feature.

We assume that the Domestic/Foreign ZC Curves and FX Curves are already built according to the standard techniques (bootstrapping or multidimensional solver).

The functions are also supporting the dual curve feature.