Calculating Accrued Interest on Bonds using Excel

Bond accrued interest is the amount of interest that a bond earns, but has not been paid yet. A bond is a type of loan that pays interest to the lender (or bondholder) at regular intervals, usually every six months or one year. The interest rate and the payment dates are fixed when the bond is issued.

However, sometimes a bondholder may want to sell the bond before the next payment date. In that case, the buyer of the bond will receive the full interest payment on the next payment date, even though they did not own the bond for the whole period. To be fair, the buyer must pay the seller the interest that the seller earned before selling the bond. This is called the accrued interest.

For example, suppose a bond pays 10% interest per year, and the payment dates are June 1 and December 1. If a bondholder sells the bond on September 1, the buyer will receive the full interest payment of 5% on December 1. However, the seller earned 2.5% interest from June 1 to September 1, so the buyer must pay the seller this amount as the accrued interest. The accrued interest is calculated by multiplying the interest rate, the face value of the bond, and the fraction of the period that has passed since the last payment date.

Accrued interest is important for bond trading, because it affects the price that the buyer and seller agree on. The buyer pays the seller the market price of the bond plus the accrued interest. The market price of the bond may change depending on the supply and demand of the bond, the credit quality of the issuer, and the interest rate environment. The accrued interest, however, is determined by the terms of the bond and the date of the transaction.

Basic Theory

Bonds pay periodic interest to bondholders. The interest is typically paid semiannually, annually, or at other specified intervals. When a bond is bought or sold between interest payment dates, the buyer needs to compensate the seller for the interest that has accrued since the last payment date.

The formula for calculating accrued interest is:

Procedures

1. Determine the Bond’s Terms:
   • Identify the annual interest rate and the number of interest periods in a year.
   • Note the face value of the bond.
2. Calculate the Accrual Period:
   • Determine the number of days that have passed since the last interest payment.
3. Apply the Formula:
   • Use the formula mentioned above to calculate the accrued interest.
4. Verify Dates:
   • Ensure that the interest payment dates and the purchase/sale date align with your calculations.

Excel Implementation

Let’s consider a scenario where a bond with a face value of $1,000, an annual interest rate of 6%, and semiannual payments is bought on September 1st, 2023. The last interest payment date was March 1st, 2023. We want to calculate the accrued interest as of the purchase date.

Excel Table:

Excel Formulas:

1. Calculate Accrual Period:

   =DATEDIF("3/1/2023", "9/1/2023", "d")

2. Apply Accrued Interest Formula:

   =($B$2/$B$3)*(B6/$B$3)*$B$4

   Where:

   • B2 is the annual interest rate
   • B3 is the number of interest periods per year
   • B6 is the calculated accrual period
   • B4 is the face value of the bond

Scenario Calculation:

1. Accrual Period Calculation:

   Accrual Period = DATEDIF(“3/1/2023”, “9/1/2023”, “d”) = 184 days

2. Accrued Interest Calculation:

   Accrued Interest =

Result

For the given scenario, the accrued interest as of September 1, 2023, is approximately $30.67.

Alternative Approaches

1. Excel’s ACCRINT Function:

   Excel provides the ACCRINT function, specifically designed for calculating accrued interest on bonds. Example: =ACCRINT(settlement, maturity, rate, par, frequency, [basis]).

2. Useful Excel Functions:

   Utilize functions like TODAY() to automatically fetch the current date and simplify date calculations.