; amortization schedules free
Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out
Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

amortization schedules free

VIEWS: 201 PAGES: 17

  • pg 1
									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
multi-currency 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 interest-rate and currency conversions of disbursed fixed-spread loans (FSLs)
as well as free-standing interest-rate and currency swaps offered in conjunction with
these loans.

Currently, interest rate swaps, cross-currency 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 step-by-step 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 Excel-based program which users can download from the following
location:

                              www.worldbank.org/treasury-alm




                                                                                           2
3
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.



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




                                                                                            4
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”.)

Fixed Interest Rate: This is the fixed interest rate applicable on the existing loan, to be
specified as a percentage on a semi-annual 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 full-maturity 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.




                                                                                                5
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
(USD-JPY, USD-EUR and EUR-JPY) for various maturities. Basis swap cost is a cost
that is incurred whenever a basis currency swap or a cross-currency swap is executed.
This cost could be positive or negative depending on the currency pair under
consideration.

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 cash-flow dates. Next, enter the outstanding principal as of each cashflow
date. Please note that future cash-flow 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.




                                                                                             6
8.         Numerical Examples
The following numerical examples illustrate the use of the swap calculator. The market
data used in the examples is provided below:



                               Market Data
     Money Market and Swap Rates(%)          Basis Swap Cost (Basis Points)

Maturity      USD      EUR     JPY       Maturity   USD-JPY   USD-EUR   EUR-JPY
     3m         5.52    2.69     0.09       1y        -9.50     -1.13    -8.38

     6m         5.96    3.12     0.21       2y       -11.50     -1.50   -10.00
     12m        6.07    3.33     0.23       3y       -14.50     -2.00   -12.50
      2y        6.23    3.94     0.45       4y       -16.50     -2.50   -14.00
      3y        6.37    4.34     0.74       5y       -20.50     -3.00   -17.50
      4y        6.48    4.65     1.04       7y       -25.00     -3.50   -21.50
      5y        6.57    4.87     1.33      10y       -30.00     -4.00   -26.00
      6y        6.64    5.06     1.58      15y       -32.00     -4.25   -27.75
      7y        6.72    5.25     1.79      20y       -33.50     -4.25   -29.25
      8y        6.77    5.41     1.97
      9y        6.82    5.52     2.12            Exchange Rates
     10y        6.87    5.60     2.24     1 USD =     0.97    EUR
     12y        6.95    5.76     2.45     1 USD =    104.28   JPY
     15y        7.02    5.95              1 EUR =    107.47   JPY
     25y        7.07    6.10
     30y        7.09    6.10




                                                                                         7
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 cash-flow dates for this
loan/swap.

                                                   Step 3


Enter the outstanding amounts corresponding to each cash-flow date in the Amortization Schedule.

After following the above three steps, the Input Area sheet appears as follows:




                                                                                                               8
                            Underlying/Desired Loan Characteristics
                                Loan Parameters                                      Current Amortization Schedule              Money Market and
                                                Existing Loan         Desired Loan
                                                 Parameters            Parameters      Date         Outstanding Principal
Currency (EUR/JPY/USD)                                USD                   USD           7/15/00               100,000,000

Interest rate type (F/V)                               V                     F            1/15/01               100,000,000
                                                                                          7/15/01               100,000,000
Spread (in Basis Points)                               55                                 1/15/02               100,000,000
Fixed Interest Rate(%)                                                                    7/15/02               100,000,000
Principal Amount (to be converted)                  100,000,000                           1/15/03               100,000,000
Effective Date of the Swap                          1/15/00                               7/15/03               100,000,000
Maturity Date of the Swap                           7/15/10                               1/15/04               100,000,000
Footnotes                                                                                 7/15/04               100,000,000
1.Three choices of currency are available -- USD, JPY and EUR                             1/15/05               100,000,000
2.Symbols for fixed and floating interest rate type are "F" and "V" respectively.         7/15/05               100,000,000
                                                                                          1/15/06               100,000,000
                                                                                          7/15/06               100,000,000
                                                                                          1/15/07               100,000,000
                                                                                          7/15/07               100,000,000
                                                                                          1/15/08               100,000,000
                                                                                          7/15/08               100,000,000
                                                                                          1/15/09               100,000,000
                                                                                          7/15/09               100,000,000
                                                                                          1/15/10               100,000,000
                                                                                          7/15/10               100,000,000




                                                                                                                            9
                                                    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 Amortization Schedule
                                      New Converted Loan           Date          Outstanding Principal
Currency                                     USD                      7/15/00                     100,000,000
Interest Rate Type                            F                       1/15/01                     100,000,000
Exchange Rate ( USD per USD)                 1.00                     7/15/01                     100,000,000
Fixed Interest Rate(%)                       7.51                     1/15/02                     100,000,000
                                                                      7/15/02                     100,000,000
                                                                      1/15/03                     100,000,000
                                                                      7/15/03                     100,000,000
                                                                      1/15/04                     100,000,000
                                                                      7/15/04                     100,000,000
                                                                      1/15/05                     100,000,000
                                                                      7/15/05                     100,000,000
                                                                      1/15/06                     100,000,000
                                                                      7/15/06                     100,000,000
                                                                      1/15/07                     100,000,000
                                                                      7/15/07                     100,000,000
                                                                      1/15/08                     100,000,000
                                                                      7/15/08                     100,000,000
                                                                      1/15/09                     100,000,000
                                                                      7/15/09                     100,000,000
                                                                      1/15/10                     100,000,000
                                                                      7/15/10                     100,000,000




                                                                                                           10
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 cash-flow dates for this loan/swap.

                                                   Step 3

Enter the outstanding amounts corresponding to each cash-flow date in the Amortization Table.

After following the above three steps, the Input Area sheet looks as follows:




                                                                                                            11
                            Underlying/Desired Loan Characteristics
                                Loan Parameters                                      Current Amortization Schedule               Money Market and
                                                Existing Loan         Desired Loan
                                                 Parameters            Parameters      Date         Outstanding Principal
Currency (EUR/JPY/USD)                                USD                   EUR           8/15/00               100,000,000

Interest rate type (F/V)                               V                     V            2/15/01               100,000,000
                                                                                          8/15/01               100,000,000
Spread (in Basis Points)                               55                                 2/15/02               100,000,000
Fixed Interest Rate(%)                                                                    8/15/02               100,000,000
Principal Amount (to be converted)                  100,000,000                           2/15/03               100,000,000
Effective Date of the Swap                          2/15/00                               8/15/03               100,000,000
Maturity Date of the Swap                           8/15/10                               2/15/04               100,000,000
Footnotes                                                                                 8/15/04               100,000,000
1.Three choices of currency are available -- USD, JPY and EUR                             2/15/05               100,000,000
2.Symbols for fixed and floating interest rate type are "F" and "V" respectively.         8/15/05               100,000,000
                                                                                          2/15/06               100,000,000
                                                                                          8/15/06               100,000,000
                                                                                          2/15/07               100,000,000
                                                                                          8/15/07               100,000,000
                                                                                          2/15/08               100,000,000
                                                                                          8/15/08               100,000,000
                                                                                          2/15/09               100,000,000
                                                                                          8/15/09               100,000,000
                                                                                          2/15/10               100,000,000
                                                                                          8/15/10               100,000,000




                                                                                                                            12
                                                  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 Amortization Schedule
                                    New Converted Loan           Date         Outstanding Principal
Currency                                    EUR                     8/15/00                      97,000,000
Interest Rate Type                           V                      2/15/01                      97,000,000
Exchange Rate ( EUR per USD)               0.97                     8/15/01                      97,000,000
Spread (in Basis Points)                    47                      2/15/02                      97,000,000
                                                                    8/15/02                      97,000,000
                                                                    2/15/03                      97,000,000
                                                                    8/15/03                      97,000,000
                                                                    2/15/04                      97,000,000
                                                                    8/15/04                      97,000,000
                                                                    2/15/05                      97,000,000
                                                                    8/15/05                      97,000,000
                                                                    2/15/06                      97,000,000
                                                                    8/15/06                      97,000,000
                                                                    2/15/07                      97,000,000
                                                                    8/15/07                      97,000,000
                                                                    2/15/08                      97,000,000
                                                                    8/15/08                      97,000,000
                                                                    2/15/09                      97,000,000
                                                                    8/15/09                      97,000,000
                                                                    2/15/10                      97,000,000
                                                                    8/15/10                      97,000,000




                                                                                                         13
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 cash-flow dates for this loan/swap.

                                                   Step 3

Enter the outstanding amounts corresponding to each cash-flow 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:




                                                                                                            14
                            Underlying/Desired Loan Characteristics
                                Loan Parameters                                      Current Amortization Schedule               Money Market and
                                                Existing Loan         Desired Loan
                                                 Parameters            Parameters      Date         Outstanding Principal
Currency (EUR/JPY/USD)                                EUR                   JPY           8/15/00               100,000,000

Interest rate type (F/V)                               V                     F            2/15/01               100,000,000
                                                                                          8/15/01               100,000,000
Spread (in Basis Points)                               55                                 2/15/02               100,000,000
Fixed Interest Rate(%)                                                                    8/15/02               100,000,000
Principal Amount (to be converted)                  100,000,000                           2/15/03               100,000,000
Effective Date of the Swap                          2/15/00                               8/15/03               100,000,000
Maturity Date of the Swap                           8/15/10                               2/15/04                 92,857,143
Footnotes                                                                                 8/15/04                 85,714,286
1.Three choices of currency are available -- USD, JPY and EUR                             2/15/05                 78,571,429
2.Symbols for fixed and floating interest rate type are "F" and "V" respectively.         8/15/05                 71,428,571
                                                                                          2/15/06                 64,285,714
                                                                                          8/15/06                 57,142,857
                                                                                          2/15/07                 50,000,000
                                                                                          8/15/07                 42,857,143
                                                                                          2/15/08                 35,714,286
                                                                                          8/15/08                 28,571,429
                                                                                          2/15/09                 21,428,571
                                                                                          8/15/09                 14,285,714
                                                                                          2/15/10                  7,142,857
                                                                                          8/15/10                           0




                                                                                                                            15
                                                   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 Amortization Schedule
                                     New Converted Loan          Date          Outstanding Principal
Currency                                    JPY                      8/15/00                  10,747,000,000
Interest Rate Type                           F                       2/15/01                  10,747,000,000
Exchange Rate ( JPY per EUR)               107.47                    8/15/01                  10,747,000,000
Fixed Interest Rate(%)                      2.28                     2/15/02                  10,747,000,000
                                                                     8/15/02                  10,747,000,000
                                                                     2/15/03                  10,747,000,000
                                                                     8/15/03                  10,747,000,000
                                                                     2/15/04                   9,979,357,143
                                                                     8/15/04                   9,211,714,286
                                                                     2/15/05                   8,444,071,429
                                                                     8/15/05                   7,676,428,571
                                                                     2/15/06                   6,908,785,714
                                                                     8/15/06                   6,141,142,857
                                                                     2/15/07                   5,373,500,000
                                                                     8/15/07                   4,605,857,143
                                                                     2/15/08                   3,838,214,286
                                                                     8/15/08                   3,070,571,429
                                                                     2/15/09                   2,302,928,571
                                                                     8/15/09                   1,535,285,714
                                                                     2/15/10                    767,642,857
                                                                     8/15/10                              (0)




                                                                                                          16
7.      Trouble-Shooting
    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                   Title                     E-mail Address                   Phone Number
Ravi Balasubramanian   Financial Officer         rbalasubramanian@worldbank.org   (202) 458 5493
Qiming Chen            Asst. Financial Officer   qchen@worldbank.org              (202) 473 6832




                                                                                              17

								
To top