Documentation/Calc Functions/ACCRINT

    From The Document Foundation Wiki

    Function name:

    ACCRINT

    Category:

    Financial Analysis

    Summary:

    Calculates the accrued interest for a security with periodic interest payments.

    Syntax:

    ACCRINT(Issue; FirstInterest; Settlement; Rate; [Par]; Frequency[; Basis])

    Returns:

    Returns a positive real number which is the sum of the accrued interest in each coupon period from issue date until settlement date, expressed in the same currency units that were used for the security’s par value.

    Arguments:

    Issue is a date, or a reference to a cell containing that date, which is the issue date of the security.

    FirstInterest is a date, or a reference to a cell containing that date, which is the first interest date of the security. The function does not currently use the value of this argument in the calculation of the accrued interest. However, it must be present and represent a valid date.

    Settlement is a date, or a reference to a cell containing that date, which is the date for which the interest accrued is to be calculated.

    Rate is a positive real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, which is the annual nominal rate of interest (coupon rate).

    Par is a positive real number, or a reference to a cell containing that number, which is the par value of the security. If Par is omitted, the function uses the value 1,000.

    Note pin.svg

    Note:
    We recommend that you always specify the value that you require for ACCRINT's Par argument, rather than allowing Calc to apply an arbitrary default. This will make your formula easier to understand and easier to maintain.

    Frequency is a positive integer, or a reference to a cell containing that integer, which is the number of interest payments per year (1, 2 or 4).


    Basis is an integer in the range 0 to 4, or a reference to a cell containing that integer, which indicates how the year is to be calculated. For more background information, visit Wikipedia's Day count convention page.

    Basis Calculation
    0 or missing US method (NASD), 12 months of 30 days each
    1 Exact number of days in months, exact number of days in year.
    2 Exact number of days in month, year has 360 days.
    3 Exact number of days in month, year has 365 days.
    4 European method, 12 months of 30 days each.


    • If any of Issue, FirstInterest, or Settlement is not a valid date, then ACCRINT returns a #VALUE! error.
    • If either Rate or Frequency is non-numeric, then ACCRINT reports a #VALUE! error.
    • If either Par or Basis is non-numeric, then ACCRINT reports an invalid argument error (Err:502).
    • If the Issue date is after the Settlement date, then ACCRINT reports an invalid argument error (Err:502).
    • If either Rate or Par is less than or equal to 0, then ACCRINT reports an invalid argument error (Err:502).
    • If either of Frequency or Basis is a non-integer value, then ACCRINT truncates it to an integer value.
    • If, after any truncation, Frequency is not equal to 1, 2, or 4, then ACCRINT reports an invalid argument error (Err:502).
    • If, after any truncation, Basis is not equal to 0, 1, 2, 3, or 4, then ACCRINT reports an invalid argument error (Err:502).

    Additional details:

    • The formula for ACCRINT is:
    [math]\displaystyle{ ACCRINT~=~\left ( \frac{Rate\times Par}{Frequency} \right )\times \left ( \frac{Days~of~interest~earned}{Days~in~payment~period}\right ) }[/math]
    • ACCRINT is not fully compliant with the functional definition in Section 6.12.2 of ODF 1.2, as follows:
    1. Calc's ACCRINT function does not currently support a calc_method argument.
    2. Calc's ACCRINT function does not currently support the 12 (monthly) Frequency option.
    3. Although Calc's ACCRINT function accepts a value for the FirstInterest argument and checks that it is a valid date, its value is not currently used in the calculation of accrued interest.
    4. Calc's ACCRINT function allows the user to include no value for the Par argument.
    • Be aware that Excel does not support option 2 for the Basis argument (actual number of days in month, 360 days in year). This difference may limit interoperability when you have a Calc spreadsheet containing AMORLINC calls (with Basis set to 2) that is later opened in Excel.

    Examples:

    Formula Description Returns
    =ACCRINT(A1; A2; A3; A4; A5; A6; A7) where cells A1 to A3 contain the dates 2001-02-28, 2001-08-31, and 2001-05-01 respectively; cells A4 to A7 contain the numbers 0.1, 1500, 2, and 4 respectively. Here the function calculates how much interest has been accrued for a security issued on 2001-02-28 with a settlement date of 2001-05-01. The rate is 0.1 (10%) and par is 1500 currency units. Interest is paid half-yearly and the basis is the European method. 26.25
    =ACCRINT("2001-02-28"; "2001-08-31"; "2001-05-01"; 0.1; 1500; 2) Here the function calculates how much interest has been accrued for a security issued on 2001-02-28 with a settlement date of 2001-05-01. The rate is 0.1 (10%) and par is 1500 currency units. Interest is paid half-yearly and since the final argument is omitted, the basis is the default US method. The change of basis leads to a different result to that in the previous example. Note that it is often good practice to utilize the DATE function instead of typing formatted date values - the formula =ACCRINT(DATE(2001; 2; 28); DATE(2001, 8, 31); DATE(2001; 5; 1); 0.1; 1500; 2) returns exactly the same result. 25.4166666666667
    =ACCRINT("2001-02-28"; "2021-08-31"; "2001-05-01"; 10%; ; 2; 4) Here the function calculates how much interest has been accrued for a security issued on 2001-02-28 with a settlement date of 2001-05-01. The rate is 10% (0.1) and par assumes the deafult value of 1000 currency units. Interest is paid half-yearly and the basis is the European method. 17.5
    =ACCRINT(3; 4; 400; 0.1; 1500; 4; 4) Here the function calculates how much interest has been accrued for a security using issue and settlement dates specified as date-time serial numbers. The rate is 0.1 (10%) and par is 1500 currency units. Interest is paid half-yearly and the basis is the European method. 162.916666666667

    Related LibreOffice functions:

    ACCRINTM

    ODF standard:

    Section 6.12.2, part 2

    Related (or similar) Excel functions:

    ACCRINT