Interpreting a Semiannual Coupon Bond Pricing Formula

Summary

The document presents an inherited spreadsheet formula intended to price a coupon bond from its coupon and current yield. Its variables describe months to maturity, a semiannual payment frequency, the number of remaining coupon payments, and the fraction of an accrual period elapsed. The expression discounts future payments using a yield-per-period factor and adjusts for the bond's position between coupon dates. A comment in the spreadsheet says accrued interest is included in the present value and identifies a possible subtraction to exclude it.

The author asks for a reference because the formula is difficult to interpret and mentions that related duration and convexity formulas are also undocumented. No answer or derivation is included, so the document does not establish whether the implementation is correct or explain its assumptions. Readers would need to check conventions such as coupon timing, yield compounding, settlement date, and accrued interest treatment against a standard bond-pricing framework before relying on the spreadsheet. Its value is primarily as an example of a pricing expression that warrants verification.

Key ideas

• The spreadsheet formula aims to price a coupon bond from coupon, yield, and months to maturity.
• It assumes semiannual coupon periods and computes the remaining payment count from maturity in months.
• A fractional-period term represents elapsed time since the last coupon date.
• The spreadsheet comment indicates that accrued interest is included unless an adjustment is applied.
• The document supplies no derivation or confirmation, so its conventions and correctness remain uncertain.

Tags

# bond price formula in excel


# bond price formula in excel












I inherited a excel spreadsheet that has the following code to price a bond given coupon and current yield

```
    'n - Number of future coupon payments till maturity
    'mtm - months to maturity
    'FREQ = 12
    n = Int((mtm - 1) / (FREQ / 2)) + 1
    f = 2
    X = 1 - (mtm Mod (FREQ / 2)) / (FREQ / 2) ' length of accrual period since last coupon date,  0 <=X<1
    If X = 1 Then
        X = 0
    End If
    'Last term is subtraction of accrued interest. The PV includes accrued interst, if need to exclude, uncomment
    pv = (cpn * (1 + yld / f) + (yld - cpn) / ((1 + yld / f) ^ n)) / (yld * ((1 + yld / f) ^ (1 - X))) '- X * cpn / f
```

Has anyone seen this formula that can point me to a reference. There are even more complicated formulas for duration and convexity. It seems to work but I can't make heads or tails out of it. Alas the documentation doesn't get any better

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.