Previous Topic

Next Topic

oFRNctm2_CFM( ) 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. Coupons are paid annually, while the rate resets are semi-annual. 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 FRN is trading at a discount margin of 15 basis points.

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

 

 

Function Specification

=oFRNctm2_CFM(15, {"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)

 

 

 

Parameter Name

Parameter Value

 

 

 

 

 

Discount Margin

15

 

 

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

 

 

Business Day Con. RS

3

 

 

Business Day Con. CP

3

 

 

Reset Offset

2

 

 

Holiday Schedule

X2:X39

 

 

Interpolation Method

1

 

 

Ex-Coupon Days

5

 

 

Ex-Coupon Type

3

 

 

Reset Cycle

1

 

 

 

 

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 data for these cell references.

 

 

Solution

The FRN's cash flow map is presented below

Leg

Payment Date

 

Reset Date

 

Coupon

Notional

Total Cash Flow

 

PV Cash Flow

 

.

1

3/5/04

0.0570000

5,746.8493

10,000.0000

15,746.8393

15,006.6335

2

2/5/05

0.0682476

6,125.4570

10,000.0000

16,125.4570

14,394.9534

3

1/5/06

0.0693213

5,5530.5113

10,000.0000

15,530.5113

12,973.4505

4

1/5/07

0.0691387

4,839.7068

10,000.0000

14,839.7068

11,594.7405

5

1/5/08

0.0710634

4,275.4841

60,000.0000

64,275.4841

46,879.9306

 

 

 

 

 

 

 

Return to www.derivativepricing.com website

Copyright 2013 Hedgebook Ltd.