Understanding and Calculating Returns on Index-Linked Bonds using Excel

Index-linked bonds are a type of bond that pays interest and principal based on an inflation index, such as the Consumer Price Index (CPI). This means that the amount of money you receive from the bond changes according to the changes in the price level of goods and services. The purpose of index-linked bonds is to protect investors from the effects of inflation, which reduces the real value of money over time.

For example, suppose you buy an index-linked bond for $100 with a 2% coupon rate and a one-year maturity. The CPI at the time of purchase is 100. After six months, the CPI increases to 105, which means that the prices of goods and services have increased by 5%. The bond will adjust its principal and interest payments to reflect this change. The new principal value will be $105 ($100 x 1.05) and the new interest payment will be $2.10 ($105 x 0.02). At the end of the year, the CPI increases to 110, which means that the prices have increased by 10% since the bond was issued. The bond will again adjust its payments accordingly. The final principal value will be $110 ($100 x 1.1) and the final interest payment will be $2.20 ($110 x 0.02). Therefore, the total amount you receive from the bond will be $112.20 ($110 + $2.10 + $2.20), which is equivalent to a 12.2% return on your initial investment of $100.

Index-linked bonds are different from regular bonds, which pay a fixed amount of interest and principal regardless of inflation. Regular bonds are exposed to inflation risk, which means that the real value of the bond payments may decrease if inflation is higher than expected. For example, suppose you buy a regular bond for $100 with a 4% coupon rate and a one-year maturity. The bond will pay you $4 of interest every six months and $100 of principal at the end of the year. The total amount you receive from the bond will be $108 ($100 + $4 + $4), which is equivalent to an 8% return on your initial investment of $100. However, if the CPI increases by 10% during the year, the real value of the bond payments will be lower than what you expected. The $108 you receive will only buy you as much goods and services as $98.18 ($108 / 1.1) did when you bought the bond. Therefore, your real return on the bond will be -1.82% (-$1.82 / $100).

Basic Theory

The basic theory behind index-linked bonds involves two key components: the bond’s nominal value and the performance of the chosen index. The nominal value is the face value of the bond, typically repaid at maturity, while the index performance determines the periodic interest payments. The formula to calculate the interest payment is as follows:

Interest Payment:

This formula ensures that the interest payment is proportional to the performance of the index, with adjustments made to the nominal value.

Procedures in Excel

1. Set Up Your Excel Sheet:
   • Column A: Periods (e.g., months or years)
   • Column B: Nominal Value of the Bond
   • Column C: Index Performance
2. Enter Initial Values:
   • Input the initial nominal value of the bond in cell B2.
   • Enter the index performance for each period in column C.
3. Formulate Interest Payment:
   • In cell D2, enter the formula: =B2*(C2-1).
   • Drag this formula down for subsequent periods.
4. Calculate Total Return:
   • In cell E2, enter the formula: =SUM(D2:Dn) (replace ‘n’ with the last row number).

Explanation

Let’s consider a scenario with the following details:

• Nominal Value: $10,000
• Index Performance (Year 1): 5%
• Index Performance (Year 2): 3%
• Index Performance (Year 3): -2%

Now, let’s input these values into our Excel sheet.

The total return after three years in this scenario is $600.

Other Approaches

Using Excel Functions:

Excel provides functions like IRR (Internal Rate of Return) and XIRR (Extended Internal Rate of Return) to calculate returns based on cash flows, which can be adapted for index-linked bonds.

Sensitivity Analysis:

You can conduct sensitivity analysis by varying the index performance to see how it impacts returns. Excel’s Data Table feature can assist in automating this analysis.