Excel ACCRINTM Function
The ACCRINTM function calculates accrued interest for securities that pay interest at maturity, essential for bond valuation and analysis.
Excel
Google SheetsExcelGoogle Sheets=ACCRINTM(issue, settlement, rate, par, [basis])Quick Answer
ACCRINTM function ACCRINTM function calculates accrued interest for securities that pay interest at maturity, such as Treasury bills and zero-coupon bonds. Unlike ACCRINT which handles periodic interest payments, ACCRINTM is specifically designed for securities with a single interest payment at maturity.
=ACCRINTM(issue, settlement, rate, par, [basis])- issue - the security's issue date
- settlement - the security's settlement date
- rate - the annual coupon rate
- par - the security's par value (face value)
- basis - the optional day count basis (0-4)
Practical Examples
Basic Treasury Bill Interest Calculation
Calculate accrued interest for a Treasury bill purchased between issue and maturity
Corporate Zero-Coupon Bond Analysis
Calculate interest for a corporate bond that pays at maturity
International Security with European Basis
Calculate accrued interest using European 30/360 day count convention
Multiple Securities Comparison
Compare accrued interest across different securities with varying rates and dates
Error Handling for Bond Calculations
Prevent errors when calculating interest for securities with invalid parameters
Common Errors and Solutions
ACCRINTM returns #NUM! error
Settlement date is before or equal to issue date, or negative rate/par values
Verify that settlement date is after issue date and all rates and par values are positive
Use data validation or IF statements to check date order: =IF(settlement<=issue, "Invalid Dates", ACCRINTM(...))
Example:
ACCRINTM returns #VALUE! error
Date parameters stored as text rather than date serial numbers
Convert text dates using DATEVALUE or reconstruct with DATE(year, month, day) function
Use DATE functions for all date inputs and verify with =ISNUMBER(issue) that dates are stored as numbers
Example:
ACCRINTM produces unexpected interest amounts
Wrong day count basis selected for the security type
Verify the correct basis: US Treasury uses basis 1, corporate bonds use basis 0, money market uses basis 2
Create a reference table mapping security types to correct basis values and document assumptions
Example:
Best Practices and Pro Tips
Choose the Correct Day Count Basis
The basis parameter significantly impacts calculation accuracy. US Treasury securities typically use basis 1 (Actual/actual), corporate bonds use basis 0 (30/360), and money market instruments use basis 2 (Actual/360). Always verify the standard convention for your security type.
Implement Robust Date Validation
Date errors are the leading cause of ACCRINTM failures. Build validation into models by checking that settlement > issue before calculations run. Use data validation to restrict date entry ranges.
ACCRINTM vs ACCRINT
Use ACCRINTM for securities paying interest at maturity (Treasury bills, zero-coupon bonds). Use ACCRINT for securities with periodic coupon payments. Using the wrong function produces incorrect valuations.
Integrate with Complementary Functions
ACCRINTM rarely stands alone. Combine it with PRICE for full bond valuation, YIELD for return calculations, and DURATION for risk analysis.
Platform Compatibility
ACCRINTM works identically in Excel 2003+ and Google Sheets. Built-in for Excel 2007 and later, requires Analysis ToolPak in Excel 2003.
Need Help with Excel ACCRINTM Function?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
The ACCRINT function calculates accrued interest for a security that pays periodic interest, enabling accurate financial reporting and bond valuation.
ExcelSheetsThe FV function calculates future value of investments with constant payments and interest rate. Master retirement planning and savings goals with examples.
ExcelSheetsThe PV function calculates the present value of an investment or loan with constant periodic payments and a constant interest rate. Master PV with examples,...
ExcelSheetsConvert fractional dollar prices to decimals with DOLLARDE. Essential for bond trading, stock quotes, and financial analysis.
ExcelSheets