Financial Model: Bond Ladder

This post introduces a new financial model — an Excel/VBA implementation of a bond ladder portfolio and summary. Its general purpose is to calculate an average weighted yield for a ladder of bonds.

Overview

While a bond’s yield is easy to calculate using Excel’s built-in YIELD formula, this financial model provides a more robust set of features. Using the model, you can:

  • Track a bond ladder’s yield to maturity (YTM) over time.
  • View a schedule of coupon payments.
  • See how each rung of the bond ladder impacts the portfolio as a whole.

There are likely two types of visitors to this post.

  1. For my regular readers of VBA tutorials, this post provides an overview of the features in the financial model. If you’re not familiar with bond ladders, you can get a general idea of what the model tries to accomplish through this brief post on bond ladders. My next post will provide a case study overview of the VBA techniques and implementation (subscribe to get notified!).
  2. If you came here looking for a bond ladder model, look no further! You can download directly in the next section. If you find it useful, please consider subscribing or sharing this post on social media.

Bond Ladder File

You can download the financial model directly through this link. Macros must be enabled upon opening. This file is for demonstration purposes only.

Model Usage and Purpose

This model provides provides historical data on a bond ladder portfolio. While it’s easy to take a snapshot of a bond ladder at a specific point of time, it’s not particularly easy to show historical trends, such as weighted YTM or coupon payments over time.

Information and Assumptions

While the model can be used to track historical YTM for a bond ladder, it will not track any forward looking yield, or make historical adjustments for bonds which are sold or called before maturity. Even though the model does permit the sale/calling of bonds, its general premise is to only provide forward-looking YTM data (based on current open positions), not total return.

This Model vs. Excel’s YIELD Function

Bond ladders are very fluid — they evolve over many years through bond maturities, reinvestment, and new funds. This sequence of events—taking place over time—requires an algorithmic approach to calculate data for each period of the bond ladder’s existence.

Excel’s YIELD function provides a useful application-level approach to determining a specific bond’s YTM. This is based off of a bond’s purchase price, par value, purchase date, maturity date, and coupon rate. Regardless of its ease of use, the YIELD function will only provide a YTM for a specific bond purchased at a specific date. Given that bond ladders are made up of multiple bonds, lots, and transactions, our model requires complex calculations. To summarize each step:

  1. Yields are calculated for each individual transaction. This is done using Excel’s YIELD function, as mentioned above.
  2. If a rung includes more than one transaction, a weighted average YTM is compiled for each month when a transaction takes place. This is then stored individually for each month of the bond’s life.
  3. The weighted average YTM is calculated for the entire bond ladder. This is also stored individually for each month of the bond ladder’s life.

General Instructions

The financial model file contains a set of pre-populated sample data showing a 10-year bond ladder (with an additional 3 years which previous matured). It’s worth taking a look around the model with this data present and testing some functionality. All sample data can be removed using the “Reset All Data” button at the bottom of the “Dashboard” Tab.

Navigation tabs at the top of the spreadsheet (row 3) allow you to navigate between different functions of the model. Each function is described in further detail in the following sections.

Dashboard

The Dashboard screen presents the charts and key details associated with the bond ladder. This includes historical information about bond ladder YTM and coupons. You can see a year-over-year comparison by changing the date toggles in each section.

Charts in the dashboard present the portfolio average YTM over time.
Charts in the dashboard present the portfolio average YTM over time.

Transactions

The Transactions screen provides a table list of all transactions associated with the bond ladder. This will include multiple transactions if single rungs are built over time. While this screen does not permit you to create a new transaction, transactions can be edited by clicking the “Edit” button on the right side of the table.

The Transaction Detail screen shows a list of all transactions associated with the bond ladder. Prior transactions may be edited in this screen.
The Transaction Detail screen shows a list of all transactions associated with the bond ladder. Prior transactions may be edited in this screen.

Rungs

The Rungs screen is where the core bond ladder management functions exist. In addition to presenting a portfolio-like view of the bond ladder, rung positions can be added (purchased), removed (sold), or flagged for maturity. You will find the following functionality:

  • Create new rung: Click the “New Rung” button above the left side of the table. This will open the “Purchase Treasury” form where you can enter transaction details and information about the bond that will be associated with the rung. Only one bond can be associated with each rung.
  • Add (buy) or remove (sell) from existing rung: Click the “Buy” or “Sell” buttons to the left of a rung entry in the table. The “Purchase Treasury” or “Sell Treasury” forms will appear allowing you to either buy or sell shares of an existing rung. You can also edit bond data in the “Purchase Treasury” screen, but keep in mind that it will update information for all transactions associated with that bond.
  • Flag a rung (bond) as matured: Click the “Mat” button to the right of a rung entry in the table. This will effectively mark all shares of a rung as sold during the maturity date. Initiating this cannot be reversed.

Also, a few notes on some data within the rung table. The column “YTM” (3rd from the right) shows the weighted average YTM of all transactions associated with that specific rung. To the right of that, the “Portfolio Weight” column shows the percentage of the entire bond ladder that the specific rung uses. To the right of that, the “Weighted YTM” column shows the weighted YTM for that rung as a proportion of the entire bond ladder (this is calculated by taking the data in the “YTM” column and multiplying it by the “Portfolio Weight” column). The sum of the entire “YTM” column represents the blended YTM for the entire bond ladder.

Note About Quotes

Most financial institutions will quote a bond as a percentage of par value. A quote of 110.50 will translate to 10 shares of one bond at 110.50, for a total value of 1105.00. This financial model has adopted this pricing approach.

Coupons

The Coupons screen shows a schedule for coupon payments in table form. This screen is strictly informational and cannot be used to modify any positions or transactions.

Updates

I will provide periodic updates for this financial model and will post all changes here. If you encounter any bugs or have any feature requests, please let me know.

Other Notes

  • If you sell a bond before maturity (or if the bond is called), the model does not adjust prior YTM data to reflect realized (actual) yield.
  • Question marks in green circles are scattered throughout the model’s forms and tables. When clicked, a tooltip will appear advising on data input and providing other information.

Leave a Reply