YIELDMAT function

The YIELDMAT function in Excel calculates the annual yield of a security that pays interest at maturity. This is often used for bonds or notes that accrue interest over their life and pay it as a lump sum upon maturity.

Syntax:

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

Arguments:

1. settlement: The settlement date (the date the security is purchased).
2. maturity: The maturity date (when the security is redeemed).
3. issue: The issue date (when the security was originally issued).
4. rate: The annual coupon rate (interest rate).
5. pr: The price per $100 face value of the security.
6. basis (optional): The day-count basis to use. Defaults to 0.
   • 0 = US (NASD) 30/360
   • 1 = Actual/actual
   • 2 = Actual/360
   • 3 = Actual/365
   • 4 = European 30/360

Formula:

The yield to maturity (YTM) is calculated as:

$$\text{Yield} = \frac{\text{Redemption Value} – \text{Price} + (\text{Interest Accrued})}{\text{Price}} \times \frac{\text{Basis Year Days}}{\text{Days to Maturity}}$$

Where:

• Redemption Value = 100 (if face value is $100).
• Interest Accrued is based on the coupon rate and time from issue to settlement.
• Days to Maturity = Difference between settlement and maturity dates.

Example:

You have a security with the following details:

• Issue Date: January 1, 2023
• Settlement Date: July 1, 2023
• Maturity Date: January 1, 2024
• Annual Coupon Rate: 5%
• Price: 98 (98% of face value)
• Basis: Actual/360 (basis = 2)

Formula:

=YIELDMAT(DATE(2023,7,1), DATE(2024,1,1), DATE(2023,1,1), 5%, 98, 2)

Result:

The function returns the annual yield of the security as a percentage.

Key Points:

1. Dates:
   • The settlement date must fall between the issue date and maturity date.
   • Use Excel’s DATE() function to avoid invalid date errors.
2. Price and Rate:
   • Price is expressed as a percentage of the face value (e.g., 98 = 98% of face value).
   • The rate is the annual coupon rate.
3. Basis:
   • The day-count basis significantly affects the calculation, so choose the one that aligns with the market convention.
4. Errors:
   • A #NUM! error occurs if the settlement date is after the maturity date or if other inputs are invalid.