MS Excel: ACCRINT function for security interest calculation

The ACCRINT function in Excel calculates the accrued interest for a security (like a bond) that pays periodic interest.It is widely used in financial analysis, especially when working with bonds, loans, or any investment product that generates interest over time.

Syntax

ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])

Argument	Description
issue	The date when the security was issued.
first_interest	The date when the first interest payment is made.
settlement	The date after the issue date when the security is traded to the buyer.
rate	The security’s annual coupon interest rate.
par	The par value of the security (default is $1,000).
frequency	Number of coupon payments per year: 1 = Annual, 2 = Semi-Annual, 4 = Quarterly.
basis (Optional)	Day count basis to use (default is 0 - US (NASD) 30/360).
calc_method (Optional)	Logical value: TRUE for actual accrued interest, FALSE for simplified interest calculation.

Key Points

• Mandatory Inputs: issue date, first interest date, settlement date, rate, par value, frequency.

• Optional Inputs: basis, calc_method.

• Output: Returns the accrued interest amount between issue and settlement dates.

Practical Example

Suppose you have a bond issued on January 1, 2024, the first interest is paid on July 1, 2024, you buy the bond on March 1, 2024.The bond's annual interest rate is 6%, face value is $1,000, and it pays interest semi-annually.

The formula:

=ACCRINT(DATE(2024,1,1), DATE(2024,7,1), DATE(2024,3,1), 0.06, 1000, 2)

Result: It will calculate the amount of interest you have "earned" from January 1st to March

1st.

Summary

Item	Value
Issue Date	01-Jan-2024
First Interest Date	01-Jul-2024
Settlement Date	01-Mar-2024
Annual Rate	6%
Par Value	$1,000
Payment Frequency	2 (Semi-Annual)
Basis	0 (30/360)
Calc Method	TRUE (default)

Important Notes

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

• If the rate or par is less than or equal to 0, Excel also returns a #NUM! error.

• If frequency is not 1, 2, or 4, Excel returns a #NUM! error.

• Always double-check your basis if you work internationally (different day count conventions).

Conclusion

The ACCRINT function is crucial when working with investments that accrue interest over time.From beginners managing basic bond purchases to advanced financial analysts tracking complex portfolios, ACCRINT provides the necessary precision for accurate accounting.