Yield to maturity is the return a bond delivers if held to the end — the rate that makes its price equal the present value of its cash flows. RATE solves for it.
The example
Buy at $926, $1,000 face, $50 coupon, 10 yrs → ~6%.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Price | $926 |
| 3 | YTM | 6.0% |
The formula
The formula:
How it works
How it works:
RATE(periods, coupon, −price, face)finds the rate that equates the price you pay to the cash you receive.- The price is negative (cash out), the coupons and face positive (cash in).
- The result is the yield to maturity — the bond’s annualized return if held to maturity.
- It accounts for both the coupon income and any gain/loss from a discount or premium price.
YTM is iterative. RATE solves it by trial and error; if it fails to converge, supply a guess as the last argument. For real settlement dates, YIELD is the dedicated bond function.
Try it: interactive demo
Price, face, coupon $, years.
Variations
Current yield (simple)
Coupon over price:
Semiannual YTM
Double the rate:
YIELD (real dates)
Traded bonds:
Pitfalls & errors
Sign matters. Price negative (paid), coupon and face positive (received), or RATE returns nonsense.
May not converge. Add a guess argument if RATE errors.
YTM ≠ current yield. YTM includes the price gain/loss; current yield is just coupon/price.
Practice workbook
Frequently asked questions
How do I calculate yield to maturity in Excel?
Why is the price negative in the formula?
What's the difference between YTM and current yield?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class