How to use ACCRINTM formula

 

 ACCRINTM Function in Excel

The ACCRINTM function in Excel is used to calculate the accrued interest for a security that pays interest at maturity. This is useful for bond investors who need to know how much interest has been earned but not yet paid on a bond or other debt security.

Syntax of ACCRINTM Function:

=ACCRINTM(issue, settlement, rate, par, basis)

Parameters:

1. issue: (Required) The issue date of the security.
2. settlement: (Required) The settlement date of the security (the date after which the security is traded to the buyer).
3. rate: (Required) The annual coupon rate of the security.
4. par: (Optional) The par value (or face value) of the security. Default is 1,000 if omitted.
5. basis: (Optional) The day count basis to use. Excel uses the following values for this argument:
   • 0 = US (NASD) 30/360
   • 1 = Actual/Actual
   • 2 = Actual/360
   • 3 = Actual/365
   • 4 = European 30/360

Explanation of Parameters:

• Issue Date: The date on which the bond or security was issued.
• Settlement Date: The date when the bond is purchased.
• Rate: The annual interest rate of the security.
• Par Value: The face value of the bond (optional, default is 1,000).
• Basis: The day count convention, which determines how interest is calculated.

Example Template:

Let’s say you have the following information about a bond:

• Issue Date: January 1, 2023
• Settlement Date: July 1, 2023
• Annual Interest Rate: 5%
• Par Value: 1,000 (default)
• Day Count Basis: Actual/Actual (1)

To calculate the accrued interest using the ACCRINTM function, you would enter the following formula in Excel:

=ACCRINTM(DATE(2023, 1, 1), DATE(2023, 7, 1), 0.05, 1000, 1)

This formula will calculate the interest accrued on the bond from the issue date (January 1, 2023) to the settlement date (July 1, 2023).

Example Calculation:

Using the above example:

• Annual Interest Rate: 5% of 1,000 = 50 per year.
• Since the bond has been held for half a year (from January 1 to July 1), the accrued interest would be half of 50, which is 25.

Therefore, the result of the ACCRINTM function will be 25.

Tips:

• Ensure the date formats (issue and settlement) are valid Excel date values.
• If you're unsure about the day count basis, you can leave it as 0 for the default NASD 30/360 convention.

This function is very handy for investors or financial analysts who deal with bond markets and need to track accrued interest for securities that don’t pay interest periodically but only at maturity.

Summary Table Example:

This simple example helps in understanding how to apply the ACCRINTM function for calculating interest accrued at the maturity of a security.