Use the Cumulative Interest Calculation Function in Excel

This updated guide explains how to Use the Cumulative Interest Calculation Function in Excel through clear steps, practical examples, and important checks that help prevent common errors.

In stock, there are 2 methods to pay interest at maturity or to pay interest periodically.

So there are 2 methods of calculating accrued interest for securities paying interest at maturity and calculating accrued interest for securities that pay interest periodically.

ACCRINT function: Use in case of periodic interest payment.

ACCRINTM function: Use in case of paying interest at maturity.

1. Calculate Accrued Interest for Periodic Interest-paying Securities Using the ACCRINT Function

Syntax

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

Meaning: Calculating accrued interest for securities paying interest periodically.

Arguments

- issue: Date of issuing securities (Required parameters).

- first_interest: First interest date of the security (required).

- settlement: Stock maturity date (determined to be the date after the issue date of securities to be sold to buyers).

- rate: The interest rate of the security in the year.

- par: Par value of securities (if you omit this value the ACCRINT function will use the value $ 1000).

- frequency: Frequency of interest payment by year, with annual payment, the frequency = 1, semi-annual payment frequency = 2, quarterly payment of frequency equal to 4).

- basis: Determine how to count dates, with values:

+ basis = 0: The default value is calculated every 30 days / year with 360 days (according to North American standards).

+ basis = 1: The number of days is calculated by the actual number of days of the month / the number of actual days of the year.

+ basis = 2: The actual number of days in the month / 360 days a year.

+ basis = 3: The actual number of days in the month / Year is 365 days.

+ basis = 4: A month has 30 days / A year has 360 days (according to European standards).

- calc_method: A logical value showing the calculation of cumulative interest, with the following values:

+ calc_method = True: Calculates accrued interest from issue date to payment date.

+ calc_method = False: Calculates accrued interest from the first interest calculation date to the payment date.

Calculation of the ACCRINT function = par x (rate / frequency) x (sum of (number of cumulative days for the ith term divided by the normal length in days of the term).

Note: with i running from 1 to the number of terms. Where the term number is the decimal plus 1 and get the integer value.

Example

Calculating the total accrued interest for a securities with interest paid periodically with the following figures in the two cases of calculating accrued interest from the issue date and calculating accrued interest from the first interest calculation date.

- Calculate accrued interest from issue date to payment date. in the target cell enter the following formula: ACCRINT (B3, C3, D3, E3, F3, G3, H3, TRUE) .

Result:

- Calculate accrued interest from the first interest calculation date to the payment date. in the target cell enter the following formula: ACCRINT (B3, C3, D3, E3, F3, G3, H3, FALSE) .

2. Calculate Accrued Interest for Maturity-paying Securities Using the ACCRINTM Function

Syntax

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

Arguments

- issue: Date of issuing securities.

- settlement: Due date of the security.

- rate: The annual interest rate.

- par: Stock value, if omitted the default value is $ 1000.

- basis: Determine how to count dates, with values:

+ basis = 0: The default value is calculated every 30 days / year with 360 days (according to North American standards).

+ basis = 1: The number of days is calculated by the actual number of days of the month / the number of actual days of the year.

+ basis = 2: The actual number of days in the month / 360 days a year.

+ basis = 3: The actual number of days in the month / Year is 365 days.

+ basis = 4: A month has 30 days / A year has 360 days (according to European standards).

Meaning: Calculating accrued interest for securities paying interest at maturity.

Calculation of the ACCRINTM function: ACCRINTM = (face value) x (interest) x (number of cumulative days) / (Year basis).

Example

Calculating accrued interest at maturity with the following figures:

in the target cell interest enter the following formula: = ACCRINTM (B3, C3, D3, E3, F3) . Here the basis for the basis not entered is set to 0 by default.

Result:

FAQ

What is the easiest way to Use the Cumulative Interest Calculation Function in Excel?

Start with the requirements in this guide, then follow each step in order. Check the result after every major change so problems are easier to identify.

Can beginners follow these instructions?

Yes. The process is organized into practical steps, and you can repeat any step before moving forward.

What should I check if the method does not work?

Confirm your software or device version, permissions, settings, file format, and internet connection when the task depends on online services.