If you want to calculate accrued interest on a security that pays interest at maturity in Excel, the ACCRINTM function is exactly what you need! 📈 Instead of complicated manual calculations, you can use ACCRINTM to find the total interest earned between issue and maturity dates — fast and accurately. In this guide, you’ll learn how to use the ACCRINTM function in Excel, with easy examples and keyboard shortcuts for Windows, macOS, and Ubuntu/Linux. ✅
🧠 What Is the ACCRINTM Function?
ACCRINTM calculates the interest accrued on a bond or security that only pays at maturity, not through periodic coupon payments.
Use it when dealing with:
- 💵 Zero-coupon bonds
- 📜 Treasury bills (T-bills)
- 💳 Certificates of deposit (CDs)
- 🏦 Loans without intermediate payments
💡 Instead of guessing, ACCRINTM gives you the exact interest value based on time elapsed and rates!
✅ Basic Syntax of ACCRINTM
=ACCRINTM(issue, settlement, rate, par, [basis])
- issue = The date when the security is issued
- settlement = The maturity (payment) date
- rate = Annual interest rate
- par = Face value of the security
- basis (optional) = Day count basis (default is 0 = US 30/360)
✅ Example: Calculate Accrued Interest
You buy a zero-coupon bond with:
- Issue date: 01-Jan-2024
- Maturity date: 01-Jan-2025
- Annual interest rate: 5%
- Par value: $1000
Formula:
=ACCRINTM(DATE(2024,1,1), DATE(2025,1,1), 5%, 1000)
✅ Result: $50
➡️ You earn $50 in interest by the maturity date!
✅ Optional: Adjusting the Basis
Basis defines how Excel counts days:
| Basis Number | Description |
|---|---|
| 0 | US (NASD) 30/360 (default) |
| 1 | Actual/Actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
🎯 Use the appropriate basis for your financial standard.
Example with basis:
=ACCRINTM(DATE(2024,1,1), DATE(2025,1,1), 5%, 1000, 1)
⌨️ Keyboard Shortcuts to Work Faster with ACCRINTM
| Action | Windows | macOS | Ubuntu/Linux (LibreOffice Calc) |
|---|---|---|---|
| Start typing a formula | = | = | = |
| Auto-complete function | Tab after typing ACCRINTM | Tab after typing ACCRINTM | Tab |
| Edit active cell | F2 | Ctrl + U | F2 |
| Insert current date (for issue or maturity) | Ctrl + ; (semicolon) | Cmd + ; | Ctrl + ; |
| Fill formula down | Ctrl + D | Cmd + D | Ctrl + D |
📌 Tip: Use Ctrl + ; to insert today’s date quickly when setting up sample data!
🐧 ACCRINTM in LibreOffice Calc (Ubuntu/Linux)
LibreOffice Calc does not have a direct ACCRINTM function, but you can manually calculate accrued interest:
Formula example:
=FaceValue * Rate * (DaysBetween/360)
You’ll have to calculate the number of days separately using DATEDIF() or DAYS().
🔄 Real-World Applications of ACCRINTM
| Scenario | Example Use |
|---|---|
| Calculating return on T-bills | Determine exact interest earned before maturity |
| Estimating bond returns | Evaluate accrued interest for accounting entries |
| Financial modeling | Predict earnings on zero-coupon bonds |
| Preparing tax reports | Show accrued (not yet received) interest income |
📈 Knowing how much you’ve earned — even before payment — is vital for accurate financial management!
🧯 Common Mistakes and Solutions
| Problem | Cause & Solution |
|---|---|
| Error in date format | Always use valid Excel dates (e.g., via DATE() function) |
| #NUM! Error | Settlement date is earlier than issue date — correct the order |
| Wrong accrued interest value | Double-check day basis and rate |
| Typing errors | Use Tab after typing ACCRINTM to auto-complete |
| Formula doesn’t update automatically | Make sure automatic calculation is enabled |
❓ Frequently Asked Questions (FAQs)
What does ACCRINTM stand for?
It means “Accrued Interest at Maturity” — calculates total interest by the maturity date.
Can ACCRINTM handle periodic payments?
No — use ACCRINT (without M) for securities that pay periodically.
How important is the basis argument?
Very! Different financial standards use different day counts — check your institution’s guidelines.
Can I use ACCRINTM for zero-coupon bonds?
Yes — it’s perfect for bonds that don’t pay regular interest but one lump sum at maturity.
Does ACCRINTM exist in LibreOffice Calc?
Not directly — you must calculate it manually using basic formulas.
What happens if I omit the basis argument?
Excel assumes US 30/360 as the day count convention.
✅ Conclusion: Excel ACCRINTM Function Simplifies Interest Calculations
Mastering the ACCRINTM function in Excel empowers you to calculate accrued interest accurately and efficiently — crucial for financial planning, accounting, and investment analysis! 📈 Whether you’re on Windows, macOS, or Ubuntu/Linux, you can quickly determine your earnings at maturity and stay ahead in your financial game.
Simplify your calculations — and make smarter financial decisions! 🚀
Complete List of Windows Keyboard Shortcuts
If you need help for Windows, you can find a whole list of all keyboard shortcuts here.
https://keyboard-shortcuts.org/
Advertisment