Read Comments (2)

The stock market volatility of the last few years has driven many to seek a safe haven in fixed-income investments in the hopes of lowering the risk of their portfolios. However, historically low interest rates have also accompanied this market volatility. Eventually, rates will begin to rise, which will lead to an erosion of bond portfolio values.

To help evaluate the volatility inherent in fixed-income investments, this installment of Spreadsheet Corner presents a template that allows you to calculate bond values based on knowledge of the coupon rate, the length of time to maturity and the current market interest rate. In addition, you will be able to see how bond prices can change with changes in market interest rates. In particular, you can examine the effects of changing interest rates on the total value of a bond portfolio, such as a bond mutual fund.

The first rule of bond price changes is the inverse relationship between market values and market interest rate changes. If market rates go up, the value of fixed income goes down. Conversely, if interest rates fall, the value of bonds will increase. In other words, the bond market rallies when interest rates fall (and vice versa).

The two primary factors in determining the degree of volatility or price sensitivity of a bond investment are the coupon rate and the time to maturity. The coupon rate is the interest rate on the bond that is established at the time the bond is issued. The higher the coupon rate, the less the value of the bond will change in response to interest rate changes. However, in this low-interest-rate environment, increasing interest rates will have a greater impact on bond values. Ultimately, a higher coupon provides more cash flow and provides it earlier, acting as an anchor by reducing the impact of interest rate changes on price.

Maturity date is the second important factor affecting the degree of price volatility. The longer the time before a bond matures, the more sensitive its value will be to interest rate changes. The relationship is illustrated in Figure 1 for 4% coupon bonds of several different maturities. Since bond values change inversely to the change in interest rates, a long-term, low-coupon-rate bond will suffer a very great decrease in value when interest rates increase; conversely, a short-term, high-coupon-rate bond will change relatively little in value. A longer time to maturity means that it will take longer for investors to recover their initial investment, so the market value of the bond is more sensitive to changes in market interest rates.

Frequently, maturities and coupon rates available in the market have offsetting effects. For example, in the late 1970s and early 1980s, when interest rates were quite high, newly issued bonds carried high coupon rates (which means less volatility) and had longer maturities (which means greater volatility) than bonds issued several years earlier. Consequently, it is important to have a measure of price sensitivity to interest rate changes that takes into account both maturity and coupon effects. Such measure of price volatility is known as duration. Duration is a weighted-average time measure. Using present value weightings, in which later cash flows are less valuable than earlier cash flows, duration indicates a weighted-average maturity. Because of this weighting scheme, we can say that, regardless of the coupon rate and the maturity of different bonds, a bond with longer duration will be more volatile than one with shorter duration. Thus, duration is directly related to price volatility caused by interest rate changes. However, duration is a fairly complicated concept, both in terms of intuition and calculation.

A bond is an interest-bearing security that obligates the issuer to pay the bondholder periodic interest payments and to repay the principal at maturity. Bonds are valued in the same manner as most other securities: The value of a bond is simply the present value of the cash payments through maturity to the bondholder.

For a bond, the cash flows consist of periodic (usually semiannual) interest payments and the return of principal at maturity. The cash flow at maturity, therefore, will consist of both the last interest payment and the principal (often, but not always, $1,000) repayment.

The only other element necessary to complete the valuation is the appropriate market interest rate (also called the required rate of return for the bond). This market rate of interest is the current return being offered on bonds of similar risk and maturity. For this example, we need an estimate of the future level of the rate. This can be an estimate we get from reading an economic forecast in the newspaper or an estimate we make based on our own evaluation of economic and market conditions.

For example’s sake, we will start by assuming that our valuation is done on an interest payment date. In the case of a typical bond, this means we will not receive another payment for six months (since most corporate bonds make semiannual interest payments). If we were calculating the bond value in between interest payments dates, we would have to determine accrued interest.

Using your spreadsheet’s present value function is useful for this valuation. The PV function in Excel computes the present value of a series of equal payments if you specify the amount of the payment, the appropriate interest rate to use and the number of periods over which the payments will be made. Furthermore, the PV function can calculate the present value of the future principal repayment.

Figure 2 contains the data inputs we need. In this example, we are valuing a bond with 15½ years to maturity, entered as 15.5 in cell C5; a 4.0% coupon rate in cell C7 (enter the rate as a decimal and use your spreadsheet’s formatting capabilities to display the results as a percentage); and $1,000 face value in cell C6. Furthermore, the required rate of return (market rate of interest) is 4.0% in cell C10 (also formatted as a percentage) and the bond pays interest semiannually.

In this example, the annual interest payment is $40 (0.04 × 1,000). But the semiannual payment is $20 ($40 ÷ 2). Furthermore, since the interest is paid twice per year, we must also adjust the market rate of interest and number of periods to a semiannual basis. The market interest rate is 4.0% a year, which is 2.0% (4.0% ÷ 2) per six-month period. Since there are 15.5 years to maturity, there are 31 (15.5 × 2) six-month periods to maturity. Cell C9 contains the payment frequency. This will allow us to change the bond to annual, quarterly, or monthly payments, if necessary.

The current value of the bond is found using this formula entered in cell C14:

=PV(C10/C9,C5*C9,-C7*C6/C9,-C6)

Note that we made adjustments for the semiannual nature of the payments—the coupon rate (C7), market interest rate (C10) and payment (C7*C6) were divided by the frequency (C9), and the time to maturity (C5) was multiplied by the frequency (C9). Furthermore, the interest payment amount and face value were made negative so that the resulting bond value will be a positive number.

In this example, the current value of the bond is exactly the same as the principal amount. This occurs whenever the coupon rate and the market rate (required rate of return) are the same. This is a good way of verifying that our formula is correct.

Given the complicated nature of duration, we begin by using a data table to see the effect of changing interest rates and maturities on value. Such a method is particularly useful for dealing with portfolios of bonds, as long as we can determine the average maturity and average coupon of the portfolio. In the First Quarter 2013 issue of Computerized Investing, we will tackle more advanced bond price sensitivity measures.

Setting up the necessary data table is a two-step process. First, we make a table for bond prices (Table 1); then we use that table to make a table of percentage price changes (Table 2). The data in Table 1 was used to create the graph shown in Figure 1.

Table 1 is simply a collection of bond prices calculated in the same way as we did in Figure 2.

We entered this formula in cell B3:

=PV($A3/$B$22,B$2*$B$22,-$B$20*$B$21/$B$22,-$B$21)

Where:

- $A3 is the market interest rate;
- $B$22 is the frequency;
- B$2 is the number of years to maturity;
- $B$20 is the coupon rate; and
- $B$21 is the principal amount.

Again, the interest payment amount and face value are made negative so that the resulting bond value will be positive.

Also, remember that using “$” symbols before the reference cells in a formula “lock” those reference cells when copying to other cells in a worksheet. For example, $A1 tells Excel you always want to refer to column A; B$1 tells Excel you always want to refer to row 1; and $B$1 tells Excel you always want to refer to cell B1.

To simplify the evaluation of the degree of price sensitivity, we used Table 1 to create Table 2, which is a table of percentage price changes across different maturities and market interest rates. Looking at percentage changes allows you to make easier comparisons between bonds or bond portfolios of different coupons and maturities.

Using the data in Table 1 to calculate the percentage price changes ensures that Table 2 is automatically updated any time you change the data table with prices. In this example, we created Table 2 in a separate worksheet. However, you can reference cells from different worksheets in formulas, so in cell B3 of Table 2 we entered this formula:

=(‘Table 1’!B3-‘Table 2’!$B$21)/‘Table 2’!$B$21

Where:

- ‘Table 1’!B3 is cell B3 from worksheet Table 1 (the bond price with one year to maturity and a market interest rate of 0.50%); and
- ‘Table 2’!$B$21 is cell $B$21 from worksheet Table 2 (the principal amount).

This formula determines the price change from your original face value (‘Table 1’!B3-‘Table 2’!$B$21) and divides it by the original value (‘Table 2’!$B$21). To compute the remaining price changes, we want to keep the original bond value (from cell B21 in Table 2) constant, so we make it an absolute address, using the dollar sign in front of the column and row references. We also want to make the new value change, so we use relative addresses for the new value (in cell B3 from Table 1). That value will change when the formula in cell B3 is copied from B3 to J18.

Table 2 shows the impact that different market interest rates and years to maturity have on the price of a bond with a 4% coupon rate. For a bond with 15 years to maturity, an increase in interest rates from 4% to 4.5% would lower the price by 5.41% (from $1,000 to $945.89).

Looking at Table 2, we also see that bond price sensitivity is not symmetrical—a 0.5% increase in rate will always give a smaller percentage decrease in value than the percentage increase in value derived from a 0.5% decrease in rates. Therefore, when interest rates are high, investors have some protection if they guess wrong about the direction of change in market rates.

In the First Quarter 2013 issue, we will further discuss duration as a measure of the interest rate sensitivity of a bond and how we can use it to approximate the change in the price of a bond for a given change in interest rates.

Click here to download spreadsheet.

→ Wayne A. Thorp, CFA is a vice president and senior financial analyst at AAII and editor of *Computerized Investing*. Follow him on Twitter at @AAII_CI.

** Robert Falzalare** from New York posted about 1 year ago:

Can not open downloaded file. error report says file is corrupt. Have tried to build spreadsheet on my own, but formula does not work. Can you help

** Doug Kinsey** from Ohio posted about 1 year ago:

Got the spreadsheet. Thank you. But cannot find the input data sheet?

Sorry, you cannot add comments while on a mobile device or while printing.