Understanding and Calculating Perpetual Bonds in Microsoft Excel

A perpetual bond is a type of bond that has no maturity date and pays a fixed amount of interest forever. Unlike regular bonds, perpetual bonds do not have to be repaid by the issuer. However, the issuer may have the option to redeem the bond after a certain period of time.

Perpetual bonds are rare and usually issued by governments or very stable companies that can afford to pay interest indefinitely. Some examples of perpetual bonds are the British consols, which were issued in the 18th and 19th centuries, and the Dutch water board bonds, which date back to the 17th century.

Perpetual bonds are considered a form of equity rather than debt, because they represent a perpetual obligation for the issuer. The value of a perpetual bond depends on the interest rate and the credit quality of the issuer. The higher the interest rate, the lower the value of the bond, and vice versa. The lower the credit quality of the issuer, the higher the risk of default, and therefore the lower the value of the bond.

Perpetual bonds are attractive to investors who seek a steady and predictable income stream. However, they also expose the investors to the risk of inflation, which erodes the purchasing power of the fixed interest payments over time. Moreover, perpetual bonds are subject to the interest rate risk, which means that the value of the bond may fluctuate significantly depending on the changes in the market interest rates.

Basic Theory

The price of a perpetual bond is determined by the present value of its future cash flows, which consist of periodic interest payments. The formula for calculating the price of a perpetual bond is:

Perpetual Bond Price = Annual Interest Payment / Discount Rate

Where:

• Annual Interest Payment: The fixed interest paid by the issuer each year.
• Discount Rate: The rate of return required by investors.

Procedures for Excel Calculation

1. Enter Data:
   • Open a new Excel spreadsheet.
   • Label column A as “Year” and column B as “Cash Flow.”
2. Enter Variables:
   • Input the annual interest payment in cell B2.
   • Input the discount rate in cell B3.
3. Calculate Present Value:
   • In cell A4, enter “Present Value.”
   • In cell B4, input the formula: =B2/B3
4. Create Cash Flow Table:
   • In column A, list the years (e.g., 1, 2, 3, …).
   • In column B, input the formula for each year’s cash flow: =$B$2.
5. Calculate Present Value of Cash Flows:
   • In cell C2, label it “PV of Cash Flows.”
   • In cell C3, input the formula: =B3/(1+B$3)^A3
6. Sum Present Values:
   • In cell C4, input the formula: =SUM(C3:C100)

Let’s consider a scenario:

• Annual Interest Payment (B2): $50
• Discount Rate (B3): 5%

1. Enter Data:
   • B2: $50
   • B3: 0.05 (5%)
2. Calculate Present Value:
   • B4: =B2/B3
3. Create Cash Flow Table:
   • In column A (Years): 1 to 100
   • In column B (Cash Flow): =$B$2
4. Calculate Present Value of Cash Flows:
   • In C3: =B3/(1+$B$3)^A3
   • Copy this formula down to C100.
5. Sum Present Values:
   • C4: =SUM(C3:C100)

Results

After entering the scenario values and executing the calculations, you will find that the perpetual bond price is $1,000, indicating that investors would be willing to pay $1,000 for a perpetual bond with a $50 annual interest payment and a 5% discount rate.

Other Approaches

1. Excel NPV Function:
   • Use Excel’s NPV function to calculate the present value of cash flows.
2. Data Table for Sensitivity Analysis:
   • Implement a data table in Excel to analyze how changes in the discount rate affect the perpetual bond price.
3. Graphical Representation:
   • Create a line chart to visualize the relationship between the discount rate and the perpetual bond price.