Stripping a Bond in Excel

Stripping a bond means separating its principal and interest payments into two different securities that can be sold individually. For example, suppose a bond has a face value of $1,000 and pays a 5% coupon annually for five years. An investor who buys this bond will receive $50 every year and $1,000 at the end of five years. However, another investor may be interested in buying only the principal or only the interest payments of this bond. This is where stripping a bond comes in handy.

An investment firm or dealer can buy this bond and strip it, meaning they detach the principal and the interest payments and sell them separately. The principal becomes a zero-coupon bond, which means it does not pay any interest but is sold at a discount and matures at the face value. The interest payments become a series of coupons, which are also sold at a discount and pay a fixed amount every year. The investment firm or dealer makes money by charging a fee for this service and by selling the stripped securities at a higher price than what they paid for the original bond.

The advantage of stripping a bond is that it creates more options for investors who have different preferences and risk profiles. Some investors may prefer a zero-coupon bond because they do not want to worry about reinvesting the interest payments or because they want to lock in a certain return. Other investors may prefer a series of coupons because they want a steady income stream or because they expect interest rates to fall. Stripping a bond also allows investors to customize the maturity and duration of their portfolio by choosing different combinations of stripped securities.

Basic Theory:

When a Treasury bond is issued, it typically pays periodic interest (coupon payments) and returns the principal at maturity. Stripping a bond separates these two cash flows into individual securities, known as STRIPS. The resulting STRIPS consist of zero-coupon securities, with each representing either the future interest payments or the principal repayment.

Procedures for Stripping a Bond in Excel:

1. Gather Bond Information: Begin by collecting essential information about the bond, such as its face value, coupon rate, time to maturity, and the current market interest rate.
2. Calculate Present Value of Future Cash Flows: Using the present value formula, calculate the present value of future interest payments and principal repayment. This involves discounting each future cash flow back to its present value using the market interest rate.
3. Create STRIPS: Separate the interest payments and principal repayment into individual securities. For each future cash flow, create a zero-coupon bond with the corresponding present value.
4. Build a STRIPS Table: Construct a table in Excel to summarize the details of each STRIP, including the payment date, cash flow type (interest/principal), and the associated present value.

Example Scenario:

Let’s consider a $1,000 face value Treasury bond with a 5% coupon rate, 5 years to maturity, and a current market interest rate of 3%.

1. Calculate Present Value: Using the present value formula in Excel, calculate the present value of the bond’s future cash flows.
• Future Interest Payments (Years 1-5): $50 each year
• Principal Repayment (Year 5): $1,000
2. Create STRIPS: Based on the calculated present values, create individual STRIPS for interest payments and principal repayment.
3. Build a STRIPS Table: Create a table in Excel to showcase the details of each STRIP, including payment date, cash flow type, and present value.
4. Result: Summing up the present values of all STRIPS will equal the bond’s current market price.

Other Approaches:

• Yield to Maturity (YTM): Calculate the yield to maturity for the bond and discount each cash flow using this rate to obtain the present values.
• Using Excel Functions: Excel provides functions like PRICE and YIELD specifically designed for bond valuation. These functions can simplify the process of calculating present values and yields.