The Excel YIELDMAT function returns the annual yield of a security that pays interest at maturity.
Syntax:= YIELDMAT (sd, md, id, rate, pr, [basis])
The YIELDMAT function syntax has the following arguments:
- Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
- Maturity Required. The security’s maturity date. The maturity date is the date when the security expires.
- Issue Required. The security’s issue date, expressed as a serial date number.
- Rate Required. The security’s interest rate at date of issue.
- Pr Required. The security’s price per $100 face value.
- Basis Optional. The type of day count basis to use.
| Basis | Day count basis |
|---|---|
| 0 or omitted | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Example: Let’s look at some Excel YIELDMAT function examples and explore how to use the YIELDMAT function as a worksheet function in Microsoft Excel:
In the following spreadsheet, the Excel Yieldmat function is used to calculate the annual yield for a security purchased on 01-Feb-2020, with issue date 01-Nov-2019 and maturity date 01-Oct-2020. The interest rate at date of issue is 5.75% and the security has a price of $99.125 per $100 face value. The US (NASD) 30/360 day count basis is used:
Syntax: =YIELDMAT(B1,B2,B3,B4,B5,B6)
Result: 7.02%
Note:
- #NUM! error – Occurs if either:
- The issue date is greater than or equal to the settlement date.
- The settlement date is greater than or equal to the maturity date.
- Invalid numbers are provided 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! error – Occurs if either:
- Any of the given arguments are non-numeric.
- The given settlement, maturity, or issue dates are not valid Excel dates.
- MS Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
- Settlement, maturity, issue, and basis are truncated to integers.
