Documentation/Calc Functions/INTRATE

    From The Document Foundation Wiki
    Other languages:

    Function name:

    INTRATE

    Category:

    Financial Analysis

    Summary:

    Calculates the uncompounded annual interest rate that would be equivalent to the income realized by purchasing a security (or other item) at one value and later selling or redeeming it at a different value. The security is assumed to provide no interest payments (examples of such securities might include zero-coupon bonds or US Treasury Bills).

    Syntax:

    INTRATE(Settlement; Maturity; Investment; Redemption[; Basis])

    Returns:

    Returns a real number which is the equivalent annual interest rate for a zero-interest security with the specified characteristics. The number returned is a percentage expressed as a decimal fraction (for example, the returned value 0.075 would represent 7.5%).

    Arguments:

    Settlement is a date (in quotation marks) or a date-time serial number, or a reference to a cell containing one of those types, which specifies the date of purchase of the security.

    Maturity is a date (in quotation marks) or a date-time serial number, or a reference to a cell containing one of those types, which specifies when the security is sold or redeemed.

    Investment is a positive real number, or a reference to a cell containing that number, which is the purchase price of the security.

    Redemption is a positive real number, or a reference to a cell containing that number, which is the selling price or redemption value of the security. Redemption must be in the same currency units as Investment.


    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 either Maturity or Settlement is not a valid date, then INTRATE reports a #VALUE! error.
    • If either Maturity or Settlement contains time components, then these are ignored by INTRATE.
    • If the Settlement date is on or after the Maturity date, then INTRATE reports an invalid argument error (Err:502).
    • If either Investment or Redemption is non numeric, then INTRATE reports a #VALUE! error.
    • If either Investment or Redemption is less than or equal to 0.0, then INTRATE reports an invalid argument error (Err:502).
    • If Basis is a non-integer value, then INTRATE truncates it to an integer value.
    • If, after any truncation, Basis is not equal to 0, 1, 2, 3, or 4, then INTRATE reports an invalid argument error (Err:502).

    Additional details:

    • The formula for INTRATE is:
    [math]\displaystyle{ \text{INTRATE}~=~\frac{Redemption\:-\:Investment}{Investment}\times\frac{1}{\text{YEARFRAC}(Settlement;\:Maturity;\:Basis)} }[/math]
    • As the above formula takes no account of compounding, INTRATE may be most reliable for periods of less than a year.

    Examples:

    Formula Description Returns
    =INTRATE(A1; A2; A3; A4; A5) where cells A1 and A2 contain the dates 2020-01-01 and 2023-06-30 respectively; cells A3:A5 contain the numbers 10000, 12000, and 3 respectively. Here the function calculates an equivalent annual interest rate for a security with the specified characteristics and returns the value 0.0572100313479624 (5.72%). 0.0572100313479624
    =INTRATE("2010-01-15"; "2022-05-05"; 1000000; 2000000) A painting was bought on 2010-01-15 for 1,000,000 currency units and sold on 2022-05-05 for 2,000,000 currency units. No value is specified for the Basis argument and so the default value of 0 (12 months of 30 days each) is applied. The function returns the value 0.0812641083521445 (8.13%). 0.0812641083521445
    =INTRATE(DATE(2010; 1; 15); DATE(2022; 5; 5); 1000000; 2000000; 3) Here the function calculates the equivalent annual interest for the same painting as in the previous example, but utilizes the DATE function instead of typing formatted date values and uses the value 3 for the Basis argument. The function returns the value 0.0812374805252615 (8.12%). 0.0812374805252615

    Related LibreOffice functions:

    RECEIVED

    YEARFRAC

    ODF standard:

    Section 6.12.22, part 2

    Related (or similar) Excel functions:

    INTRATE