ODDFPRICE

Syntax:

ODDFPRICE ( settlement , maturity , issue , first-coupon , rate , yld , redemption ,frequency [ , [ basis ] ] )

Description: Computes the price per $100 face value of a security having an odd (short or long) first period.

Mathematical Formula:

Odd short first coupon:

where:

A = number of days from the beginning of the coupon period to the settlement date (accrued days).DSC = number of days from the settlement to the next coupon date.DFC = number of days from the beginning of the odd first coupon to the first coupon date.E = number of days in the coupon period.N = number of coupons payable between the settlement date and the redemption date. (If this number contains a fraction, it is raised to the next whole number.)

Odd long first coupon:

where:

Ai = number of days from the beginning of the ith, or last, quasi-coupon period within odd period.DCi = number of days from dated date (or issue date) to first quasi-coupon (i = 1) or number of days in quasi-coupon (i = 2,..., i = NC).DSC = number of days from settlement to next coupon date.E = number of days in coupon period.N = number of coupons payable between the first real coupon date and redemption date. (If this number contains a fraction, it is raised to the next whole number.)NC = number of quasi-coupon periods that fit in odd period. (If this number contains a fraction, it is raised to the next whole number.)NLi = normal length in days of the full ith, or last, quasi-coupon period within odd period.Nq = number of whole quasi-coupon periods between settlement date and first coupon.

Arguments:

Name

Type

Description

settlement

number

The security's settlement date.

maturity

number

The security's maturity date.

issue

number

The security's issue date.

first-coupon

number

The security's first coupon date.

rate

number

The security's interest rate.

yld

number

The security's annual yield.

redemption

number

The security's redemption value per $100 face value.

frequency

number

the number of coupon payments per year. (For annual payments, frequency is 1; for semiannual payments, frequency is 2; for quarterly payments, frequency is 4.)

basis

number

The truncated integer type of day count basis to use, as follows:

Value

Day Count Basis

0 or omitted

US (NASD) 30/360

1

Actual/actual

2

Actual/360

3

Actual/365

4

European 30/360

Time information in the date arguments is ignored.

Return Type and Value: number – The price per $100 face value of a security having an odd (short or long) first period.

However, if

  • settlement, maturity, issue, or first-coupon is out of range for the current date base value, #NUM! is returned.

  • The following is not true: maturity is later than first-coupon, which is later than settlement, which is later than issue, so #NUM! is returned.

  • rate or yld < 0, #NUM! is returned.

  • frequency is any number other than 1, 2, or 4, #NUM! is returned.

  • basis < 0 or basis > 4, #NUM! is returned.

example: