The ACCRINTM() function calculates the accrued interest of a debt instrument (such as a bond or loan) that has a single interest payment at maturity. This is a simplified version of ACCRINT(), designed for securities with one annual interest period.
Syntax
ACCRINTM(Issue; Settlement; Nominal_Interest; Par_Value; Basis)
Arguments
- Issue (required) – The issuance date of the security.
- Settlement (required) – The date when ownership of the security changes.
- Nominal_Interest (required) – The annual interest rate of the security.
- Par_Value (optional) – The nominal value of the security. If omitted, Excel defaults to 1000 (contrary to older Excel Help documentation).
- Basis (optional) – Day-count convention.
Notes
- Dates must be entered without time values (decimals are truncated).
- Basis must be an integer (decimals are truncated).
- Errors:
- #VALUE! – Invalid dates or non-numeric entries.
- #NUMBER! – Invalid numeric inputs.
Background
- This function is specifically for single-payment securities (e.g., zero-coupon bonds or loans due in full at maturity).
- Unlike ACCRINT(), which handles periodic interest payments, ACCRINTM() assumes one annual interest period.
- The calculation method aligns with ACCRINT(), but with a simplified structure.
Examples
- Debt Due in Full
A $1,000 debt issued on June 1, 2010, with:
- 4% annual interest
- Ownership change on August 9, 2010
Formula:
=ACCRINTM(C2, C3, 4%, 1000, 4)
- C2 = Issue date (June 1, 2010)
- C3 = Settlement date (August 9, 2010)
Result:
Calculates the accrued interest from issuance to settlement.
- German Federal Government Bond (WKN 113517)
- Issued: October 25, 2000
- Nominal Interest: 5.5% (annual)
- Maturity: January 4, 2031
- Purchase Date: August 30, 2010
- Market Price: €143.27
Formula:
=ACCRINTM(C24, C25, 5.5%, 100, 4)
- C24 = Last interest payment date (January 4, 2010)
- C25 = Settlement date (August 30, 2010)
Result:
- Accrued Interest: €3.61 per bond
- Total Payment: €143.27 (price) + €3.61 (interest) = €146.88