Quick Navigation
YIELDMAT Function
Summary
The YIELDMAT function calculates the annual yield for a security that pays interest at maturity, such as certain bonds or notes. This financial function helps investors determine the effective return based on settlement date, maturity, issue details, interest rate, and market price.
Syntax
YIELDMAT(settlement, maturity, issue, rate, pr, [basis])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| settlement | Date |
Yes | Security settlement date (post-issue trading date) |
| maturity | Date |
Yes | Security maturity/expiration date |
| issue | Date |
Yes | Security issue date |
| rate | Number |
Yes | Interest rate at issuance |
| pr | Number |
Yes | Price per $100 face value |
| basis | Number |
No | Optional day count method (0=US 30/360 default) |
Using the YIELDMAT Function
YIELDMAT is crucial for fixed-income analysts evaluating securities where interest is paid only at maturity. Use it to compare actual yields against stated rates, assess investment value, or validate bond pricing models. Always use DATE functions for date parameters to avoid text parsing errors.
Common YIELDMAT Examples
Basic Bond Yield Calculation
=YIELDMAT(DATE(2008,3,15),DATE(2008,11,3),DATE(2007,11,8),6.25%,100.0123,0)Calculates 6.10% yield for a bond settled March 15, 2008, maturing November 3, 2008, issued November 8, 2007 at 6.25% rate, price $100.0123 using US 30/360 basis.
Using Cell References
=YIELDMAT(A2,A3,A4,A5,A6,0)References worksheet cells containing dates, rate, and price for cleaner formulas.
Frequently Asked Questions
Common Errors and Solutions
#VALUE! error
Cause: Invalid date format for settlement, maturity, or issue
Solution: Use DATE(year,month,day) functions instead of text dates
#NUM! error - rate or pr invalid
Cause: Rate < 0 or pr ≤ 0
Solution: Ensure positive interest rate and price > 0
#NUM! - Invalid basis
Cause: Basis < 0 or basis > 4
Solution: Use values 0-4 only
#NUM! - Date sequence
Cause: Settlement ≥ maturity
Solution: Verify chronological order: issue < settlement < maturity
Notes
- Dates stored as serial numbers (Jan 1, 1900 = 1)
- Settlement always follows issue date
- Truncates dates and basis to integers
- Perfect for zero-coupon or discount bonds paying at maturity
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in: Excel 2003 and earlier
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+