Previous Topic

Next Topic

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

 

 

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

 

 

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:

Discount Margin

24.624569

Clean Price

65,000.0000

Accrued Interest

983.8356

Dirty Price

65,983.8356

Macaulay Duration

3.805578

Modified Duration

3.805484

Convexity

19.498609

PVBP

25.110043

 

 

Return to www.derivativepricing.com website

Copyright 2013 Hedgebook Ltd.