Spreadsheet Bond Math

Excel and Google Sheets offer built-in functions to calculate bond prices, yields, and duration — no need to manually discount cash flows.

Key takeaways

• The PRICE function computes fair value given coupon, face, maturity, and required yield
• The YIELD function backs out the yield-to-maturity from a quoted price
• DURATION gives you a bond's interest-rate sensitivity in years
• These functions handle the day-count conventions and accrued interest details
• Automating bond math saves time and eliminates arithmetic errors

The PRICE function

The PRICE function takes the settlement date, maturity date, annual coupon rate (as a decimal), the required yield (annual, as a decimal), the face value, and payment frequency, then returns the price.

In Excel and Google Sheets:

=PRICE(settlement, maturity, rate, yld, redemption, frequency)

Example:

• Settlement: 1/15/2025 (today)
• Maturity: 1/15/2030 (5 years out)
• Coupon rate: 4% (0.04)
• Required yield: 5% (0.05)
• Face value: $1,000
• Frequency: 2 (semi-annual)

=PRICE("1/15/2025", "1/15/2030", 0.04, 0.05, 1000, 2)

This returns approximately $956.75 — the same bond we hand-calculated in the previous article, now computed in one cell. No more summing ten discounted cash flows.

The function assumes you settle on the stated date and hold to maturity. It also accounts for fractional periods if you settle between coupon dates, automatically adjusting for accrued interest.

The YIELD function

YIELD is the inverse of PRICE. You feed it a market price and it solves for the yield-to-maturity (YTM).

=YIELD(settlement, maturity, rate, price, redemption, frequency)

Example:

• Settlement: 1/15/2025
• Maturity: 1/15/2030
• Coupon rate: 4% (0.04)
• Market price: $960
• Face value: $1,000
• Frequency: 2 (semi-annual)

=YIELD("1/15/2025", "1/15/2030", 0.04, 960, 1000, 2)

This returns approximately 0.0491, or 4.91% — the annualized yield implied by the $960 price.

In practice, you use YIELD to:

1. Check a bond broker's quote: if they're showing a 5% yield, does your YIELD function confirm it?
2. Compare bonds: calculate the YTM of two competing options and pick the higher-yielding one (controlling for credit quality, duration, and liquidity).
3. Track changes: if a bond was 4.8% yesterday and 4.91% today, the price fell as yields rose.

The DURATION function

Duration measures a bond's sensitivity to interest-rate changes. A bond with a 5-year duration will roughly lose 5% in price if rates rise by 1%.

=DURATION(settlement, maturity, coupon, yield, frequency)

Example (same bond as before, but now at the current yield): =DURATION("1/15/2025", "1/15/2030", 0.04, 0.0491, 2)

This returns approximately 4.3 years. This bond is less sensitive than its 5-year maturity because it pays coupons; the regular cash inflows bring the weighted average time of your money back down.

Duration is crucial for building a bond portfolio. If you want a specific interest-rate sensitivity (say, a 3-year average duration), you blend short and long bonds to hit that target.

Date handling and conventions

One source of confusion: what do settlement and maturity dates actually mean?

• Maturity: the date the issuer repays principal. Non-negotiable; it's in the bond contract.
• Settlement: the date you become the owner and accrue interest. For most bonds, this is 2 business days after you buy (the "T+2" convention). When you're pricing a bond on your spreadsheet, use today's date or the date you're modeling.

If you input dates incorrectly, the function returns an error or a nonsense answer. Always double-check that the dates are text strings in quotes or actual date cells.

Accrued interest and clean vs. dirty prices

The PRICE function returns the dirty price — the actual amount you pay, including accrued interest. When you see bond quotes in the market, they often display the clean price (excluding accrued interest) and then add accrued interest separately at settlement.

For most spreadsheet-based work, the dirty price is what you use because it's the cash out of your pocket.

Some spreadsheet builders prefer to separate the two:

Accrued interest = coupon × (days since last coupon / days in coupon period)

Clean price = dirty price – accrued interest

This distinction matters for tax calculations and for matching your settlement invoice.

Building a bond analytics worksheet

A common setup for managing a bond portfolio is a table with columns:

• Ticker / CUSIP
• Coupon rate
• Maturity date
• Market price
• Settlement date
• Quantity held

Then, in adjacent columns:

• YTM (using YIELD)
• Duration (using DURATION)
• Current yield (coupon / price)
• Value of position (price × quantity)
• Interest earned to date (accrued interest)

One row per bond. As prices update, the YTMs and durations update automatically. You can sort by YTM to find the highest-yielding bonds, or by duration to see which are most vulnerable to rate rises.

Handling callable bonds

A wrinkle: callable bonds can be redeemed before maturity if rates fall. The PRICE and YIELD functions assume a simple maturity, not a call date.

For callable bonds, some market participants use yield-to-call (YTC) instead of YTM. You can model this manually:

=YIELD(settlement, call_date, rate, price, par, frequency)

Use the call date instead of maturity, and assume the issuer redeems at par ($1,000). This gives a more conservative yield estimate if rates are likely to fall and the bond is called away.

More sophisticated analysis uses real-time callable bond analytics software, but for a personal portfolio, YTC is a helpful sanity check.

Floating-rate bonds and OID bonds

Standard coupons are fixed. Floating-rate bonds adjust their coupon each period based on a reference rate (e.g., SOFR). PRICE and YIELD work only for fixed-coupon bonds.

For floating-rate bonds, the price typically stays near par because the coupon resets. You'd model it differently, forecasting the floating coupon and discounting accordingly.

Bonds issued below par (Original Issue Discount, or OID, discussed later) also complicate the PRICE function's interpretation; you need to track cost basis for tax purposes separately.

Practical example: comparing two bonds

Scenario: You're choosing between Bond A and Bond B.

Bond A:

• 3.5% coupon
• Matures 12/15/2032
• Quoted at $982
• Semi-annual payments

Bond B:

• 4.2% coupon
• Matures 6/15/2033
• Quoted at $1,025
• Semi-annual payments

Both are high-grade corporates. Which is the better value?

Set up a spreadsheet:

Bond	Coupon	Maturity	Price	YTM	Duration
A	0.035	12/15/2032	982	YIELD(...)	DURATION(...)
B	0.042	6/15/2033	1025	YIELD(...)	DURATION(...)

Plug in the YIELD and DURATION formulas. You find:

• Bond A: YTM 3.8%, Duration 6.5 years
• Bond B: YTM 4.0%, Duration 7.2 years

Bond B has a higher yield (4.0% vs. 3.8%), but it's also more sensitive to rate moves (7.2-year duration vs. 6.5 years). If you think rates will rise, Bond A's shorter duration is appealing. If you want income and rates are stable, Bond B's 4% yield is superior.

Both are defensible; the spreadsheet lets you see the trade-offs clearly.

Error-checking

Common pitfalls:

1. Coupon as percentage, not decimal: input 0.035, not 3.5.
2. Dates in wrong format: Excel wants serial date numbers or text in a recognized format. If a PRICE call returns #NUM!, check your dates first.
3. Frequency mismatch: Use 2 for semi-annual, 1 for annual, 4 for quarterly. Get this wrong and the result is way off.
4. Maturity before settlement: a bond can't mature before you own it. Swap them if PRICE errors.

A quick sanity check: if coupon < yield, the price should be less than par. If coupon > yield, price should be more than par. If they don't align, revisit your inputs.

Summary: automation in practice

The PRICE, YIELD, and DURATION functions transform bond math from hand calculations into one-line formulas. This frees you to focus on strategy: which bonds to buy, what durations to target, how to rebalance.

For individual investors, these functions are available in any spreadsheet suite. For institutional portfolios, dedicated fixed-income analytics platforms integrate these calculations with real-time pricing and risk models. But the logic beneath is the same: discount cash flows, find yields, measure sensitivity.

Related concepts

• ./13-pricing-a-bond-with-pv.md
• ./06-yield-to-maturity-ytm.md
• ./03-maturity-and-tenor.md

Decision tree

Next

Spreadsheet formulas give you the nominal yield — the raw number. But inflation erodes that yield. In the next article, we subtract inflation to find the real yield: the purchasing power you actually gain.