Bond Ladder Builder Calculator

Purpose and model boundary

This calculator builds a ladder of up to 10 bonds bought today and held to maturity. For each bond you enter its face amount, its yearly coupon, the year it matures counted from today and the price you pay per 100 of face; you also choose whether bonds that mature before the last one are reinvested, and if so at what yield. The workbook lays out every year's coupons and returned principal, finds each bond's yield to maturity and the yield of the whole ladder, and works out the ladder's duration. The main result is the yield of the whole ladder: the yearly rate at which every coupon and principal payment, discounted to today, adds up to what the ladder costs.

Every figure on this page is computed by the spreadsheet; the page adds nothing to it. To study one bond in depth, with its price, duration and convexity, use the Bond Price Yield Duration Convexity Calculator; for the income a dividend stock pays over the years instead, the Dividend Growth Yield on Cost Calculator.

The model is educational. It does not quote prices or yields, and it is not financial, investment, tax or legal advice.

Inputs and units

• The bonds in your ladder, one per row from the first, up to 10. Each row you use needs its face amount, from 1 to 1,000,000,000 dollars; its yearly coupon, from 0% to 20% of face; the year it matures, a whole number from 1 to 30 counted from today; and the price you pay, from 1 to 200 per 100 of face. Blank rows are ignored.
• Reinvest bonds that mature early: Yes or No.
• Yield on reinvested principal, from 0% to 20% a year. It is asked for, and used, only when you choose to reinvest.

At least one bond is required. Money is in US dollars; coupons and yields are yearly percentages.

Governing relationships

Coupons are paid once a year, and each bond is bought on a coupon date, so the price you pay is the whole price, with no accrued interest. A bond of face F, coupon c and maturity m pays F × c at the end of each year up to m and its face F at the end of year m; it costs F × price / 100.

A bond's yield to maturity is the yearly rate y at which its payments, discounted to today, equal its price:

price / 100 = Σ (payment per 1 of face in year t) / (1 + y)^t

The ladder's yield is the same rate for all the ladder's yearly cash flows against what the whole ladder costs. Its Macaulay duration is the average time until those payments arrive, each weighted by its value today at the ladder yield, and the modified duration is the Macaulay duration divided by 1 plus the ladder yield. Every payment after today is positive, so each search has exactly one answer.

With reinvestment, a bond that matures before the last one rolls its face amount, at par, into a new bond that pays the reinvestment yield each year and matures with the last bond. Its new coupons are paid to you as income, and its principal comes back in the ladder's last year, so every face amount comes back exactly once. Without reinvestment, each bond's face comes back in the year it matures.

Calculation sequence

The spreadsheet first checks every row and input. For each bond in use it writes the payment per 1 of face in each of 30 years and finds its yield by halving: the search starts with the range −50% to 100% a year and halves it 48 times, keeping the half where the discounted payments still exceed the price, so the answer is within about 3e-15 of the exact rate. No iterative calculation, solver or yield function is used. It then lays out the ladder's coupons, coupons on reinvested principal, principal paid back and principal rolled for each year, finds the ladder's yield the same way, and works out the durations, the cost, the first year's coupon income and the totals. A block of consistency checks confirms the arithmetic before the status is returned.

Outputs and interpretation

The headline is the yield of the whole ladder. Beside it are the ladder's Macaulay duration, what it costs today and its coupon income in the first year. The tables show each bond with its cost, yield to maturity, duration and yearly coupon, and each year's cash flows until the last bond matures. The modified duration, the total of every coupon and principal payment, the face-weighted average maturity, the total face amount, the year the last bond matures and the workbook's self-check are returned to the API and MCP.

With the default inputs, one 10,000-dollar bond paying 4% a year, maturing in year 5 and bought at 98.50, the ladder costs 9,850 dollars, pays 400 dollars of coupons in the first year and yields 4.340% a year, above its coupon because it was bought below par; its duration is 4.63 years. The five-year ladder example, one 10,000-dollar bond maturing in each of years 1 to 5 with coupons from 4.0% to 4.4%, costs 49,810 dollars, yields 4.403% and has a duration of 2.84 years. Rolling its early bonds into year 5 at 4.5% raises the yield to 4.440% and the duration to 4.61 years, because the principal now comes back in year 5, with 52,240 dollars of cash that year. Ten par bonds paying 4.5% yield exactly 4.5%.

The first chart shows the cash the ladder pays each year: the coupon income and, in the years bonds mature, the principal paid back. The second chart places each bond as a dot at its maturity and its yield to maturity, with the dashed line at the yield of the whole ladder; two bonds maturing in the same year are two dots at one year.

Validation and status logic

The workbook evaluates the conditions below from top to bottom and returns the first matching status.

NOT VALID is a refusal, not a numeric answer: every number reads zero and the tables are empty. CHECK accompanies a computed result but calls for the stated review. The self-check is reported separately as arithmetic-integrity evidence; it does not replace this ordered status chain.

Assumptions and limitations

Coupons are paid once a year and each bond is bought on a coupon date; real bonds often pay twice a year and are bought between coupon dates with accrued interest. Every bond is held to maturity, with no default, call or sale, and there is no tax. Reinvested principal rolls once, at par, into the ladder's last year; a ladder that keeps buying a new longest bond forever is not modelled. Amounts are nominal dollars.

Restrictions and non-computing states

This calculator enforces its numeric and choice limits before it accepts a result. Every row in use must be complete, maturities are whole years from 1 to 30, and at least one bond is needed. The reinvestment yield is checked only when you reinvest; when you do not, it is hidden and ignored. A yield beyond −50% or 100% a year is shown at that end of the range, with the CHECK status above.

Errors and warnings

A request rejected by the page's input limits is not a computed result. A transport or service failure cannot establish a fresh workbook answer. Review the workbook's References and Disclaimer, and check any purchase with a qualified professional before using a result for a real decision.

Sources and further reading

The face amounts, coupons, maturities, prices and reinvestment yield come from the inputs, not market data: the model is discounting and simple arithmetic, and it uses no table, rate or figure from any publication. The workbook's References sheet identifies the sources below. The links explain the methods the model follows; they do not endorse this calculator.

• The Bond Price Yield Duration Convexity Calculator on this site uses the same conventions for one bond: a yield found by a bracketing search, the Macaulay duration weighted by present value, and the modified duration as the Macaulay duration divided by 1 plus the yield.
• Microsoft Support's SUMPRODUCT, SMALL and INDEX documentation describe the functions the workbook uses to discount the payments and to sort the bonds.

Each yield is found by halving a range of rates 48 times; the methodology states every relationship the workbook uses.

This is an educational estimate, not financial, investment, tax or legal advice. Bond prices, yields and terms differ in practice, and real bonds can default, be called or be sold before maturity; check any purchase with a qualified professional.