Swap Calculator
User’s Manual
World Bank Treasury
1.
Introduction
This swap calculator has been developed by the World Bank’s Treasury staff as a flexible multicurrency swap pricing tool. The calculator has been designed taking into account the specific needs of IBRD borrowers. It enables IBRD borrowers to calculate indicative pricing for interestrate and currency conversions of disbursed fixedspread loans (FSLs) as well as freestanding interestrate and currency swaps offered in conjunction with these loans. Currently, interest rate swaps, crosscurrency swaps and basis swaps in three major currencies USD, JPY and EURO can be priced using this tool. The swap calculator is sufficiently flexible to accommodate plain vanilla, amortizing, accreting and roller coaster swaps. This manual provides stepbystep details of how to use the swap calculator. If you have any questions regarding the calculator, please contact the World Bank staff at the numbers provided on the last page of this manual.
2.
System and Software Requirements
Operating System : Windows 3.1 or higher Minimum Hardware : 486 PCs or higher Minimum Memory : 16 MB Software : Excel 5.0 or higher
3.
Getting Started
The calculator is an Excelbased program which users can download from the following location:
http://treasury.worldbank.org/Services/Asset+Liability+Management/Analytical+Tools/Swap+Calculator.html
At this web site, select the Swap Calculator button to go to the Swap Calculator Page. Then click on Swap Calculator Excel Program. The following screen should appear:
Click the Save File button to save the file to your local drive.
2
4.
Calculator Layout
The calculator consists of two separate worksheets: Input Area and Output Area. In the Input Area sheet, users need to enter the following information: · · · · · · · Currency and interest rate basis of their existing loan Currency and interest rate basis that is desired Principal Amount to be converted/swapped Maturity date of the conversion/swap Effective date by which the new loan terms are to take effect Amortization Schedule of the existing loan Current market data including applicable yield curves, exchange rates and basis swap costs.
Based on the above inputs, the swap calculator provides the following information in the Output Area sheet: · The fixed interest rate or spread over or below LIBOR that needs to be paid on the converted loan · The new Amortization Schedule of the converted loan
5.
Loan/Swap Data
Information regarding the current loan and desired loan characteristics must be entered in the corresponding table in the “Input Area” sheet. A brief description of the various parameters is given below. Currency: Currencies available are USD, EUR and JPY. Interest Rate Type: Interest rate type can be Fixed(F) or Floating(V). Spread: This is the spread over or below LIBOR applicable to the floating interest rate on the existing loan, expressed in basis points. (Applicable only if interest rate type is “Floating”. This cell should be left blank if interest rate type is “Fixed”.)
3
Fixed Interest Rate: This is the fixed interest rate applicable on the existing loan, to be specified as a percentage on a semiannual basis. (Applicable only if interest rate type is “Fixed”. This cell should be left blank if interest rate type is “Floating”.) Principal Amount (to be converted): This is the loan principal amount to be converted or swapped into a different currency and/or interest rate basis. Effective Date: This is the date from which the loan conversion or swap will become effective, i.e., the date from which interest accruals will be calculated on the basis of the changed currency and interest rate basis, as applicable. In case of FSL conversions or swaps offered in conjunction with FSLs, the effective date is always the next interest payment date on the existing loan. Maturity Date: This is the final date until which the loan conversion or the swap will remain in effect. In the case of fullmaturity loan conversion or swap, this would be the final maturity date of the existing loan. If, however, a partial maturity loan conversion or swap is desired, users can enter a final maturity date that is different from the final maturity date of the loan. The final maturity date should always correspond to a loan interest payment date. The calculator allows for final maturity dates up to 30 years from the Effective Date. Current Amortization Schedule: In this table, the borrower is required to enter the schedule of outstanding principal amounts on the existing loan, starting from the interest payment date following the Effective Date. The interest payment dates are automatically generated once the “Reset Amortization Schedule” button is clicked.
6.
Market Data
The swap calculator requires market data on (a) Money Market and Swap Rates (b) Basis Swap Cost and (c) Exchange Rates. The Money Market and Swap Rates table requires data on yield curves in the currencies relevant to the swap. Currently, the calculator provides for data to be entered for three major currencies – USD, EUR and JPY. The Basis Swap Cost table requires data on basis swap cost for the three currency pairs (USDJPY, USDEUR and EURJPY) for various maturities. Basis swap cost is a cost that is incurred whenever a basis currency swap or a crosscurrency swap is executed. This cost could be positive or negative depending on the currency pair under consideration.
4
The Exchange Rates table requires data on the exchange rates for the applicable currency pair(s). Yield curves, basis swap cost and exchange rates can be found on our web site under Market Data. The data provided is as of close of the previous business day or earlier. The user may copy the data from the Market Data sheet (or enter it from any other source) into the respective tables in the Input Area sheet of the swap calculator before starting any computations.
7.
Computations
Computations require the following two steps: Reset Amortization Schedule: After entering the inputs in the three input tables, click the Reset Amortization Schedule button. This clears the amortization schedule and projects future cashflow dates. Next, enter the outstanding principal as of each cashflow date. Please note that future cashflow dates depend on effective date and years to final maturity. Whenever one of these two variables changes, the amortization schedule needs to be reset. Compute Rate/Spread: After entering the amortization schedule, click the Compute Rate/Spread button. The calculator then solves for the fixed interest rate or the spread over or below LIBOR to be paid on the converted loan. In general, the computation should not take more than 15 seconds. The fixed interest rate or spread, together with the new amortization schedule for the swap is posted on the Output Area sheet. The Output Area sheet also provides the new amortization schedule of the converted loan/associated swap.
8.
Numerical Examples
The following numerical examples illustrate the use of the swap calculator. The market data used in the examples is provided below:
5
Market Data
Money Market and Swap Rates(%)
Maturity 3m 6m 12m 2y 3y 4y 5y 6y 7y 8y 9y 10y 12y 15y 25y 30y USD 5.52 5.96 6.07 6.23 6.37 6.48 6.57 6.64 6.72 6.77 6.82 6.87 6.95 7.02 7.07 7.09 EUR 2.69 3.12 3.33 3.94 4.34 4.65 4.87 5.06 5.25 5.41 5.52 5.60 5.76 5.95 6.10 6.10 JPY 0.09 0.21 0.23 0.45 0.74 1.04 1.33 1.58 1.79 1.97 2.12 2.24 2.45
Basis Swap Cost (Basis Points)
Maturity 1y 2y 3y 4y 5y 7y 10y 15y 20y USDJPY 9.50 11.50 14.50 16.50 20.50 25.00 30.00 32.00 33.50 USDEUR 1.13 1.50 2.00 2.50 3.00 3.50 4.00 4.25 4.25 EURJPY 8.38 10.00 12.50 14.00 17.50 21.50 26.00 27.75 29.25
Exchange Rates
1 USD = 1 USD = 1 EUR = 0.97 104.28 107.47 EUR JPY JPY
6
Example 1
The borrower has taken out a USD 100 million FSL, with a bullet maturity, at an interest rate of LIBOR + 55 basis points. The interest payment dates on the loan are January 15 and July 15. The maturity date of the loan is July 15, 2010. The borrower now wishes to convert the interest rate basis to fixed, using an interest rate swap. What interest rate would apply between the borrower and the IBRD? The borrower can use IBRD’s swap calculator to get an indicative pricing on this swap by following these steps:
Step 1
Enter the current loan and desired loan characteristics in the corresponding table in the “Input Area” sheet . These include information on currency, interest rate type, spread/fixed rate on the current loan and the effective date of the swap.
Step 2
Click the “Reset Amortization Schedule” button. This will project the future cashflow dates for this loan/swap.
Step 3
Enter the outstanding amounts corresponding to each cashflow date in the Amortization Schedule.
After following the above three steps, the Input Area sheet appears as follows:
7
Underlying/Desired Loan Characteristics
Loan Parameters
Existing Loan Parameters Currency (EUR/JPY/USD) Interest rate type (F/V) USD V Desired Loan Parameters USD F
Current Amortization Schedule
Date 7/15/00 1/15/01 7/15/01 Outstanding Principal 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000
Spread (in Basis Points) Fixed Interest Rate(%) Principal Amount (to be converted) Effective Date of the Swap Maturity Date of the Swap Footnotes
55
1/15/02 7/15/02
100,000,000 1/15/00 7/15/10
1/15/03 7/15/03 1/15/04 7/15/04 1/15/05 7/15/05 1/15/06 7/15/06 1/15/07 7/15/07 1/15/08 7/15/08 1/15/09 7/15/09 1/15/10 7/15/10
1.Three choices of currency are available USD, JPY and EUR 2.Symbols for fixed and floating interest rate type are "F" and "V" respectively.
8
Step 4
Enter market data. For this example, data on USD interest rates would suffice.
Step 5
Click the “Compute Rate/Spread” button. The solution appears in the “Output Area” sheet. The fixed interest rate that would apply is 7.51%.
After following the above five steps, the Output Area sheet looks as follows:
Output
Output Parameters
New Converted Loan Currency Interest Rate Type Exchange Rate ( USD per USD) Fixed Interest Rate(%) USD F 1.00 7.51
New Amortization Schedule
Date 7/15/00 1/15/01 7/15/01 1/15/02 7/15/02 1/15/03 7/15/03 1/15/04 7/15/04 1/15/05 7/15/05 1/15/06 7/15/06 1/15/07 7/15/07 1/15/08 7/15/08 1/15/09 7/15/09 1/15/10 7/15/10 Outstanding Principal 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000
9
Example 2
The borrower has taken out a USD 100 million FSL, with a bullet maturity, at an interest rate of LIBOR + 55 basis points. The interest payment dates on the loan are February 15 and August 15. The maturity date of the loan is August 15, 2010. The borrower now wishes to convert the loan into floating rate EUR. What would be the spread over LIBOR on the loan after conversion? The borrower can use IBRD’s swap calculator to get indicative pricing on the converted loan by following these steps:
Step 1
Enter the current loan and desired loan characteristics in the corresponding table in the “Input Area” sheet . These include information on currency, interest rate type, spread/fixed rate on the current loan and the effective date of the swap.
Step 2
Click the “Reset Amortization Schedule” button. This will lay out the cashflow dates for this loan/swap.
Step 3
Enter the outstanding amounts corresponding to each cashflow date in the Amortization Table.
After following the above three steps, the Input Area sheet looks as follows:
10
Underlying/Desired Loan Characteristics
Loan Parameters
Existing Loan Parameters Currency (EUR/JPY/USD) Interest rate type (F/V) USD V Desired Loan Parameters EUR V
Current Amortization Schedule
Date 8/15/00 2/15/01 8/15/01 Outstanding Principal 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000
Spread (in Basis Points) Fixed Interest Rate(%) Principal Amount (to be converted) Effective Date of the Swap Maturity Date of the Swap Footnotes
55
2/15/02 8/15/02
100,000,000 2/15/00 8/15/10
2/15/03 8/15/03 2/15/04 8/15/04 2/15/05 8/15/05 2/15/06 8/15/06 2/15/07 8/15/07 2/15/08 8/15/08 2/15/09 8/15/09 2/15/10 8/15/10
1.Three choices of currency are available USD, JPY and EUR 2.Symbols for fixed and floating interest rate type are "F" and "V" respectively.
11
Step 4
Enter market data. For this example, data on USD and EUR interest rates, basis swap cost between USD and EUR, and USD/EUR exchange rate is required for pricing.
Step 5
Click the “Compute Rate/Spread” button. The solution appears in the “Output Area” sheet. The fixed spread over EURO LIBOR that would apply on the converted loan is 47 basis points.
After following the above five steps, the Output Area sheet looks as follows:
Output
Output Parameters
New Converted Loan Currency Interest Rate Type Exchange Rate ( EUR per USD) Spread (in Basis Points) EUR V 0.97 47
New Amortization Schedule
Date 8/15/00 2/15/01 8/15/01 2/15/02 8/15/02 2/15/03 8/15/03 2/15/04 8/15/04 2/15/05 8/15/05 2/15/06 8/15/06 2/15/07 8/15/07 2/15/08 8/15/08 2/15/09 8/15/09 2/15/10 8/15/10 Outstanding Principal 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000 97,000,000
12
Example 3
The borrower has taken out an amortizing EUR 100 million FSL, at an interest rate of LIBOR + 55 basis points. The maturity date of the loan is August 15, 2010. The grace period is until August 15, 2003 and the borrower has chosen the level repayment option. The interest payment dates on the loan are February 15 and August 15. The borrower has chosen the level repayment option for loan repayments. The borrower now wishes to convert the loan into fixed rate JPY. What would be the applicable rate on the loan after conversion? The borrower can use IBRD’s swap calculator to get indicative pricing on the converted loan by following these steps:
Step 1
Enter the current loan and desired loan characteristics in the corresponding table in the “Input Area” sheet . These include information on currency, interest rate type, spread/fixed rate on the current loan and the effective date of the swap.
Step 2
Click the “Reset Amortization Schedule” button. This will lay out the cashflow dates for this loan/swap.
Step 3
Enter the outstanding amounts corresponding to each cashflow date in the Amortization Table. Note that since there are 14 dates on which equal amounts of the loan would be repaid, the outstanding amount should be reduced by 7.1 (= 100/14) on each cashflow date.
After following the above three steps, the Input Area sheet looks as follows:
13
Underlying/Desired Loan Characteristics
Loan Parameters
Existing Loan Parameters Currency (EUR/JPY/USD) Interest rate type (F/V) EUR V Desired Loan Parameters JPY F
Current Amortization Schedule
Date 8/15/00 2/15/01 8/15/01 Outstanding Principal 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 100,000,000 92,857,143 85,714,286 78,571,429 71,428,571 64,285,714 57,142,857 50,000,000 42,857,143 35,714,286 28,571,429 21,428,571 14,285,714 7,142,857 0
Spread (in Basis Points) Fixed Interest Rate(%) Principal Amount (to be converted) Effective Date of the Swap Maturity Date of the Swap Footnotes
55
2/15/02 8/15/02
100,000,000 2/15/00 8/15/10
2/15/03 8/15/03 2/15/04 8/15/04 2/15/05 8/15/05 2/15/06 8/15/06 2/15/07 8/15/07 2/15/08 8/15/08 2/15/09 8/15/09 2/15/10 8/15/10
1.Three choices of currency are available USD, JPY and EUR 2.Symbols for fixed and floating interest rate type are "F" and "V" respectively.
14
Step 4
Enter market data. For this example, data on EUR and JPY interest rates, basis swap cost between EUR and JPY, and JPY/EUR exchange rate is required for pricing.
Step 5
Click the “Compute Rate/Spread” button. The solution appears in the “Output Area” sheet. The fixed interest rate that would apply on the converted loan is 2.28 %.
After following the above five steps, the Output Area sheet looks as follows:
Output
Output Parameters
New Converted Loan Currency Interest Rate Type Exchange Rate ( JPY per EUR) Fixed Interest Rate(%) JPY F 107.47 2.28
New Amortization Schedule
Date 8/15/00 2/15/01 8/15/01 2/15/02 8/15/02 2/15/03 8/15/03 2/15/04 8/15/04 2/15/05 8/15/05 2/15/06 8/15/06 2/15/07 8/15/07 2/15/08 8/15/08 2/15/09 8/15/09 2/15/10 8/15/10 Outstanding Principal 10,747,000,000 10,747,000,000 10,747,000,000 10,747,000,000 10,747,000,000 10,747,000,000 10,747,000,000 9,979,357,143 9,211,714,286 8,444,071,429 7,676,428,571 6,908,785,714 6,141,142,857 5,373,500,000 4,605,857,143 3,838,214,286 3,070,571,429 2,302,928,571 1,535,285,714 767,642,857 (0)
15
7.
TroubleShooting
· Error message “Please Enter Fixed Interest Rate”. Please enter the interest rate and then click OK. · Error message “Please Enter Amortization Schedule”. Click OK and then enter the amortization schedule. · Error message “ Please Enter Exchange Rate Information”. Click OK and then enter exchange rate information.
8.
Contact Information
Any questions regarding the swap calculator or this manual may be addressed to: Name
Jose F. Molina
Title
Sr. Financial Officer
Email Address
Jmolina2@worldbank.org
Phone Number
(202) 4580743
16