ACCRINT Formula

The article in this webpage describes how to use ACCRINT formula in Excel with meaning, syntax, detailed explanation and examples.

What is ACCRINT formula?

The ACCRINT Formula in Excel is used to calculate the accrued interest of a security that pays interest periodically. The ACCRINT formula in Excel is a part of financial formulas.

The ACCRINT formula in Excel is used when dealing with bonds or other fixed-income securities that accrue interest over time. This function takes into account the annual interest rate, the number of periods, the face value of the security, and other arguments to calculate the accrued interest. The ACCRINT formula in Excel returns interest on a periodic basis, such as bonds that pay interest annually, semi-annually, or quarterly.

The ACCRINT, short for “accrued interest,” is a powerful tool that assists users in computing the accrued interest for a financial instrument over a given period. This function is particularly useful in scenarios involving bonds and other interest-bearing securities.

How to use ACCRINT formula in Excel with Examples?

The ACCRINT formula in Excel is commonly used in financial modeling, accounting, and investment analysis to determine the amount of interest earned on a security between two specified dates.

In the dynamic world of finance and accounting, Microsoft Excel plays a pivotal role in simplifying the complex calculations and aiding professionals in making informed decisions. One such function that holds significance in interest calculations is the ACCRINT formula.

ACCRINT Syntax

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

The arguments of ACCRINT formula in Excel are:

• issue (required argument) – This is the security’s issue date.
• first_interest (required argument) – This is the security’s first interest data.
• settlement (required argument) – This is the security’s settlement date.
• rate (required argument) – This is the security’s annual coupon rate.
• par (required argument) – This is the security’s par value. If you will omit the par value, then it will use $1,000 by default.
• frequency (required argument) – This is the number of coupon payments per year (1 for annual, 2 for semiannual and 4 for quarterly).
• [basis] (optional argument) – This is the type of day count basis. If you will omit the basis, then it will use zero (0) by default. The day count basis are mentioned below:
  • • 0 – US (NASD) 30/360
    • 1 – Actual/Actual
    • 2 – Actual/360
    • 3 – Actual/365
    • 4 – European 30/360
• [calc method] (optional argument) – This is the logical value to calculate the total accrued interest when settlement date is after the first interest date.
  • • TRUE (1) – Calculates the total accrued interest from issue to settlement date.
    • FALSE (0) – Calculates the total accrued interest from first interest to settlement date. If you omit or do not enter then by default considered as TRUE (1).

Important Notes

• The ACCRINT formula in Excel will return #VALUE Error if issue, first interest or settlement date is not valid.
• The ACCRINT formula in Excel will return #NUM Error if any of the below condition is met:
  • rate <=0
  • par <=0
  • issue >= settlement
  • frequency is other than 1, 2 or 4
  • basis <0 or >4
• The dates and basis are truncated to integers in ACCRINT formula.

ACCRINT Examples

Example 1 (required arguments)

Let’s understand the ACCRINT formula in Excel with example.

Suppose we have the following details:

• issue date: January 1, 2020
• first interest date: Jun 30, 2020
• settlement date: Jun 15, 2020
• annual coupon rate: 10%
• par value: $1,000
• frequency: 2 (semi-annual)

The formula to calculate ACCRINT is:

=ACCRINT(D6,D7,D8,D9,D10,D11)

The result will be 45.56, which represents the accrued interest for the bond.

Example 2 [basis]

The [basis] argument is optional in ACCRINT formula and if you ignore it will consider as zero (0) by default and will use US (NASD) 30/360 for the same.

In the below ACCRINT formula example, the accrued interest for the security is calculated by adding the [basis] argument and keeping all other arguments same as shown in the previous ACCRINT formula example.

Suppose we have the following details:

• day count basis: 3 (actual/365)

The formula to calculate ACCRINT is:

=ACCRINT(D6,D7,D8,D9,D10,D11,D12)

Result: The ACCRINT formula will return 45.48 as accrued interest.

Example 3 [calc method]

The [calc_method] argument is also optional in ACCRINT formula and if you will ignore then it will consider as TRUE (1) by default. In the below ACCRINT formula example, the accrued interest for the security is calculated by adding the [basis] and [calc_method] arguments.

• day count basis: 4 (European 30/360)
• calc method: 1 (TRUE)

The formula to calculate ACCRINT is:

=ACCRINT(D6,D7,D8,D9,D10,D11,D12,D13)

Result: The ACCRINT formula will return 45.56 as accrued interest.

FAQs

ACCRINT vs ACCRINTM

There are two financial formulas in Microsoft Excel (ACCRINT and ACCRINTM) to calculate the accrued interest for the security. The main difference between ACCRINT and ACCRINTM formula is: in ACCRINT formula the interest is paid periodically, whereas in ACCRINTM formula the interest is paid one time at the maturity.

Conclusion

The Excel ACCRINT function proves to be a valuable tool for calculating accrued interest on financial instruments. Whether you’re dealing with bonds or other interest-bearing securities, the ACCRINT formula simplifies the process by providing an accurate and efficient way to determine the interest that has accumulated over a specific period.

By utilizing the ARRCINT function, financial professionals and enthusiasts alike can easily manage and analyze the interest accruals, contributing to a clearer understanding of investment performance. With its user-friendly interface and reliable calculations, ACCRINT function stands as a helpful feature for anyone navigating the complex world of finance within the Microsoft Excel.

Subscribe Rath Point's official YouTube channel to know the latest updates, tips, and tricks.

« Finacial Formulas | Next Formula »