ACCRINT function

The ACCRINT function in Excel calculates the accrued interest on a security (such as a bond) that pays periodic interest.

Syntax

ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])

Parameters

• issue: The date the security is issued. This is the date the bond or security was originally issued.
• first_interest: The date of the first interest payment after the issue date.
• settlement: The settlement date of the security, which is the date after the issue date when the security is traded to the buyer.
• rate: The annual coupon interest rate of the security. This is the percentage of the face value that is paid as interest each period.
• par: The face value or principal amount of the security, typically 100 or 1000, depending on the security.
• frequency: The number of interest payments per year. It can be one of the following values:
  • 1 = Annual payments
  • 2 = Semiannual payments
  • 4 = Quarterly payments
• [basis] (optional): The day count basis to use for the calculation. This can be one of the following:
  • 0 or omitted = US (NASD) 30/360
  • 1 = Actual/Actual
  • 2 = Actual/360
  • 3 = Actual/365
  • 4 = European 30/360
• [calc_method] (optional): Whether to use simple or compound interest for accruals. This is a logical value where:
  • TRUE = Compound interest (default)
  • FALSE = Simple interest

How It Works

The ACCRINT function calculates the interest that has accrued on a bond or other fixed-income security between the settlement date and the next interest payment date. This function is typically used by investors who have bought or sold a security between interest payment dates, to calculate the interest that is due at the time of the transaction.

The accrued interest is calculated based on the frequency of the payments, the rate of interest, and the day count basis.

Examples

1. Basic Example: Suppose you have a bond with the following details:
   • Issue date: January 1, 2023
   • First interest date: July 1, 2023
   • Settlement date: April 1, 2023
   • Interest rate: 6% (annual)
   • Face value: 1000
   • Frequency: Semiannual (2 payments per year)

   The formula to calculate the accrued interest would be:

   =ACCRINT("1/1/2023", "7/1/2023", "4/1/2023", 0.06, 1000, 2)
   

   This formula calculates the interest accrued between January 1, 2023, and April 1, 2023, on a semiannual bond paying 6% annual interest.

2. Including Day Count Basis: If you need to apply a specific day count basis (e.g., Actual/Actual), you would specify the basis argument. For example:

   =ACCRINT("1/1/2023", "7/1/2023", "4/1/2023", 0.06, 1000, 2, 1)
   

   In this case, the basis argument is set to 1 (Actual/Actual).

3. Including Compound Interest: If you want to calculate the accrued interest using compound interest (the default method), you would use the calc_method argument:

   =ACCRINT("1/1/2023", "7/1/2023", "4/1/2023", 0.06, 1000, 2, 1, TRUE)
   

Important Notes

• The ACCRINT function assumes that the interest payments are made at regular intervals, so the settlement date and the first interest date must be aligned to the payment schedule.
• If the first_interest date is later than the settlement date, Excel will return a #NUM! error.
• The calc_method argument is optional. By default, Excel uses compound interest for the calculation.

Summary

The ACCRINT function is used to calculate the accrued interest on a bond or other fixed-income security. It takes into account the issue date, the first interest date, the settlement date, the interest rate, and other relevant details to determine how much interest has accrued on the bond at the time of the settlement. This is useful for investors trading bonds between interest payment dates.