The ACCRINT function in Excel calculates the accrued interest for a security that pays periodic interest. This can be useful for financial analysts or investors tracking the interest that has accumulated on a bond between payment periods.
Here is the syntax for the ACCRINT function:
ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis])
Here’s an explanation of each argument:
- issue: The date when the security was issued. This is a required argument and should be input as a date using the `DATE` function or as cell reference to a date.
- first_interest: The first date interest will be paid on the security after the issue date. This is also a required argument.
- settlement: The settlement date of the security. This represents the date when the security is purchased or transferred to the buyer. This date must be after the issue date.
- rate: The annual coupon rate of the security. This refers to the interest rate that the issuer of the bond pays to the bondholders.
- par: The par value of the security. This is the face value of the bond. In many cases, this is set to $1,000, but you can adjust it based on the specific bond being analyzed.
- frequency: The number of interest payments per year. It can be 1 (annual), 2 (semiannual), or 4 (quarterly).
- basis: (Optional) The day count basis to be used. This is an optional argument that determines how days are counted in a year. It can be:
- 0 or omitted for the US 30/360
- 1 for Actual/Actual
- 2 for Actual/360
- 3 for Actual/365
- 4 for European 30/360
Example:
Assume you have a bond with the following details:
- Issue Date: January 1, 2020
- First Interest Date: July 1, 2020
- Settlement Date: March 1, 2020
- Annual Interest Rate: 5%
- Par Value: $1,000
- Semiannual Payment (frequency of 2)
- US 30/360 basis (the default)
In your Excel sheet, you would input:
=ACCRINT(DATE(2020,1,1), DATE(2020,7,1), DATE(2020,3,1), 0.05, 1000, 2, 0)
This formula will return the accrued interest for the given security from the issue date to the settlement date.
Remember to format your date inputs correctly and adjust the arguments based on your specific bond details.
Unlock Your Potential
Excel
Access
Power BI
