How to use the ACCRINT function
What is the ACCRINT function?
The ACCRINT function calculates the accrued interest for a security that pays periodic interest.
Table of Contents
1. Introduction
What is a security?
A security that pays interest is a bond that investors use to loan money to a company or a government in exchange for interest payments. The interest rate is fixed and does not change over the life of the security.
What is accrued interest?
In finance, accrued interest is the interest on a bond that has accumulated since the last coupon payment.
What is periodic interest?
The interest payments are usually made periodically, such as every three, six months or twelve months, until the security matures and the principal amount is repaid.
2. Syntax
ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])
| issue | Required. The security's issue date. |
| first_interest | Required. The security's first interest date. |
| settlement | Required. The security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. |
| rate | Required. The security's annual coupon rate. |
| par | Required. The security's par value. Default value is $1,000. |
| frequency | Required. The number of coupon payments per year. |
| frequency | payments |
|---|---|
| 1 | Annual |
| 2 | Semiannual |
| 4 | Quarterly |
| [basis] | Optional. The type of day count basis to use. |
| Basis | Day count basis |
|---|---|
| 0 (default value) | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
| [calc_method] | Optional. A logical value that specifies the way to calculate the total accrued interest when the date of settlement is later than the date of first_interest. |
| calc_method | Function returns |
|---|---|
| TRUE | Returns the total accrued interest from issue to settlement. |
| FALSE | Returns the accrued interest from first_interest to settlement. Default value. |
What are Excel dates?
Excel dates are a way of representing dates as serial numbers, where each number corresponds to a unique date. This system allows Excel to perform calculations with dates just like any other number.
- Dates are stored as serial numbers to enable date calculations.
- The Excel date system starts on January 1st, 1900 which is serial number 1.
- January 2nd, 1900 is serial number 2, etc.
How to create Excel dates?
I recommend the DATE function to create Excel dates in the ACCRINT function.
How are the interest dates calculated?
The "First_interest" argument determines the schedule of the security's interest payments. The "first_interest" date, along with the "frequency" argument defines the subsequent interest payment dates.
For example, if the "frequency" is set to 4 (quarterly), interest payments will be scheduled every quarter starting from the "first_interest" date.
The "first_interest" argument is used to align the interest payment schedule with the actual interest payment dates of the security to make sure that the calculation of accrued interest is accurate and reflects the security's terms.
How is the accrued interest calculated?
The DATE function returns a number that acts as a date in the Excel environment.
Function syntax: DATE(year, month, day)
3. Example 1
What is the accrued interest for a bond with a par value of $2,000, an quarterly coupon rate of 5%, an issue date of March 3, first interest date of August 31, and a settlement date of May 1?
The image above shows the following arguments for the bond:
Issue date: March 1, 2025
First interest date: August 31, 2025
Settlement date: May 1, 2025
Coupon rate: 5% (0.05)
Par value: $2,000
Frequency: 4 (quarterly payments)
Basis: 0 (US (NASD) 30/360)
Formula in cell C10:
The ACCRINT function requires Excel dates to work properly, the image above shows Excel dates in cells C2, C3, and C4. The result is 16.9444444444444.
4. Example 2
If a company issued a 5-year bond with a face value of $25,000 on July 1, 2020, with a coupon rate of 4.5% paid semi-annually, what is the accrued interest as of December 31, 2022?
The arguments are:
| Issue date | 7/1/2020 |
| First interest date | 8/31/2020 |
| Settlement date | 12/31/2022 |
| Coupon rate | 0.045 |
| Par value | $25,000 |
| Frequency | 2 |
| Basis | 0 |
Formula in cell C10:
The ACCRINT function requires Excel dates to work properly, the image above shows Excel dates in cells C2, C3, and C4. The result is $2,812.5
5. Example 3
An investor purchased a 10-year Treasury note with a face value of $50,000 on February 1, 2021, with a coupon rate of 3.2% paid semi-annually. What is the accrued interest on this note as of September 30, 2023?
The arguments are:
| Issue date | 7/1/2020 |
| First interest date | 8/31/2020 |
| Settlement date | 12/31/2022 |
| Coupon rate | 0.045 |
| Par value | $25,000 |
| Frequency | 2 |
| Basis | 0 |
Formula in cell C10:
The ACCRINT function requires Excel dates to work properly, the image above shows Excel dates in cells C2, C3, and C4. The result is $4,253
6. How is the ACCRINT function calculated?
The ACCRINT function uses the `First_interest` date and frequency to calculate the accrued interest between the "issue" date to the "settlement" date.
ACCRINT is calculated using the following formula:
where:
- Ai : number of accrued days for the i-th quasi-coupon period within odd period.
- NC : number of quasi-coupon periods that fit in odd period. If this number contains a fraction, raise it to the next whole number.
- NLi : normal length in days of the quasi-coupon period within odd period
7. ACCRINT function #VALUE! error
The ACCRINT function may return a #VALUE! error if dates are entered as text values, the image above shows the #VALUE! error in cell C2.
Use the DATE function to enter dates (hard coded dates) in the ACCRINT function . For example, DATE(2018,3,1). The DATE function converts the specified year, month, and day to an Excel date (number).
The following formula shows how to use hard coded values in the ACCRINT function:
The ACCRINT function returns the #NUM! error value:
- if the rate is less than 0 (zero).
- if par value is less than 0 (zero).
- if the frequency is not 1,2, or 4.
- basis is less than 0 (zero) or larger than 4.
- issue date is equal or larger than settlement date.
What is hard coded values in a formula?
Hard coded values in Excel are values that are entered directly into a formula instead of using cell references.
8. Get Excel file
Useful resources
Functions in 'Financial' category
The ACCRINT function function is one of 27 functions in the 'Financial' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form