Understanding Zero-Coupon Bonds and STRIPS Prices with Excel Formulas

A bond zero-coupon is a type of bond that does not pay any interest to the investor during its term. Instead, the bond is sold at a discount to its face value and pays the full face value at maturity. The difference between the purchase price and the face value represents the return on the bond. For example, if you buy a bond zero-coupon with a face value of $1,000 for $800 and hold it until maturity, you will receive $1,000 at the end of the term and earn a return of $200.

A strip bond is a bond that has been separated into its principal and interest components, which are sold individually as new securities. Each component becomes a zero-coupon bond that pays only one payment at maturity. For example, if you buy the principal component of a bond with a face value of $1,000 for $600 and hold it until maturity, you will receive $1,000 at the end of the term and earn a return of $400. If you buy the interest component of the same bond for $200 and hold it until maturity, you will receive the interest payment at the end of the term and earn a return of $50.

The prices of bond zero-coupon and strip bonds are determined by the present value of their future payments, which depends on the time to maturity and the prevailing interest rates in the market. The longer the time to maturity, the lower the present value and the lower the price. The higher the interest rate, the lower the present value and the lower the price. Therefore, bond zero-coupon and strip bonds are more sensitive to interest rate changes than regular bonds that pay periodic interest. This means that their prices can fluctuate more significantly in response to market conditions.

Basic Theory:

The price of a zero-coupon bond or STRIPS is determined by discounting its future cash flows (the face value at maturity) back to the present value. The formula for calculating the price ( $P$ ) of a zero-coupon bond is given by:

$P = \frac{F}{(1 + r)^n}$

Where:

$P$ is the price of the bond,
$F$ is the face value of the bond,
$r$ is the annual interest rate (yield to maturity) expressed as a decimal,
$n$ is the number of years to maturity.

Procedures:

Open Microsoft Excel and create a new spreadsheet.
Label cells A1 to A5 as follows: “Face Value,” “Annual Interest Rate,” “Years to Maturity,” “Yield to Maturity,” and “Price.”
Input the necessary information for your scenario into cells B1 to B4.
In cell B5, enter the formula to calculate the bond price using the formula mentioned above.

Explanation:

Let’s consider a scenario where you have a zero-coupon bond with a face value of $1,000, an annual interest rate of 4%, and a maturity period of 5 years. The yield to maturity is also assumed to be 4%.

Scenario:

Face Value ( $F$ ): $1,000
Annual Interest Rate ( $r$ ): 4%
Years to Maturity ( $n$ ): 5
Yield to Maturity: 4%

Calculation:

$P = \frac{1,000}{(1 + 0.04)^5}$

Now, let’s perform the calculation using Excel formulas:

Enter the data in cells B1 to B4.
In cell B5, enter the formula:
```
        =B1/(1+B2)^B3
```

Excel Table:


Face Value	$1,000
Annual Interest	4%
Years to Maturity	5
Yield to Maturity	4%
Price	Formula Result (B1/(1+B2)^B3)

Result:

The calculated price of the zero-coupon bond in this scenario is $822.70.

Other Approaches:

Built-in Functions: Excel provides built-in functions for financial calculations. You can use the PRICE function to calculate the price of a zero-coupon bond.
```
        =PRICE(B2,B3,B1,0,B4)
```
Data Table: Excel’s Data Table feature allows you to perform sensitivity analysis by varying one or more input values. This is useful for analyzing how changes in interest rates or years to maturity impact the bond price.