# The Excel YIELDMAT Function

Related Functions:
YIELD
YIELDDISC

## Function Description

The Excel YIELDMAT function calculates the annual yield of a security that pays interest at maturity.

The syntax of the function is:

YIELDMAT( settlement, maturity, issue, rate, pr, [basis] )

Where the arguments are as follows:

settlement-The settlement date of the security (i.e. the date that the coupon is purchased).
maturity-The maturity date of the security (i.e. the date that the coupon expires).
issue-The issue date of the security.
rate-The security's interest rate at date of issue.
pr-The security's price per \$100 face value.
[basis]-

An optional integer argument which specifies the financial day count basis that is used by the security. Possible values are:

BasisDay Count Basis
0 (or omitted)US (NASD) 30/360
1actual/actual
2actual/360
3actual/365
4European 30/360
For a detailed explanation of the financial day count basis rules, see the Wikipedia Day Count Convention page.

Note that the date arguments must satisfy the following:

issue   <   settlement   <   maturity

Also, the settlement, maturity and issue date arguments should be supplied to the function as either:

• References to cells containing dates
or
• Dates returned from formulas.

Warning: If you attempt to enter text representations of dates into Excel functions, these can be interpreted differently, depending on the date system and date interpretation settings on your computer.

## Excel Yieldmat Function Example

In the following spreadsheet, the Excel Yieldmat function is used to calculate the annual yield for a security purchased on 01-Jan-2017, with issue date 01-Jul-2014 and maturity date 30-Jun-2018. The interest rate at date of issue is 5.5% and the security has a price of \$101 per \$100 face value. The US (NASD) 30/360 day count basis is used:

AB
1Settlement Date:01-Jan-2017
2Maturity Date:30-Jun-2018
3Issue Date:01-Jul-2014
4=YIELDMAT( B1, B2, B3, 5.5%, 101 )

The above Yieldmat function calculates the yield to be 4.21%.

Note that, in the above example:

• The [basis] argument has been omitted, and so the function uses the default value 0 (denoting the US (NADS) 30/360 day count basis);
• As recommended, the settlement, maturity and issue dates have been input as references to cells containing dates.

For further examples of the Excel Yieldmat function, see the Microsoft Office website.

## Yieldmat Function Errors

If you get an error from the Yieldmat function, this is likely to be one of the following:

Common Errors
 #NUM! - Occurs if either:The issue date is ≥ settlement date;The settlement date is ≥ maturity date;Invalid numbers are supplied for the rate, pr or [basis] arguments.(I.e. if either: rate < 0; pr ≤ 0; or [basis] is any number other than 0, 1, 2, 3 or 4). #VALUE! - Occurs if either:Any of the supplied arguments are non-numeric;The supplied settlement, maturity or issue dates are not valid Excel dates.