[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

PRICE

Syntax:

PRICE ( settlement , maturity , rate , yld , redemption , frequency [ , [ basis ] ] )

Description: Computes the price per $100 face value of a security that pays periodic interest.

Mathematical Formula:

where:

DSC = number of days from settlement to next coupon date.
E = number of days in coupon period in which the settlement date falls.
N = number of coupons payable between settlement date and redemption date.
A = number of days from beginning of coupon period to settlement date.

Arguments:

Name

Type

Description

settlement

number

The security's settlement date.

maturity

number

The security's maturity date.

rate

number

The security's interest rate.

yield

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.) frequency is truncated to an integer.

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 that pays periodic interest.

However, if

settlement or maturity is out of range for the current date base value, #NUM! is returned.

settlement ≥ maturity, #NUM! is returned.

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

redemption ≤ 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:

PRICE(DATE(2008,2,15),DATE(2017,11,15),0.0575,0.065,100,2,0) results in 94.6344

end example]