YIELDMAT Function (LibreOffice Calc)
The YIELDMAT function returns the annual yield of a security that pays interest at maturity. It is essential for valuing interest-bearing, non-coupon instruments and for computing yield from price.
Verification status: Consult the documentation and examples below. Native application testing has not been documented for this guide. How formula examples are checked
What the YIELDMAT Function Does
- Computes yield to maturity (YTM) for interest-at-maturity securities
- Uses issue date, settlement date, maturity date, interest rate, and price
- Supports multiple day-count basis systems
- Works with real dates, serial numbers, and DATEVALUE
- Fully compatible with Excel’s YIELDMAT function
It is designed to be precise, finance‑grade, and ideal for money‑market and structured‑note valuation.
Syntax
YIELDMAT(settlement; maturity; issue; rate; price; [basis])
Arguments
-
settlement:
The date the security is traded to the buyer. -
maturity:
The date the security matures. -
issue:
The date the security was issued. -
rate:
Annual interest rate (e.g., 0.05 for 5%). -
price:
Price per 100 face value. -
basis (optional):
Day-count convention:
| basis | Day-count convention |
|---|---|
| 0 | US 30/360 |
| 1 | Actual/Actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Basic Examples
Yield of an interest-at-maturity note
=YIELDMAT("2024-03-15"; "2025-03-15"; "2024-01-01"; 0.05; 98.5)
Using Actual/Actual
=YIELDMAT(A1; A2; A3; 0.04; 99.2; 1)
Using text dates
=YIELDMAT(DATEVALUE(A1); DATEVALUE(A2); DATEVALUE(A3); Rate; Price)
Advanced Examples
Structured note with Actual/360
=YIELDMAT("2024-01-10"; "2024-10-10"; "2024-01-01"; 0.052; 97.8; 2)
From imported CSV timestamps
=YIELDMAT(DATEVALUE(LEFT(A1;10)); DATEVALUE(LEFT(A2;10)); DATEVALUE(LEFT(A3;10)); Rate; Price)
From Excel serial dates stored as text
=YIELDMAT(DATE(1899;12;30)+VALUE(A1); DATE(1899;12;30)+VALUE(A2); DATE(1899;12;30)+VALUE(A3); Rate; Price)
Compute price from yield (inverse)
=PRICEMAT(Settlement; Maturity; Issue; Rate; Yield; Basis)
Compute effective annual yield
=(1 + YIELDMAT(A1;A2;A3;Rate;Price;Basis)) ^ 1 - 1
Compute return over the holding period
=(100 - Price) / Price
Edge Cases and Behavior Details
YIELDMAT returns a numeric value (annual yield)
Accepts:
- Real dates
- Serial numbers
- DATEVALUE outputs
Invalid text → Err:502
Behavior details
- Issue < Settlement < Maturity must hold
- Basis must be 0–4
- Rate and price must be ≥ 0
- Time components ignored
- Uses clean price (interest paid only at maturity)
- Uses iterative numerical methods internally
YIELDMAT of an error → error propagates
Common Errors and Fixes
Err:502 — Invalid argument
Cause:
- Dates not recognized
- Basis outside 0–4
- Rate or price not numeric
Fix:
- Wrap dates with DATEVALUE
- Validate basis
- Ensure rate and price are numeric
Err:504 — Invalid date sequence
Cause:
- Settlement before issue
- Settlement after maturity
Fix:
- Correct date order
Unexpected yield
Cause:
- Wrong basis
- Incorrect rate or price
- Incorrect issue date
Fix:
- Verify inputs carefully
- Confirm day-count convention
Best Practices
- Use Actual/360 (basis 2) for money‑market instruments
- Use Actual/Actual (basis 1) for government notes
- Normalize text dates with DATEVALUE
- Validate date order carefully
- Use YIELDMAT with PRICEMAT for full interest‑at‑maturity modeling
Related Patterns and Alternatives
- Use PRICEMAT to compute price from yield
- Use PRICEDISC for pure discount securities
- Use DISC to compute discount rate
- Use ACCRINTM for zero‑coupon bonds
- Use YEARFRAC for fractional year calculations
By mastering YIELDMAT and its companion functions, you can build powerful, accurate, and fully professional fixed‑income valuation models in LibreOffice Calc.