Documentation/Calc Functions/EFFECT

    From The Document Foundation Wiki
    Other languages:

    Function name:

    EFFECT

    Category:

    Financial Analysis

    Summary:

    Calculates an effective rate of interest based on a nominal interest rate, with interest credited and compounded multiple times during the period covered by the nominal interest rate.

    Syntax:

    EFFECT(NOM; P)

    Returns:

    Returns a non-negative real number, which is the effective interest rate for the specified arguments. The number returned is a percentage and, by default, is formatted as a percentage.

    Arguments:

    NOM is a non-negative 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 nominal interest rate.

    P is a positive integer, or a reference to a cell containing that number, which is the number of interest payment periods during the time frame to which NOM applies. For example, if NOM is an annual rate, then P might be set to 2 for semiannual interest payments, set to 4 for quarterly interest payments, or set to 12 for monthly interest payments. Note however that EFFECT only requires P to be a positive integer value and sets no other limits on it.

    • If either NOM or P is non-numeric, then EFFECT reports a #VALUE! error.
    • If NOM is less than 0.0, then EFFECT reports an invalid argument error (Err:502).
    • If P is less than 1.0, then EFFECT reports an invalid argument error (Err:502).
    • If P is a non-integer value, then EFFECT truncates it to an integer value.

    Additional details:

    • The formula for EFFECT is:
    [math]\displaystyle{ \text{EFFECT}(Rate;\:Periods)~=~ \left( 1\:+\:\frac{Rate}{Periods} \right)^{Periods}\:-\:1 }[/math]
    • EFFECT assumes that interest is credited at the end of exactly equal periods. However, this may not always be completely accurate – for example, different quarters of a year may comprise different numbers of days.

    Examples:

    Data for examples
    A B
    1 4.75% 4
    Formula Description Returns
    =EFFECT(A1; B1) An investment offers a nominal interest rate of 4.75% per year, but pays interest every quarter. EFFECT returns the value 4.84%, which is the effective interest rate per year that takes account of compounding. 4.84%
    =EFFECT(0.0475; 2) An investment offers a nominal interest rate of 4.75% per year, but pays interest every half year. EFFECT returns the value 4.81%, which is the effective interest rate per year that takes account of compounding. Note that this value is greater than the nominal interest rate but a little lower than the effective interest rate calculated in the previous example. 4.81%
    =EFFECT(0; 100) If the nominal interest rate is set to 0%, then EFFECT returns the value 0.00% however many payment periods are specified. 0.00%
    =EFFECT(13%; 1) If the number of payment periods is set to 1, then EFFECT always returns the specified nominal interest rate. 13.00%
    =EFFECT(133.33%; 117.99) The same value is returned by the formula =EFFECT(133.33%; 117), demonstrating that non-integer values of the P argument are truncated to the integer part. 276.50%

    Related LibreOffice functions:

    EFFECT_ADD

    NOMINAL

    NOMINAL_ADD

    ODF standard:

    Section 6.12.19, part 2

    Related (or similar) Excel functions:

    EFFECT