MS Excel: ACCRINTM for accrued interest

The ACCRINTM function in Excel calculates the accrued interest for a security that pays interest only at maturity.Unlike ACCRINT, where periodic payments are made, ACCRINTM is used when the full interest amount is paid once, together with the principal at the end of the investment term.

Syntax

ACCRINTM(issue, settlement, rate, par, [basis])

Argument	Description
issue	The date when the security was issued.
settlement	The date when the security matures (payment is made).
rate	The annual coupon interest rate of the security.
par	The par value of the security (usually $1,000).
basis (Optional)	Day count basis (default is 0 – US (NASD) 30/360).

Key Points

• Only for single-payment securities (no periodic payments).

• You must know the issue date, settlement date, and interest rate.

• The basis argument helps define how days are counted between dates.

• The result is the total interest earned between the issue and maturity dates.

Practical Example

Imagine you invest in a bond issued on January 1, 2024, that matures on January 1, 2025.The bond offers a 5% annual interest rate, and the par value is $1,000.

The formula:

=ACCRINTM(DATE(2024,1,1), DATE(2025,1,1), 0.05, 1000)

Result: $50(5% of $1,000 over one year)

Summary

Item	Value
Issue Date	01-Jan-2024
Maturity Date	01-Jan-2025
Annual Rate	5%
Par Value	$1,000
Basis	0 (30/360)

Important Notes

• If the settlement date is before the issue date, Excel returns a #NUM! error.

• If the rate or par value is ≤ 0, Excel also returns a #NUM! error.

• If basis is not within accepted values (0–4), Excel returns a #NUM! error.

• Using the correct basis is crucial when dealing with international bonds.

When to Use ACCRINTM?

Bonds that pay at maturity Short-term notes and certificates of deposit Financial products without regular coupon payments

Conclusion

The ACCRINTM function is perfect when dealing with zero-coupon bonds or other securities that pay one lump-sum interest at maturity.If you are working in banking, investments, or financial modeling, mastering ACCRINTM ensures your interest calculations are precise and reliable.