YIELDMAT Function

Always Use DATE Function

Use DATE(year, month, day) instead of text dates to avoid regional format issues and ensure accuracy across different Excel versions. For example, use DATE(2024,4,1) instead of "4/1/2024" or "1/4/2024". This prevents ambiguity between US and European date formats and ensures your formulas work correctly when shared internationally.

Understand Day Count Basis

Choose the correct basis parameter based on your market convention: US securities typically use 0 (30/360), money market instruments use 2 (Actual/360), and some international bonds use 1 (Actual/actual) or 4 (European 30/360). Using the wrong basis can lead to yield calculations that don't match market standards, making comparisons inaccurate.

Date Sequence Matters

Ensure issue date < settlement date < maturity date. Excel will return #NUM! error if this sequence is violated. The issue date must be when the security was originally created, the settlement date is when you purchased it, and the maturity date is when it expires. Logically, you cannot purchase a security before it was issued, and it cannot mature before you purchased it.

Convert to Percentage

YIELDMAT returns a decimal value (e.g., 0.0864 for 8.64%). Multiply by 100 or format cells as percentage to display in familiar format. In Excel, select the cell and press Ctrl+Shift+% or use the Percentage button in the Home tab. This makes yield comparisons more intuitive and aligns with standard financial reporting practices.

Compare with Market Rates

Use YIELDMAT results to compare against prevailing market rates to identify over/undervalued securities. If your calculated yield is significantly higher than comparable securities, the investment may be underpriced (good buying opportunity). Conversely, a lower yield might indicate overpricing. Always consider credit risk and other factors when making investment decisions.

YIELDMAT vs YIELD vs YIELDDISC

Use YIELDMAT for securities that pay interest at maturity (treasury bills, commercial paper), YIELD for bonds with periodic coupon payments, and YIELDDISC for pure discount securities with no interest payments. Using the wrong function will produce incorrect yield calculations.

Treasury Bill Convention

US treasury bills typically use basis 2 (Actual/360), which counts the actual number of days and assumes a 360-day year. This is the money market convention in the United States. Always use the correct basis for your security type to ensure accurate yield calculations.

Verify Results

The calculated yield should be higher than the stated rate when the security trades at a discount (price < 100) and lower when it trades at a premium (price > 100). This fundamental relationship helps validate your YIELDMAT calculations.