YIELDMAT function
YIELDMAT returns the annual yield of a bond or note that pays all its interest at maturity, based on its price. It is the inverse of PRICEMAT.
Example
Read it as a sentence: find the yield on a security settling March 1, 2024, maturing September 1, 2024, and issued January 1, 2024, paying 6 percent at a price of 100, using basis 0, which gives 0.0594059405940595.
Syntax
YIELDMAT(settlement, maturity, issue, rate, pr, [basis])
Arguments in [square brackets] are optional.
-
settlement(required) - Date the security is bought (after issue, before maturity).
-
maturity(required) - Date the security expires and is repaid.
-
issue(required) - Issue date of the security.
-
rate(required) - Interest rate at issue.
-
pr(required) - Price per 100 face value.
-
basis(optional) - Day-count convention: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.
Returns: Annual yield of a security paying interest at maturity.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2023,11,15),0.0625,100.0123,0) | 0.061426719 | Typical |
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2023,11,15),0.0625,100.0123,0) | 0.061426719 | Basis 0 |
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2023,11,15),0.0625,100.0123,1) | 0.061418755 | Basis 1 |
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2023,11,15),0.0625,100.0123,2) | 0.06140696 | Basis 2 |
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2023,11,15),0.0625,100.0123,3) | 0.061418755 | Basis 3 |
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2023,11,15),0.0625,100.0123,4) | 0.061426719 | Basis 4 |
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2024,6,1),0.0625,100.0123,0) | #NUM! | Issue after settlement |
=YIELDMAT(DATE(2024,2,15),DATE(2025,4,13),DATE(2024,2,15),0.0625,100.0123,0) | #NUM! | Issue = settlement |
Watch out for
- Dates must run issue, settlement, maturity.
- Settlement must fall after the issue date: with settlement equal to issue Excel returns #NUM! (measured in Excel 16.0.20527).
Where it works
- Excel
- YIELDMAT is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .