How to use ACCRINTM Formula in Excel?

The ACCRINTM Formula in Excel is used to return the accrued interest for a security that pays interest at maturity (one time only).

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

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

What is ACCRINTM Formula in Excel?

The ACCRINTM Formula in Excel was introduced in Excel 2007 and not available in previous versions.

There are two formulas in Microsoft Excel (ACCRINT and ACCRINTM) to calculate the accrued interest for the security. In the ACCRINT Formula the interest is paid periodically, whereas in the ACCRINTM Formula the interest is paid one time at maturity.

What is the Syntax of ACCRINTM Formula in Excel?

The Syntax of ACCRINTM Formula in Excel is:

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

What are the Arguments of ACCRINTM Formula in Excel?

The Arguments of ACCRINTM Formula in Excel are explained below in detail and summary for your easy reference:

Issue (Required Argument)

This is the Security’s issue date.

Settlement (Required Argument)

This is the Security’s maturity 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.

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

Summary of ACCRINTM Arguments:

• Issue (Required) – issue date.
• Settlement (Required) – maturity date.
• Rate (Required) – annual coupon rate.
• Par (Required) – par value.
• Basis (Optional) – day count basis.

How to Calculate the ACCRINTM Formula in Excel?

The ACCRINTM formula in Excel is calculated based on the below:

ACCRINTM = Par x Rate x A/D

A: Number of Accrued Days

D: Annual Year Basis

How to use ACCRINTM Formula in Excel with Examples?

Example 1 (Required Arguments)

In the below example of ACCRINTM Formula in Excel, the accrued interest for the Security is calculated as 3,033.06 by considering issue dat (01-Jan-2020), settlement date (30-April-2050), annual coupon rate (10.00%) and par value (1,000). The Excel ACCRINTM formula in cell D11 is:

=ACCRINTM(D6,D7,D8,D9)

Example 2 (Optional Argument)

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

In the below ACCRINTM formula example, the accrued interest for the Security is calculated as 3,032.65 by considering the basis argument as 1 and keeping all other arguments same as shown in the previous ACCRINTM formula example. The Excel ACCRINTM formula in cell D12 is:

=ACCRINTM(D6,D7,D8,D9,D10)

Example 3 (without Par Value)

Let’s take an example of ACCRINTM Formula in Excel without considering the Par Value and see the result.

In the below ACCRINTM formula example, we have ignored the Par Value but still got the accrued interes as 3,032.65 because the ACCRINTM formula will consider the default Par Value as 1,000.

=ACCRINTM(D6,D7,D8,,D10)

Example 4 (Wrong Accrued Intetest)

There should be additional comma for the Par Value in the Excel ACCRINTM formula to get the correct result as shown above example. If you will not add the extra comma for Par Value in Excel ACCRINTM formula then you will get the wrong accrued interest amount as shown below:

=ACCRINTM(D6,D7,D8,D10)

Example 5 (Dates)

The Date format should be correct in the Excel ACCRINTM formula. The error occurs if the dates are entered in text format. To avoid the error its always advisable to use the DATE formula instead of using the text as date.

The syntax of DATE formula in Excel is =DATE(year,month,day). Refere the below ACCRINTM formula example to use the DATE formula in it:

=ACCRINTM(DATE(2020,1,1),DATE(2050,4,30),D8,D9,D10)

What are the Key Points for ACCRINTM Formula in Excel?

You must remeber below key points while using the ACCRINTM formula in Excel:

• #VALUE Error in the Excel ACCRINTM Formula, if:
  • issue or settlement date is not valid
• #NUM Error in the Excel ACCRINTM Formula, if:
  • Rate <=0
  • Par <=0
  • issue >= settlement
  • basis <0 or >4
• Dates and basis are truncated to integers.