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.
- 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) .
Result:
- 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) .
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:
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.
Result:
Good luck!
You should read it
- How to use SUMPRODUCT function in Excel
- EFFECT function - The function of calculating the annual real interest rate in Excel
- NPER function - The function calculates the period number of an investment in Excel
- How to add computers to Excel
- How to use the SUM function to calculate totals in Excel
- SUBTOTAL function - The calculation function for a group in a list in Excel
- How to use subtraction in Excel
- DAYS function, how to use the function to calculate the number of days between two time points in Excel
May be interested
- Format dates in Excelthe following article details how to format dates in excel. step 1: click the cell you want to format date - right-click and select format cells.
- Coupdays function - Calculates the number of days in a coupon period and contains the settlement date in Excelthe following article details how to use the coupdays function in excel. function meaning: the coupdays function calculates the number of days in the coupon period and contains the settlement date. syntax: coupdays (settlement, maturity, frequency, [basis]).
- Instructions to copy data from Excel to Word retain the formatsometimes at work you want to convert table data from excel to word, but when the copy to word is formatted or exceeds the frame in the word. the following article details how to copy data from excel to word to keep the format.
- Instructions for using the Networksday function in excelthe following article details the calculation of workdays using networksday.
- Normalize string when pressing Enter key in Excelthe following article will guide in detail how to use functions to normalize strings when pressing the enter key in excel. this function will remove spaces at the beginning and end of the line, delete two consecutive white space characters between words.
- Instructions to merge multiple Excel files into 1 Filedetailed instructions on how to combine multiple excel files into a single file with macro.