Zoho Sheet YIELD: Find annual yield from a regular coupon price
Use YIELD in Zoho Sheet with annual-rate evidence, complete price and redemption inputs, coupon frequency and independent inverse-pricing checks.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: for the two-year annual-pay example below, a 6% coupon and price 96.4334705075 per 100 face value imply an independently calculated annual yield of 8%. Yield is a rate derived from the price and payment model, not the coupon percentage itself.
Syntax and an important wording correction
=YIELD(settlement; maturity; rate; price; redemption_value; frequency; [basis])
Frequency is 1, 2 or 4. Basis defaults to 0, US/NASD 30/360; alternatives are 1 actual/actual, 2 actual/360, 3 actual/365 and 4 European 30/360. Supply actual date values and decimal rates. Official YIELD reference.
The article calls the coupon rate “per period.” However, its complete published semiannual example agrees with an annual coupon rate divided by frequency, and disagrees with the literal per-period interpretation at the displayed precision. The calculation below makes that evidence explicit. Keep price and redemption on the same per-100-face quotation scale; do not mix a total holding value with a price quote.
Enter a complete annual example
In a fresh worksheet enter:
| Cell | Input |
|---|---|
| B2 | =DATE(2025;1;15) |
| B3 | =DATE(2027;1;15) |
| B4 | 0.06 |
| B5 | 96.433470507544582 |
| B6 | 100 |
| B7 | 1 |
| B8 | 0 |
Enter =YIELD(B2;B3;B4;B5;B6;B7;B8) in B10. The independent reference is 0.08, displayed as 8% with percentage formatting. The complete formula is:
=YIELD(DATE(2025;1;15);DATE(2027;1;15);0.06;96.433470507544582;100;1;0)
To verify the answer independently, substitute 0.08 into 6/(1+y) + 106/(1+y)^2. It reproduces the input price to its rounding precision. This is an inverse cash-flow calculation, not a claim that this formula was executed in Zoho.
For semiannual payments, use price 96.370104775743146 and frequency 2, leaving other inputs unchanged. Four coupons of 3, discounted at y/2, again imply nominal annual yield 0.08. The independent annual effective compounding equivalent is (1+0.08/2)^2-1 = 0.0816, or 8.16%; do not silently substitute this derived number for the function’s annual yield quote.
Resolve the help wording with the vendor demonstration
The official public worksheet shows February 1, 2018 settlement, April 12, 2020 maturity, rate 0.0233, price 500, redemption 100000, frequency 2 and basis 0. It displays 4.679083403. These unusual price/redemption figures are reproduced to identify the evidence, not as typical bond assumptions. Published YIELD example.
Independent US 30/360 arithmetic gives 109 accrued days, 71 days to the next coupon, a 180-day period and five remaining coupons. With c = 100*0.0233/2 = 1.165 and q = 1+y/2, solve:
500 = c/q^(71/180) + c/q^(1+71/180)
+ c/q^(2+71/180) + c/q^(3+71/180)
+ (c+100000)/q^(4+71/180) - c*109/180
The independent root is 4.67908340257729, matching the displayed digits. Using coupon 2.33, as the literal per-half-year reading would imply, produces 4.68007330515172, which does not match. This resolves the example’s rate interpretation without claiming access to Zoho’s implementation or an authored runtime test. A raw yield of 4.679 represents about 467.9% under percentage formatting, not 4.679%.
Practical controls and price sensitivity
With annual dates unchanged, price 100, coupon 0.06 and redemption 100 imply 6% in the independent model. A zero-coupon price of 82.644628099173554 implies 10%, because 100/1.1^2 reconstructs that price.
For the positive cash flows in these examples, increasing the discount rate lowers present value. This gives a useful direction check: a price below par with a positive coupon should not be interpreted as a lower yield than the coupon in this regular coupon-date model. Retain full price precision during calculations and round only presentation. Small changes in a rounded price can change the recovered rate.
Use PRICE for the forward question, and COUPNUM to inspect payment count. Neither a reconstructed price nor a quoted yield guarantees a realized return, reinvestment rate or payment.
Errors and model boundaries
Zoho lists #NUM! for negative price or coupon rate, unsupported frequency, basis outside 0–4, or settlement after maturity. The article does not settle every zero-value, redemption or coercion boundary, so those are not inferred here. Official error conditions.
These examples concern regular coupons, positive prices and redemption. They do not establish irregular coupons, holiday adjustments, solver tolerance, iteration limits or convergence for extreme inputs. No universal accuracy guarantee follows from a single published result. Use DATE to avoid locale-dependent text parsing.
Availability and evidence scope
Sources and the read-only vendor demonstration were checked October 18, 2026. The guide’s worked outputs are independent financial arithmetic, and the explicitly identified vendor row is published-example evidence. No authored Zoho workbook execution or screenshot is claimed. The rolling help does not establish an introduction release, historical retirement status or mobile parity. These are spreadsheet calculation examples, not investment advice.