YIELD Function in Microsoft Excel: Syntax, Examples & Troubleshooting

=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

All arguments except basis are required. Settlement and maturity are dates; rate is the annual coupon rate; pr is price per 100 face value; redemption is the amount per 100 at maturity. Frequency is 1, 2 or 4 coupon payments per year. Do not supply the entire position cost in a per-100 price argument.

The optional basis codes are 0 or omitted = US (NASD) 30/360; 1 = actual/actual; 2 = actual/360; 3 = actual/365; 4 = European 30/360. Use DATE formulas or genuine date values; date text can be ambiguous between locales. The dates and relevant frequency/basis inputs are truncated to integers as documented, so time-of-day fractions do not create intraday accrual.

Put =DATE(2026,1,15) in B2 and =DATE(2028,1,15) in B3. Put 5% in B4, 100 in B5 (price), 100 in B6 (redemption), 1 in B7 and 0 in B8.

1. Enter =YIELD(B2,B3,B4,B5,B6,B7,B8) in D2.
2. Expected result: 0.05, or 5%.
3. Check that 5/1.05+105/1.05^2 equals the price 100.

This fixture settles on a regular annual coupon boundary. The cash flows are 5 in one year and 105 in two years, with no initial accrued-interest fraction.

In B10 enter =5/1.1+105/1.1^2, approximately 91.32231404958677. Then enter =YIELD(B2,B3,B4,B10,B6,B7,B8) in D3. Expected yield is approximately 10%.

Use the full-precision price formula rather than manually entering 91.32 if you want a precise inverse check. A rounded market quote can imply a slightly different yield, which is not necessarily a solver fault.

Enter:

=YIELD(DATE(2026,7,15),DATE(2027,1,15),6%,106/1.04-3,100,1,0)

Expected result: approximately 8%. On the 30/360 basis, half the annual period has accrued and half remains. Price excluding accrued interest is 106/1.04−3, approximately 98.92307692; adding accrued interest 3 gives the discounted 106 final payment. Solving 106/(price+3)−1 and dividing by 0.5 recovers 8%.

Microsoft documents a direct calculation when one coupon period or less remains. For multiple remaining periods, it describes a Newton-method process of up to a hundred iterations based on the PRICE equation. Do not present an independent solver or an algebraic single-period expression as a native test of every multi-period convergence case.

A high coupon rate does not by itself mean a high yield: paying a premium for that coupon changes the return implied by the same cash flows. Keep the original price precision and chosen convention beside any reported yield.

Microsoft documents #VALUE! for invalid dates. #NUM! is documented for a negative coupon rate; price or redemption zero or negative; unsupported frequency; basis outside 0–4; or settlement at or after maturity. It does not list zero coupon rate as invalid.

If the result differs from a quote, check clean-price versus accrued-interest treatment, settlement date, frequency and day-count convention before changing solver expectations. This function models a regular coupon security, not arbitrary dated cash flows, costs or a live market recommendation.

Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016. This current support list does not establish the first release. The introduction version and last functioning version have not been established from the reviewed documentation. No retirement is asserted.

Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.

DATE in Excel | PV in Excel | RATE in Excel | XIRR in Excel

• Microsoft YIELD reference, retrieved October 17, 2026.