Syntax. ACCRINT(Issue,First_Interest_Date,Settlement,Nominal_Interest,Par_Value, Frequency,Basis,Calculation_Method)
Definition. The function ACCRINT() calculates the accrued interest of a debt due in full intra-annually or the accrued interest of a fixed-interest security with periodic interest payments.
Arguments
Issue (required) Specifies the date of the loan agreement or the issue of the security.
First_Interest_Date (required) Identical to the due date if it is intra-annual; otherwise, results from the number of periods, which is defined by the frequency.
Settlement (required) The date when the ownership of the security changes.
Nominal_Interest (required) The agreed-upon yearly interest rate (coupon interest rate) of the debt instrument/loan/bond.
Par Value (optional) The nominal value of the security. If this argument is not specified, Excel calculates with 1000 monetary units (contrary to the information provided in Excel Help in Excel 2003 and earlier versions).
Frequency (required) Specifies the number of interest payments within a year. Valid values are the integers 1 = annual; 2 = biannual; 4 = quarterly.
Basis (optional) Defines the method you want to use for determining the days in the year according to Table 15-2, shown in the section titled AMORDEGRC(). If this argument is omitted, Excel calculates with Basis = 0.
Calculation method (optional) A logical value that decides whether the interest is calculated for the duration up to this point (TRUE or omitted) or whether only the interest since the last interest payment is specified (FALSE).
All function arguments that call for a date use the date without the time. In other words, fractions are rounded. The Frequency and Basis arguments also require integers and truncate decimal places.
If invalid dates are used or no numbers are entered where required, the function returns the error #VALUE!. If invalid numbers are entered for non-date arguments, the function returns the #NUMBER! error.
Important
To avoid unnecessary errors, please be aware of the following note from Excel Help:
“Dates should be entered with the DATE() function or as results of other formulas or functions. Problems might occur if dates are entered as text.”
Background. Interest for fixed-interest loans/bonds is paid once or several times a year depending on the Frequency argument. If the security changes ownership between interest deadlines, the buyer must pay the seller the interest rate as well as the accrued interest up to that date (which makes up the purchase price). If there is more than one interest date within the year, the accrued interest is calculated by dividing the nominal interest by the number of interest dates. With this (relative) interest rate, the days of the starting interest period are then calculated by using the day counting method specified in the Basis argument.
Example. A debt of $1,000.00, which is due in full with a nominal interest rate of 4 percent p.a. and a repayment by December 1, 2010, has been agreed upon on June 1, 2010. On August 9, 2010, there is a change in creditors. At this date, a real interest rate of 4.5 percent p.a. for the outstanding time until repayment is customary. What price does the person who takes over the debt pay?
To come to the point right away: You can simply determine the result with the functions PRICEMAT() and ACCRINT(). However, if you want to recreate what these functions are doing, think about the following:
How much interest has accrued up to today and must be paid to the creditor? The answer of $7.56 (after rounding) is
=ACCRINT(C2,C4,C3,4%,1000,1,4)
if cell C2 contains the date June 1, 2010; C3 the date August 9, 2010; and C4 the date December 1, 2010. You can test this by counting the days that have passed since the debt agreement (68, as also proven with DAYS360() and COUPDAYBS()) and determining the interest part with
1000*68/360*4%.What is the value of the disbursement of $1,000.00 plus interest (of 4%/2 for 1000, because of the difference of only half a year) on December 1, 2010, with the given real rate of interest on August 9, 2010? This is calculated by discounting the future value of $1,020.00 to the day of August 9, 2010. To do so, you can determine that there are 112 days (with Basis = 4) between the two dates. DAYS360() can help here, as can the function COUPDAYSNC(), which recognizes counters other than 30/360 days. The cash value is $1,005.92.
You get the same cash value (price) if you multiply the result of PRICEMAT() by 10 and then add the accrued interest. Multiplication by 10 is necessary because PRICEMAT() uses a par value of 100.