Price Function

Purpose

Return value

Syntax

=PRICE(sd,md,rate,yld,redemption,frequency,[basis])

• sd - Settlement date of the security.
• md - Maturity date of the security.
• rate - Annual coupon rate.
• yld - Annual required rate of return.
• redemption - Redemption value per $100 face value.
• frequency - Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4.
• basis - [optional] Day count basis (see below, default =0).

Using the PRICE function

The Excel PRICE function returns the price per $100 face value of a security that pays periodic interest. For example, the PRICE function can be used to determine the “clean price” of a bond (also known as the quoted price), which is the price of the bond excluding accrued interest.

In the example shown, the formula in F5 is:

=PRICE(C9,C10,C7,C8,C6,C12,C13)

with these inputs, the PRICE function returns 97.56, which indicates the value of the bond is 97.56% of the face value. To get the actual dollar value, the formula in F6 is:

=F5/100*C5

Entering dates

In Excel, dates are serial numbers . Generally, the best way to enter valid dates is to use cell references, as shown in the example. If you want to enter valid dates directly inside a function, the DATE function is the best approach.

Basis

The basis argument controls how days are counted. The PRICE function allows 5 options (0-4) and defaults to zero, which specifies US 30/360 basis . This article on Wikipedia provides a detailed explanation of available conventions.

Notes

• In Excel, dates are serial numbers .
• settlement , maturity , frequency , and basis are truncated to integers
• If settlement or maturity dates are not valid, PRICE returns #VALUE!
• If basis is out-of-range, PRICE returns #NUM!
• If maturity date is not later than settlement date, PRICE returns #NUM!

Purpose

Return value

Syntax

=PRICEDISC(sd,md,discount,redemption,[basis])

• sd - Settlement date of the security.
• md - Maturity date of the security.
• discount - Discount rate of the security.
• redemption - Redemption value per $100 face value.
• basis - [optional] Day count basis (see below, default =0).

Using the PRICEDISC function

The Excel PRICEDISC function returns the price per $100 face value of a discounted security. In the example shown, the formula in F5 is:

=PRICEDISC(C6,C7,C8,C9,C10)

with these inputs, PRICEDISC returns a price for the bond of $82.50.

Entering dates

In Excel, dates are serial numbers . Generally, the best way to enter valid dates is to use cell references, as shown in the example. To enter valid dates directly inside a function, you can use the DATE function . The same formula above using the DATE function, and with other values hardcoded looks like this:

=PRICEDISC(DATE(2017,7,1),DATE(2020,1,1),7%,100,0)

Basis

The basis argument controls how days are counted. The PRICEMAT function allows 5 options (0-4) and defaults to zero, which specifies US 30/360 basis. This article on Wikipedia provides a detailed explanation of available conventions.

Notes

• In Excel, dates are serial numbers .
• Settlement, maturity, and basis are truncated to integers.
• If any date is not valid, PRICEDISC returns #VALUE!
• Rate must be positive or PRICEDISC returns the #NUM!
• If basis is out-of-range, PRICEDISC returns #NUM!
• If maturity date is not later than settlement date, PRICEDISC returns #NUM!