YIELD Function
Calculates the yield to maturity of a security that pays periodic interest.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| settlement | Date the security is purchased. | Required |
| maturity | Date the security expires. | Required |
| rate | Annual coupon rate. | Required |
| pr | Price per $100 face value. | Required |
| redemption | Redemption value per $100 face value. | Required |
| frequency | Number of coupon payments per year (1, 2, or 4). | Required |
| basis | Day-count convention (0 = US 30/360, default). | Optional |
Basic Example
Yield of a semi-annual bond
=YIELD(DATE(2024,4,1), DATE(2034,4,1), 0.05, 92.64, 100, 2)A 10-year bond with a 5% coupon, priced at $92.64, has a yield to maturity of about 6%.
Advanced Examples
Example 1: Annual-pay bond
European bondYield of an annual-pay bond
=YIELD(DATE(2024,4,1), DATE(2034,4,1), 0.05, 92.64, 100, 1, 1)How YIELD Works
YIELD solves for the discount rate that makes the present value of all future coupon payments plus the redemption value equal to the current price.
Important Notes & Limitations
Settlement must be before maturity.
Frequency must be 1, 2, or 4.
For bonds with irregular coupon periods, use ODDFYIELD or ODDLYIELD.
Common Errors & Fixes
#NUM! errorInvalid dates, price, or rates.Fix: Verify all inputs.
#VALUE! errorDates are not recognized.Fix: Use DATE function or valid date serial numbers.
Download Practice File
YIELD
yieldPractice YIELD with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the YIELD function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps