Yielddisc Function

Purpose

Return value

Syntax

=YIELDDISC(sd,md,pr,redemption,[basis])

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

Using the YIELDDISC function

The Excel YIELDDISC function returns the annual yield for a discounted security (non-interest-bearing), such as a Treasury bill, that is issued at a discount but that matures at face value. In the example shown, the formula in F5 is:

=YIELDDISC(C7,C8,C5,C6,C9)

with these inputs, the YIELDDISC function returns 0.03264023 which, or 3.26% when formatted with the percentage number format .

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 issue, and basis are truncated to integers
• If settlement or maturity dates are not valid, YIELDDISC returns #VALUE!
• YIELDDISC returns #NUM! if any of the following are true: pr <= 0 settlement >= maturity Basis is not 0-4

Purpose

Return value

Syntax

=YIELDMAT(sd,md,id,rate,pr,[basis])

• sd - Settlement date of the security.
• md - Maturity date of the security.
• id - Issue date of the security.
• rate - Interest rate of security.
• pr - Price per $100 face value.
• basis - [optional] Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4).

Using the YIELDMAT function

The YIELDMAT function returns the annual yield of a security that pays interest at maturity. In the example shown, the formula in F5 is:

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

with these inputs, the YIELDMAT function returns 0.081 which, or 8.10% when formatted with the percentage number format .

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 issue, and basis are truncated to integers
• If settlement, maturity , or issue dates are not valid, YIELDMAT returns #VALUE!
• YIELDMAT returns #NUM! if any of the following are true: rate < 0 pr <= 0 settlement >= maturity Basis is not 0-4