YIELDMAT in Zoho Sheet: Syntax and Worked Examples

YIELDMAT calculates annual yield for a security that pays its interest at maturity. Its rate input describes the security’s interest; its result is the yield implied by the supplied purchase price and dates. They are different quantities.

For issue January 15, 2026, settlement April 15, 2026, maturity January 15, 2027, annual interest 6%, clean price 98.5 per 100 and basis 0, the independent expected yield is 0.08, displayed as 8% when formatted as a percentage. This is an independently derived expectation, not a result from an authored Zoho workbook.

=YIELDMAT(settlement; maturity; issue_date; rate; price; [basis])

Supply real date values. Rate is the interest rate attached to the security; price is a clean quote normalized to 100 principal in the model corroborated below. A 10,000-principal holding quoted at 98.5 still uses 98.5, not 9,850, as this argument. There is no coupon-frequency argument.

Basis defaults to 0. Zoho lists 0 US/NASD 30/360, 1 actual/actual, 2 actual/360, 3 actual/365 and 4 European 30/360. These worked examples explicitly select 0 or 3 and avoid month ends. Enter a rate of 6% as 0.06; the numeric result 0.08 means an 8% annual yield, not 0.08%.

The official YIELDMAT reference identifies an annual-yield result, but its short rate description uses the vague phrase “per period.” The public example discussed below corroborates annual simple-interest accrual for the controlled model used here.

Enter =YIELDMAT(A2;B2;C2;D2;E2;F2). The complete formula is:

=YIELDMAT(DATE(2026;4;15);DATE(2027;1;15);DATE(2026;1;15);0.06;98.5;0)

The 30/360 day counts are 90 from issue to settlement, 270 from settlement to maturity and 360 over the complete issue-to-maturity interval. Independently:

1. Maturity payment per 100 is 100+100×0.06×360/360 = 106.
2. Accrued interest is 100×0.06×90/360 = 1.5.
3. Settlement amount per 100 is clean price plus accrued interest: 98.5+1.5 = 100.
4. Remaining-period return is 106/100−1 = 0.06.
5. Annual simple yield is 0.06×360/270 = 0.08.

This annualization is not the compounded calculation (106/100)^(360/270)−1. Nor is it interest divided by the clean quote alone: the accrued amount belongs in the settlement denominator.

Zoho’s linked public YIELDMAT demonstration shows settlement February 1, 2019, maturity December 31, 2021, issue April 13, 2018, rate 0.024, price 300 and basis 2. Its displayed result is −0.216151719.

Actual day counts are 294 accrued, 1,064 remaining and 1,358 total. The independent expression is:

((100+100*0.024*1358/360)/(300+100*0.024*294/360)-1)*360/1064

It gives −0.2161517185465983, agreeing at the displayed precision. Omitting accrued interest from the denominator instead gives −0.2153533835. This discriminating example supports the clean-price, annual-simple-yield interpretation. The negative yield follows from the vendor’s unusually high price; it does not establish acceptance of every possible negative-yield input or boundary.

For the original 2026–2027 schedule, use rate 4%, basis 0 and a clean price independently calculated as 104/1.0375−1. That price is approximately 99.2409638554. A precision-preserving input is:

=YIELDMAT(DATE(2026;4;15);DATE(2027;1;15);DATE(2026;1;15);0.04;104/1.0375-1;0)

The expected yield is 5%. Keeping the price expression or its full cell value avoids introducing rounding error by typing only 99.24.

A basis-3 check uses actual elapsed 90 days, remaining 275 and total 365. With rate 6%, define clean price as 106/(1+0.08×275/365)−6×90/365, approximately 98.4947081519. Substituting that full expression as price with basis 3 gives independent expected yield 8%. The day-count denominator and accrued-interest calculation must change together; changing only the final annualization would not be the same model.

These are algebraic round-trip checks on the stated model. They are not a claim of exact inverse agreement for every Zoho boundary, basis or rounded input.

The controlled examples use issue ≤ settlement < maturity and positive clean prices. The public article does not give comprehensive numeric-domain rules or exact error tokens for equality, reversed dates, zero settlement denominators or fractional basis values. Do not infer those outcomes from another spreadsheet product. Irregular schedules and basis-1 leap-year handling are not established by these examples.

YIELD covers a periodic-coupon security and has a different signature. DATE constructs the dates explicitly; YEARFRAC explains the role of a year basis. The examples here are function education, not a recommendation to buy or sell a security.

The rolling official article and its existing public example were checked on October 18, 2026. New calculations were not executed in the Zoho application or engine. Its first introduction version, mobile/import parity and retirement/deprecation history are not established by those pages.