Previous Topic

Next Topic

oFRNctm_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 10 January 2003, and a maturity date of 10 January 2006. Coupons are paid semi-annually, while the rate resets are quarterly. The accrual basis is actual/365 and the constant notional amount is $100,000. Cash flows that fall on a business day are not adjusted, while discount factors are used to interpolate rates and discount factors.

Assuming the FRN is trading at a discount margin of 20 basis points, the cash flow map of the FRN is determined as follows.

 

Function Specification

=oFRNctm_CFM(20, {"1/7/03", "3/7/03", "10/1/03", "10/1/06"}, 3, 2, L2:M13, O2:P7, 100000, 1, I2:J3, R2:T17, {4,4}, {1,1}, 2, , 1)

 

 

 

Parameter Name

Parameter Value

 

 

 

 

 

Discount Margin

20

 

 

Valuation Date

1 July 2003

 

 

Settlement Date

3 July 2003

 

 

Effective Date

10 January 2003

 

 

Maturity Date

10 January 2006

 

 

ResetFrequency

3

 

 

Payment Frequency

2

 

 

Quoted MarginRS

L2:M13

 

 

Quoted MarginCP

O2:P7

 

 

Notional Amount

100000

 

 

Notional Flag

1

 

 

Past Reset Rates

I2:J3

 

 

Zero Curve

R2:T17

 

 

Accrual Basis RS

4

 

 

Accrual Basis CP

4

 

 

Business Day Con. RS

1

 

 

Business Day Con. CP

1

 

 

Reset Offset

2

 

 

Holiday Schedule

 

 

 

Interpolation Method

1

 

 

 

 

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

 

Quoted Margin RS (L2:M13):

 

 

Reset Date

QM (bp)

 

 

.

 

 

8/1/03

25

 

 

8/4/03

35

 

 

8/7/03

25

 

 

8/10/03

35

 

 

8/1/04

25

 

 

8/4/04

35

 

 

8/7/04

25

 

 

7/10/04

35

 

 

6/1/05

25

 

 

7/4/05

35

 

 

7/7/05

25

 

 

6/10/05

35

 

 

 

 

 

 

Quoted Margin CP (O2:P7):

 

 

Effective Date

QM (bp)

 

 

.

 

 

10/1/03

15

 

 

10/7/03

10

 

 

10/1/04

15

 

 

10/7/04

10

 

 

10/1/05

15

 

 

10/7/05

10

 

 

 

 

 

 

Past Reset Rates (I2:J3)

 

 

Reset Date

Reset Rate

 

 

.

 

 

8/1/03

0.051010

 

 

8/4/03

0.051150

 

 

 

 

 

 

Where the required reset dates and effective dates are derived from the oFRNctm_Dates( ) example. Refer to the FRN Examples - Zero Curve topic for the Zero Curve.

 

 

 

 

Solution

The following results are obtained:

 

 

Leg

Payment Date

Reset Date

 

Coupon

Notional

Total Cash Flow

PV Cash Flow

 

 

1

10/7/03

0.055945

2,722.3811

0

2,722.3811

2,769.5500

2

10/1/04

0.059283

2,986.4685

0

2,986.4685

2,895.4873

3

10/7/04

0.063367

3,157.4909

0

3,157.4909

2,966.8814

4

10/1/05

0.067767

3,413.8561

0

3,413.8561

3,100.3665

5

10/7/05

0.072504

3,592.9479

0

3,592.9479

3,149.0872

6

10/1/06

0.067821

3,416.5990

0

3,416.5990

2,894.1759

 

 

 

 

 

 

 

Return to www.derivativepricing.com website

Copyright 2013 Hedgebook Ltd.