ACCRINT Function - Calculate Accrued Interest for Periodic Payment Securities
PRO
BASIC
The ACCRINT
function in Jspreadsheet Formulas Pro is a comprehensive financial calculation tool designed for securities with periodic interest payments. This advanced function serves multiple professional applications:
Investment Banking:
- New bond issue pricing
- Secondary market valuations
- Structured product analysis
- Deal pricing and syndication
- Fixed-income origination
Portfolio Management:
- Interest income forecasting
- Portfolio yield analysis
- Investment performance tracking
- Cash flow projections
- Risk assessment calculations
Trading Operations:
- Clean and dirty price calculations
- Trading position valuations
- Arbitrage opportunity analysis
- Market-making operations
- Dealer inventory management
Risk Management:
- Interest rate exposure monitoring
- Duration calculations
- Portfolio stress testing
- Value-at-risk analysis
- Hedging strategy development
Treasury Operations:
- Corporate debt management
- Cash position monitoring
- Liability planning
- Interest expense forecasting
- Debt restructuring analysis
The function supports various fixed-income instruments:
Corporate Securities:
- Investment-grade bonds
- High-yield bonds
- Medium-term notes
- Commercial paper
- Convertible bonds
Government and Municipal:
- Treasury bonds and notes
- Municipal securities
- Agency bonds
- Sovereign debt
- State and local issues
Structured Products:
- Asset-backed securities
- Mortgage-backed bonds
- Collateralized debt obligations
- Credit-linked notes
- Interest rate products
This professional-grade tool incorporates industry-standard calculation methods, supporting multiple day-count conventions and payment frequencies, making it essential for financial institutions, investment firms, and corporate treasury departments.
Documentation
Returns the accrued interest for a security that pays periodic interest.
Category
Financial
Syntax
ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])
Parameter | Description |
---|---|
issue |
The security's issue date (when the security was first issued). |
first_interest |
The security's first interest payment date. |
settlement |
The security's settlement date (the date through which interest is calculated). |
rate |
The security's annual coupon rate (as a decimal, e.g., 0.05 for 5%). |
par |
The security's par value (face value), typically 1000 for bonds. |
frequency |
The number of coupon payments per year: 1 (annual), 2 (semi-annual), or 4 (quarterly). |
[basis] |
Optional. Day count basis: 0 (30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), 4 (30/360 European). Defaults to 0. |
[calc_method] |
Optional. Calculation method: TRUE (US method) or FALSE (European method). Defaults to FALSE. |
Behavior
The ACCRINT
function calculates accrued interest using the following logic:
- Date Validation: Ensures issue date < settlement date and validates all date inputs
- Interest Calculation: Computes interest based on the day count basis and payment frequency
- Method Application: Applies either US or European calculation method
Input Handling:
- Empty Cells: Returns #VALUE! error if required arguments are missing
- Invalid Dates: Returns #VALUE! error for non-date values in date parameters
- Non-Numeric Values: Returns #VALUE! error for text in numeric parameters
- Boolean Values: Returns #VALUE! error; does not accept TRUE/FALSE except for calc_method
- Error Propagation: Returns error if any input argument contains an error
Common Errors
Error | Description |
---|---|
#NUM! | Issue date ≥ settlement date, basis not in range 0-4, frequency not 1/2/4, or invalid rate/par values. |
#VALUE! | Invalid date formats, non-numeric values in numeric parameters, or missing required arguments. |
#DIV/0! | Par value is zero, which would cause division by zero in interest calculation. |
Best Practices
- Date Validation: Ensure issue date < first interest date < settlement date for logical consistency
- Rate Format: Express rates as decimals (0.05 for 5%) rather than percentages (5%)
- Basis Selection: Choose appropriate day count basis for your market (0 for US corporate bonds, 1 for government bonds)
- Par Value: Use standard denominations (1000 for bonds, 100 for some securities) and never zero
- Frequency Matching: Ensure frequency matches the actual payment schedule of the security
- Date Formats: Use consistent date formats recognized by your system (YYYY-MM-DD recommended)
Usage Examples
Here are comprehensive examples demonstrating the ACCRINT function in various financial scenarios:
1. Corporate Bond Analysis:
// Investment Grade Corporate Bond
=ACCRINT("2024-01-15", "2024-07-15", "2024-04-15", 0.065, 1000, 2, 0)
// Semi-annual payments, 30/360 basis
// $1,000 par value, 6.5% coupon
// High-Yield Corporate Bond
=ACCRINT("2024-01-01", "2024-04-01", "2024-02-15", 0.085, 1000, 4, 0)
// Quarterly payments, 30/360 basis
// $1,000 par value, 8.5% coupon
// Medium-Term Note
=ACCRINT("2024-02-01", "2024-08-01", "2024-05-15", 0.055, 5000, 2, 0)
// Semi-annual payments, 30/360 basis
// $5,000 par value, 5.5% coupon
2. Government Securities:
// Treasury Bond
=ACCRINT("2024-01-01", "2024-07-01", "2024-03-15", 0.045, 10000, 2, 1)
// Semi-annual payments, Actual/Actual basis
// $10,000 par value, 4.5% coupon
// Agency Bond
=ACCRINT("2024-02-15", "2024-08-15", "2024-05-01", 0.048, 25000, 2, 1)
// Semi-annual payments, Actual/Actual basis
// $25,000 par value, 4.8% coupon
// Treasury Note
=ACCRINT("2024-01-01", "2024-04-01", "2024-02-15", 0.042, 50000, 4, 1)
// Quarterly payments, Actual/Actual basis
// $50,000 par value, 4.2% coupon
3. Municipal Securities:
// General Obligation Bond
=ACCRINT("2024-01-01", "2024-07-01", "2024-04-15", 0.038, 100000, 2, 0)
// Semi-annual payments, 30/360 basis
// $100,000 par value, 3.8% coupon
// Revenue Bond
=ACCRINT("2024-02-01", "2024-08-01", "2024-05-15", 0.042, 75000, 2, 0)
// Semi-annual payments, 30/360 basis
// $75,000 par value, 4.2% coupon
// Short-Term Note
=ACCRINT("2024-01-15", "2024-07-15", "2024-03-01", 0.035, 50000, 2, 0)
// Semi-annual payments, 30/360 basis
// $50,000 par value, 3.5% coupon
4. Structured Products:
// Asset-Backed Security
=ACCRINT("2024-01-01", "2024-04-01", "2024-02-15", 0.052, 250000, 4, 2)
// Quarterly payments, Actual/360 basis
// $250,000 par value, 5.2% coupon
// Mortgage-Backed Bond
=ACCRINT("2024-02-01", "2024-05-01", "2024-03-15", 0.048, 500000, 12, 0)
// Monthly payments, 30/360 basis
// $500,000 par value, 4.8% coupon
// CDO Tranche
=ACCRINT("2024-01-15", "2024-04-15", "2024-03-01", 0.065, 1000000, 4, 2)
// Quarterly payments, Actual/360 basis
// $1,000,000 par value, 6.5% coupon
5. International Securities:
// European Corporate Bond
=ACCRINT("2024-01-01", "2024-07-01", "2024-04-15", 0.045, 100000, 2, 4)
// Semi-annual payments, 30/360 European basis
// €100,000 par value, 4.5% coupon
// Sovereign Bond
=ACCRINT("2024-02-15", "2024-08-15", "2024-05-01", 0.058, 200000, 2, 1)
// Semi-annual payments, Actual/Actual basis
// $200,000 par value, 5.8% coupon
// Emerging Market Bond
=ACCRINT("2024-01-15", "2024-07-15", "2024-04-01", 0.075, 150000, 2, 0)
// Semi-annual payments, 30/360 basis
// $150,000 par value, 7.5% coupon
6. Portfolio Analysis Examples:
// Clean Price Calculation
=ACCRINT("2024-01-01", "2024-07-01", "2024-03-15", 0.05, 1000000, 2, 0)
// For determining dirty price: market_price + accrued_interest
// Yield Analysis
=ACCRINT("2024-02-01", "2024-08-01", "2024-05-15", 0.06, 500000, 2, 1)
// Component of yield-to-maturity calculation
// Interest Income Projection
=ACCRINT("2024-01-15", "2024-07-15", "2024-06-30", 0.055, 750000, 2, 0)
// For period-end income forecasting
Interactive Spreadsheet Demo
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<div id="spreadsheet"></div>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZjY1NTEyZjM4Zjk3ZmFkYzFkNjMxMDY2NWE1ZDA4ZjRhYzc1ZWY5YmYzYmZkODU3MGM3M2M2ODljMGViNTMwN2I3Y2MxNjU4NWRjYmRkZTNlYTBiZjU5YjlkNTBmYjk1MzA3ZTQ2Njg1ZDU1MjI1YmYwYjI3NWRkMTRiNDE2N2QsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOekF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Issue Date",
"First Interest",
"Settlement",
"Rate",
"Par Value",
"Frequency",
"Basis",
"Accrued Interest"
],
[
"2023-01-15",
"2023-07-15",
"2023-04-15",
0.065,
1000,
2,
0,
"=ACCRINT(A2,B2,C2,D2,E2,F2,G2)"
],
[
"2023-03-01",
"2023-09-01",
"2023-06-01",
0.045,
5000,
2,
1,
"=ACCRINT(A3,B3,C3,D3,E3,F3,G3)"
],
[
"2023-02-10",
"2023-08-10",
"2023-05-10",
0.055,
2500,
2,
0,
"=ACCRINT(A4,B4,C4,D4,E4,F4,G4)"
]
]
}]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set license
jspreadsheet.setLicense('ZjY1NTEyZjM4Zjk3ZmFkYzFkNjMxMDY2NWE1ZDA4ZjRhYzc1ZWY5YmYzYmZkODU3MGM3M2M2ODljMGViNTMwN2I3Y2MxNjU4NWRjYmRkZTNlYTBiZjU5YjlkNTBmYjk1MzA3ZTQ2Njg1ZDU1MjI1YmYwYjI3NWRkMTRiNDE2N2QsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOekF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Issue Date",
"First Interest",
"Settlement",
"Rate",
"Par Value",
"Frequency",
"Basis",
"Accrued Interest"
],
[
"2023-01-15",
"2023-07-15",
"2023-04-15",
0.065,
1000,
2,
0,
"=ACCRINT(A2,B2,C2,D2,E2,F2,G2)"
],
[
"2023-03-01",
"2023-09-01",
"2023-06-01",
0.045,
5000,
2,
1,
"=ACCRINT(A3,B3,C3,D3,E3,F3,G3)"
],
[
"2023-02-10",
"2023-08-10",
"2023-05-10",
0.055,
2500,
2,
0,
"=ACCRINT(A4,B4,C4,D4,E4,F4,G4)"
]
];
// Render component
return (
<Spreadsheet ref={spreadsheet}>
<Worksheet data={data} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet">
<Worksheet :data="data" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";
// Set license
jspreadsheet.setLicense('ZjY1NTEyZjM4Zjk3ZmFkYzFkNjMxMDY2NWE1ZDA4ZjRhYzc1ZWY5YmYzYmZkODU3MGM3M2M2ODljMGViNTMwN2I3Y2MxNjU4NWRjYmRkZTNlYTBiZjU5YjlkNTBmYjk1MzA3ZTQ2Njg1ZDU1MjI1YmYwYjI3NWRkMTRiNDE2N2QsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOekF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Issue Date",
"First Interest",
"Settlement",
"Rate",
"Par Value",
"Frequency",
"Basis",
"Accrued Interest"
],
[
"2023-01-15",
"2023-07-15",
"2023-04-15",
0.065,
1000,
2,
0,
"=ACCRINT(A2,B2,C2,D2,E2,F2,G2)"
],
[
"2023-03-01",
"2023-09-01",
"2023-06-01",
0.045,
5000,
2,
1,
"=ACCRINT(A3,B3,C3,D3,E3,F3,G3)"
],
[
"2023-02-10",
"2023-08-10",
"2023-05-10",
0.055,
2500,
2,
0,
"=ACCRINT(A4,B4,C4,D4,E4,F4,G4)"
]
]
return {
data
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZjY1NTEyZjM4Zjk3ZmFkYzFkNjMxMDY2NWE1ZDA4ZjRhYzc1ZWY5YmYzYmZkODU3MGM3M2M2ODljMGViNTMwN2I3Y2MxNjU4NWRjYmRkZTNlYTBiZjU5YjlkNTBmYjk1MzA3ZTQ2Njg1ZDU1MjI1YmYwYjI3NWRkMTRiNDE2N2QsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOekF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
[
"Issue Date",
"First Interest",
"Settlement",
"Rate",
"Par Value",
"Frequency",
"Basis",
"Accrued Interest"
],
[
"2023-01-15",
"2023-07-15",
"2023-04-15",
0.065,
1000,
2,
0,
"=ACCRINT(A2,B2,C2,D2,E2,F2,G2)"
],
[
"2023-03-01",
"2023-09-01",
"2023-06-01",
0.045,
5000,
2,
1,
"=ACCRINT(A3,B3,C3,D3,E3,F3,G3)"
],
[
"2023-02-10",
"2023-08-10",
"2023-05-10",
0.055,
2500,
2,
0,
"=ACCRINT(A4,B4,C4,D4,E4,F4,G4)"
]
]
}]
});
}
}