Bond price, yield to maturity and accrued interest calculator

Coupon dates run back from maturity. A is the days from the previous coupon to settlement, E the days in that coupon period and DSC the days to the next coupon, counted with the chosen day-count basis. With N coupons left, the clean price is the spreadsheet PRICE formula; yield is found by solving it for the yield (YIELD).

Price = R/(1 + y/f)^(N−1+DSC/E) + Σ_{k=1..N} (100c/f)/(1 + y/f)^(k−1+DSC/E) − (100c/f)(A/E)
Accrued interest = (100c/f) × A/E        Dirty price = clean price + accrued interest
With one coupon left: Price = (100c/f + R)/(1 + (E−A)/E × y/f) − (100c/f)(A/E)

Day counts: 30/360 bases count every month as 30 days (US NASD and European rules differ at month ends) and E = 360/f; actual/actual uses the real days in the coupon period; actual/360 and actual/365 count real days with E = 360/f or 365/f. Macaulay duration weights each cash flow’s time by its present value; modified duration divides it by (1 + y/f).

Assumptions and limits

• Fixed-rate bonds with regular coupons only: odd first or last coupons, ex-dividend periods, call features and inflation linking are not modelled.
• Coupon dates follow the end-of-month rule: a maturity on the last day of a month keeps coupons on month ends.
• At US 30/360 month ends, spreadsheet implementations differ slightly; this tool follows the SIA rule.
• An educational calculation, not financial, tax, legal or investment advice.

Worked example

Microsoft’s examples: settlement 2008-02-15, maturity 2017-11-15, 5.75% coupon, 6.5% yield, semi-annual, 30/360 gives a clean price of 94.6343616; with maturity 2016-11-15 and price 95.04287, the yield is 6.50%. A bond settling 2008-01-01, maturing 2016-01-01, 8% coupon at a 9% yield, actual/actual, has a modified duration of 5.7357.

References

• Microsoft Excel: PRICE function
• Microsoft Excel: YIELD function
• Microsoft DAX: DURATION function
• Microsoft DAX: MDURATION function
• Microsoft DAX: PRICE function and day-count bases