oFRNctm2_DM( ) Example

Description

Consider a Floating Rate Note with a valuation date of 1 July 2003, a settlement date of 3 July 2003, an effective date of 1 March 2003, a first coupon date of 1 May 2003, and a maturity date of 1 May 2008. The FRN, which has semi-annual resets and annual coupons, is trading at a clean price of \$65,000. The FRN has an ex-coupon period of 5 business days, while cash flows and rate resets that fall on a business day are adjusted to the following day. The accrual basis is actual/360 and the rate reset cycle is computed backwards from the maturity date.

The cash flow map of the FRN is determined as follows:

Function Specification

=oFRNctm2_DM(65000, {"1/7/03","3/7/03","1/3/03","1/5/03","","1/5/08"}, 2, 1, K2:L6, N2:O6, H2:I6, 1, Q2:R2, T2:V17, {3,3}, {3,3}, 2, X2:X39, 1, 5, 3, 1, 0)

Parameter Name

Parameter Value

Clean Price

65000

Valuation Date

1 July 2003

Settlement Date

3 July 2003

Effective Date

1 March 2003

First Coupon Date

1 May 2003

Penultimate Coupon Date

Maturity Date

1 May 2008

Reset Frequency

2

Payment Frequency

1

Quoted Margin RS

K2:L6

Quoted Margin CP

N2:O6

Notional Amount

H2:I6

Notional Flag

1

Past Reset Rates

Q2:R2

Zero Curve

T2:V17

Accrual Basis RS

3

Accrual Basis CP

3

3

3

Reset Offset

2

Holiday Schedule

X2:X39

Interpolation Method

1

Ex-Coupon Days

5

Ex-Coupon Type

3

Reset Cycle

1

Output Flag

0

Where the cell references contain the following information (excluding the headers):

Notional Amount (H2:I6):

Reset Date

Notional Amount

.

1/5/03

\$100,000

3/5/04

\$90,000

2/5/05

\$80,000

1/5/06

\$70,000

1/5/07

\$60,000

Quoted Margin RS (K2:L6)

Reset Date

QM (bp)

.

29/4/03

20

29/4/04

20

28/4/05

15

27/4/06

15

27/4/07

15

Quoted Margin CP (N2:O6)

Effective Date

QM (bp)

.

1/5/03

20

3/5/03

20

2/5/05

20

1/5/06

15

1/5/07

15

Past Reset Rates (Q2:R2)

Reset Date

Rate

.

29/4/03

0.0530

See Zero Curve and Holiday Schedule for the data for these cell references.

Solution

The following results are obtained:

24.624569

65,000.0000

983.8356

65,983.8356

3.805578

3.805484

19.498609

25.110043