Use the cumulative interest calculation function in Excel

The following article details how to calculate the accrued interest in securities.

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.

Inside:

- 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.

Use the cumulative interest calculation function in Excel Picture 1

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

Use the cumulative interest calculation function in Excel Picture 2

Result:

Use the cumulative interest calculation function in Excel Picture 3

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

Use the cumulative interest calculation function in Excel Picture 4

2. Calculate accrued interest for maturity-paying securities using the ACCRINTM function

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

Inside:

- 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:

Use the cumulative interest calculation function in Excel Picture 5

In the cell to calculate 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.

Use the cumulative interest calculation function in Excel Picture 6

Result:

Use the cumulative interest calculation function in Excel Picture 7

Good luck!