Why Spreadsheet Structure Matters
A bootstrap is sequential: to solve for a discount factor at a new maturity, the present values of the bond’s earlier cash flows must already be known. A spreadsheet makes this sequence visible and repeatable, but only if its dates, cash flows, formulas, and checks are organized carefully. A reference to the wrong date or an inadvertently rounded factor can produce a plausible-looking curve that does not actually reproduce the input prices.
The setup here assumes that each bond price is a value at a common valuation date and is consistent with its listed contractual cash flows. There are no embedded options, taxes, or default adjustments in the pricing model. Use prices and cash flows on a consistent basis, including settlement and accrued-interest treatment where applicable. The spreadsheet uses discount factors directly; a factor \(D(t)\) discounts a cash flow due at time \(t\) to the valuation date.
A Practical Workbook Layout
Keep the curve and the bond cash flows in separate tables. The curve table should contain one row for each date at which a factor is to be stored. The cash-flow table should contain one row per payment, not one row per bond. This makes coupon dates explicit and allows each bond to have a different number of payments.
| Curve table column | Purpose |
|---|---|
| Maturity date | Identifies the date associated with a discount factor. |
| Time \(t\) | Stores the year fraction from the valuation date under the chosen time convention. |
| Discount factor \(D(t)\) | Stores a known factor or the factor solved at this maturity. |
| Status | Records whether the factor is known, solved, or still missing. |
| Cash-flow table column | Purpose |
|---|---|
| Bond identifier | Connects each payment to its input bond. |
| Payment date and time | Identifies when that payment occurs. |
| Cash-flow amount | Stores the coupon, redemption amount, or combined payment on that date. |
| Discount factor | Retrieves the factor for that payment date once available. |
| Present value | Multiplies the payment by its matching factor. |
If a bond has a coupon and redemption on the same date, combine them into one cash flow or keep them as two rows with the same date and factor. Either approach gives the same present value. The curve table should be sorted by increasing maturity, and each bond should be processed only after the factors for all of its earlier payment dates are known.
In a spreadsheet, use an exact-match date lookup for a payment date with a stored factor. For example, an Excel XLOOKUP can use the payment-date column as its lookup range, the curve’s discount-factor column as its return range, and exact match mode. If a date is not found, return a visible missing-value indicator rather than substituting a nearby factor. A missing date is a data or sequencing issue to resolve, not permission to interpolate silently.
Solving for the Terminal Factor
Suppose a bond has price \(P\), earlier cash flows \(C_j\) at dates \(t_j<T\), and a terminal cash flow \(C_T\) at maturity \(T\). If all earlier factors are known, its pricing equation is
Isolating the only unknown gives the spreadsheet’s sequential solve:
This formula requires a nonzero terminal cash flow and known factors for every earlier cash flow. If an earlier payment date has no factor, the earlier present-value sum is incomplete. If more than one factor in the equation is unknown, one bond price alone does not determine all of them; the sequential formula does not apply until the missing information is resolved.
Enter each input bond’s price and each dated cash flow. Use a consistent valuation date, price basis, and year-fraction convention.
Sort the factor dates from earliest to latest. Start with factors already available from the shortest-maturity instruments.
For the next bond, retrieve the factor for each payment date before maturity and multiply it by that payment’s cash flow.
Subtract the earlier present-value sum from the bond price, divide by the terminal cash flow, and store the result at the exact maturity date.
Use the stored factors for every payment date and compare the calculated price with the input price. Investigate any material difference.
Worked Spreadsheet Examples
Worked Example: Building a Three-Maturity Curve
Three hypothetical annual-payment bonds have prices per 100 of face amount as follows. Bond A pays 105 at year 1 and costs 99.75. Bond B pays 4 at year 1 and 104 at year 2 and costs 97.40. Bond C pays 3 at year 1, 3 at year 2, and 103 at year 3 and costs 93.10. Find the discount factors and show the repricing checks.
Method. Enter each cash flow on its own row, sort the maturity dates, and solve one terminal factor at a time. Retain the unrounded factors in the spreadsheet; the displayed values below are rounded to eight decimal places.
Year 1. Bond A has only one payment, so its price is \(99.75=105D(1)\). Therefore,
Repricing Bond A gives \(105(0.95)=99.75\), matching its input price.
Year 2. Bond B’s year-1 payment has present value \(4(0.95)=3.80\). Its remaining price is \(97.40-3.80=93.60\), so
Repricing Bond B gives \(4(0.95)+104(0.90)=3.80+93.60=97.40\).
Year 3. Bond C’s earlier payments have present values \(3(0.95)=2.85\) at year 1 and \(3(0.90)=2.70\) at year 2. Their total is \(2.85+2.70=5.55\). The amount of the price attributable to the terminal payment is \(93.10-5.55=87.55\). Thus,
The repricing check is \(3(0.95)+3(0.90)+103(0.85)=2.85+2.70+87.55=93.10\). All three calculated prices match their inputs. The common mistake this method avoids is treating the full bond price as the value of the terminal cash flow without first subtracting the earlier coupons’ present values.
Worked Example: Using Exact Dated Cash Flows
A hypothetical bond has a price of 98.00. It pays 2.00 on a date with known factor \(D(t_1)=0.985\), 3.00 on a later date with known factor \(D(t_2)=0.940\), and 103.00 at its final payment date \(T\). Assume those two earlier dates and factors are already present in the curve table. Find \(D(T)\) and verify the price.
Method. Match the two earlier payments to their exact dates in the curve table, calculate their present values, and use the remaining price to solve for the terminal factor. The payment amounts are not themselves discounted at the terminal date’s factor.
The first earlier payment has present value \(2.00(0.985)=1.970\). The second has present value \(3.00(0.940)=2.820\). Together they contribute \(1.970+2.820=4.790\). The price remaining for the terminal payment is \(98.000-4.790=93.210\). Therefore,
The factor is displayed to eight decimal places; the workbook should retain the full calculated precision. Repricing gives \(1.970+2.820+103(0.9049514563\ldots)=1.970+2.820+93.210=98.000\). The check matches the input price.
A spreadsheet should flag a payment date that does not exactly match a curve date. Using the factor from a nearby date would change the earlier present-value sum and therefore change the solved terminal factor. This is particularly important when coupon dates are irregular.
Worked Example: Adding Automatic Repricing Checks
A workbook already contains \(D(0.5)=0.975\). Bond A costs 99.00 and pays 2.00 at year 0.5 and 102.00 at year 1.5. Bond B costs 100.4835294118 and pays 3.00 at year 0.5, 3.00 at year 1.5, and 103.00 at year 2. Find the two unknown factors in sequence and show how the repricing check detects whether the formulas are consistent.
Method. First solve Bond A’s terminal factor, store it at year 1.5, and then use that stored factor as an earlier factor for Bond B. In the cash-flow table, the present-value formula for each row is its cash flow multiplied by the exact-date factor. The bond-level repriced price is the sum of that bond’s present-value rows.
For Bond A, the year-0.5 payment has present value \(2(0.975)=1.950\). The remaining price is \(99.000-1.950=97.050\). Therefore,
Bond A reprices as \(1.950+102(0.9514705882\ldots)=1.950+97.050=99.000\).
For Bond B, the known earlier present values are \(3(0.975)=2.925\) at year 0.5 and \(3(0.9514705882\ldots)=2.8544117647\) at year 1.5. Their sum is \(5.7794117647\). Subtracting from the bond price gives \(100.4835294118-5.7794117647=94.7041176471\), so
Bond B reprices as \(2.925+2.8544117647+103(0.9194574529\ldots)=2.925+2.8544117647+94.7041176471=100.4835294118\), up to the displayed precision. In the spreadsheet, calculate the repriced price from the cash-flow rows rather than copying the input price into the check. A formula that accidentally references the input-price cell would show a zero residual without testing the bootstrap at all.
Repricing, Precision, and Common Traps
For each bond, define the repricing residual as the calculated price minus the input price. A correct calculation should give a residual of zero apart from small differences caused by displayed or stored precision. Keep full precision in factor cells and round only for presentation. If the workbook stores rounded factors and uses them in later solves, rounding error can accumulate across maturities.
A useful check table has one row per bond and columns for the input price, repriced price, and residual. A conditional format can highlight a residual exceeding a chosen tolerance. The tolerance should reflect the units and precision of the input prices; it is a diagnostic threshold, not a replacement for using correct formulas.
- Subtract earlier present values, not earlier cash-flow amounts. Coupons must first be multiplied by their date-specific discount factors.
- Do not use approximate date matches. A nearby date’s factor is not the factor for the cash flow unless a separately specified interpolation method is being used.
- Do not include the terminal cash flow in the earlier present-value sum. The terminal amount is the denominator in the sequential solve.
- Do not confuse a solved factor with a bond price. A factor is dimensionless; its product with a cash-flow amount is a present value in the same monetary units as the price.
- Do not mistake a rounded display for the stored value. Later formulas should refer to the full-precision cell.
For example, suppose a bond costs 98.00, pays 3.00 at an earlier date with known factor 0.94, and pays 103.00 at maturity. The earlier payment’s present value is \(3(0.94)=2.82\). The terminal factor is therefore \((98.00-2.82)/103=95.18/103\), which is approximately 0.9240777, or 0.9241 to four decimal places. Using 98.00 directly as the terminal payment’s present value would ignore the earlier coupon and give the wrong result.
Check Your Understanding
For each problem, assume prices and cash flows are on a consistent basis and use full precision in intermediate calculations.
- A bond costs 101.00, pays 4.00 at a date with known factor 0.98, and pays 104.00 at maturity. What terminal factor should the spreadsheet calculate? (Answer: 0.9335)
- A bond costs 99.00, pays 5.00 at year 1 and 105.00 at year 2. If \(D(1)=0.96\), find \(D(2)\) and state the repriced price. (Answer: \(D(2)=0.8971\); repriced price \(=99.00\))
- A bond costs 100.00 and pays 2.00 at a date with factor 0.99, 4.00 at a later date with factor 0.93, and 104.00 at maturity. Find the terminal factor and describe the date-matching check the spreadsheet should use. (Answer: \(D(T)=0.9067\); require exact matches for both earlier payment dates)
- A first bond costs 98.50 and pays 3.00 at year 1 and 103.00 at year 2. Given \(D(1)=0.97\), solve for \(D(2)\). A second bond costs 99.00 and pays 2.00 at year 1, 2.00 at year 2, and 102.00 at year 3. Using the factor just found, solve for \(D(3)\). (Answer: \(D(2)=0.92708738\); \(D(3)=0.93339044\))