YIELD Function
Calculate bond yields in Excel and Sheets. Learn the YIELD function syntax, parameters, examples, and common errors with this comprehensive guide.
Excel
Google SheetsExcelGoogle Sheets=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])Quick Answer
YIELD function YIELD function calculates the annual yield of a security that pays periodic interest. The syntax is `=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])` where settlement is the purchase date, maturity is the expiration date, rate is the annual coupon rate, pr is the current price per $100, redemption is the value at maturity (typically 100), and frequency is payment periods per year.
=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])- Example: `=YIELD(DATE(2025,1,15), DATE(2030,1,15), 0.06, 95.50, 100, 2)` returns 0.0679 or 6.79%.
Practical Examples
Basic Bond Yield Calculation
Calculate yield for a standard corporate bond with semi-annual payments
Treasury Bond Yield with Specific Basis
Calculate yield for a US Treasury bond using actual/actual day count
Annual Payment Bond Yield
Calculate yield for a bond with annual coupon payments
High-Yield Corporate Bond
Calculate yield for a high-yield corporate bond trading at discount
Quarterly Payment Bond with European Basis
Calculate yield for a bond with quarterly payments using European day count
Long-Term Bond Yield Analysis
Calculate yield for a 20-year bond to compare with current rates
Error Handling with IFERROR
Handle potential errors when dates or values are invalid
Common Errors and Solutions
YIELD returns #NUM! error
Invalid dates (settlement >= maturity), negative rate, price <= 0, redemption <= 0, invalid frequency (not 1, 2, or 4), or invalid basis (not 0-4)
Ensure settlement date is before maturity date, all numeric values are positive, frequency is 1, 2, or 4, and basis is between 0 and 4. Verify rate is entered as decimal (0.06 for 6%).
Validate inputs: settlement < maturity, rate >= 0, pr > 0, redemption > 0, frequency in (1,2,4), basis in (0,1,2,3,4). Add data validation rules to prevent invalid entries.
Example:
YIELD returns #VALUE! error
One or more arguments is not a valid data type - settlement or maturity is not a valid date, or rate, pr, redemption, frequency, or basis is not a number
Use the DATE(year, month, day) function to construct dates rather than text strings. Ensure all numeric parameters are formatted as numbers, not text. Convert text to numbers using VALUE() if necessary.
Always use DATE() function for date inputs: =YIELD(DATE(2025,1,15), DATE(2030,1,15), 0.06, 95.50, 100, 2). Format cells properly before calculations.
Example:
YIELD cannot converge to a result
YIELD uses iterative calculation (Newton method) and cannot converge to a result within 100 iterations due to extreme or unrealistic input values
Check if input values are realistic for bond markets. Typical bond prices range from 50-150, coupon rates 0-20%, and yields 0-25%. If values are extreme, verify data source or adjust parameters.
Add conditional formatting to flag unusual values: prices < 50 or > 150, rates > 0.20, yields > 0.30. Review flagged bonds manually before calculations.
Example:
YIELD returns unexpected or unrealistic yields
Coupon rate entered as percentage (6 instead of 0.06), price entered as decimal (0.9550 instead of 95.50), or wrong frequency for bond type
Enter coupon rate as decimal (0.06 for 6%), price as dollar amount per $100 face value (95.50 for $95.50), and verify frequency matches bond terms (most corporate bonds are semi-annual = 2).
Document input format clearly: rate as decimal (0.06), price as dollars per $100 (95.50), frequency as number (1, 2, or 4). Use cell formatting to display percentages while storing decimals.
Example:
Best Practices and Advanced Tips
Understanding Day Count Basis
The basis parameter significantly impacts yield calculations. US corporate bonds typically use 0 (30/360 NASD), while European bonds use 4 (30/360 European). Treasury securities use 1 (actual/actual) for most precise calculations. Choose the correct basis for your security type.
Date Formatting Best Practices
Always use DATE() function for settlement and maturity parameters rather than text dates to avoid regional format issues and ensure calculation accuracy across different Excel versions and locales.
Yield vs Price Relationship
Bond prices and yields have an inverse relationship. When price increases above par (100), yield decreases below the coupon rate, and vice versa. This fundamental relationship helps validate your YIELD calculations - if price > 100, yield should be < coupon rate.
Iteration Convergence
YIELD uses iterative calculation (Newton method) and may fail with extreme values. If #NUM! persists after validating inputs, verify input values are within realistic ranges for bond markets. Typical yields range from 0-25% for most securities.
Combining with Other Financial Functions
Use YIELD with PRICE, DURATION, and MDURATION for comprehensive bond analysis. These functions work together for complete fixed income portfolio management. For example, use PRICE to verify: PRICE(settlement, maturity, rate, YIELD(...), redemption, frequency, basis) should equal pr.
Settlement vs Trade Date
Remember that the settlement date is typically 1-2 business days after the trade date (T+1 or T+2 for most bonds). When analyzing market transactions, ensure you're using the settlement date, not the trade date, for accurate yield calculations.
Handling Accrued Interest
YIELD calculates the yield based on the clean price (without accrued interest). If you have a dirty price (including accrued interest), subtract the accrued interest before using YIELD. Use ACCRINT or ACCRINTM to calculate accrued interest.
Choosing the Right Yield Function
Use YIELD for bonds with periodic coupon payments, YIELDDISC for zero-coupon/discount securities (like T-Bills), and YIELDMAT for securities that pay interest at maturity. Using the wrong function will produce incorrect results.
Need Help with YIELD Function?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
The DURATION function calculates Macaulay duration of a security with periodic interest payments. Learn syntax, examples, and best practices.
ExcelSheetsThe FV function calculates future value of investments with constant payments and interest rate. Master retirement planning and savings goals with examples.
ExcelSheetsThe PV function calculates the present value of an investment or loan with constant periodic payments and a constant interest rate. Master PV with examples,...
ExcelSheetsCalculate Treasury bill yields in Excel and Sheets. Learn the TBILLYIELD function syntax, parameters, examples, and common errors with this guide.
ExcelSheets