Pricedisc Function

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!

Purpose

Return value

Syntax

=PRICEMAT(sd,md,id,rate,yld,[basis])

• sd - Settlement date of the security.
• md - Maturity date of the security.
• id - Issue date of the security.
• rate - Security interest rate at date of issue.
• yld - Annual yield of the security.
• basis - [optional] Day count basis (see below, default =0).

Using the PRICEMAT function

The Excel PRICEMAT function returns the price per $100 face value of a security that pays interest at maturity . In the example shown, the formula in F5 is:

=PRICEMAT(C5,C6,C7,C8,C9,C10)

with these inputs, PRICEMAT returns a price for the bond of $93.09.

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 .

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 .
• s ettlement, maturity, issu e, and basis are truncated to integers.
• If any date is not valid, PRICEMAT returns #VALUE!
• rate and yield must be positive or PRICEMAT returns the #NUM!
• If basis is out-of-range, PRICEMAT returns #NUM!
• If maturity date is not later than settlement date, PRICEMAT returns #NUM!