Jump to content

Documentation/Calc Functions/EFFECT

From The Document Foundation Wiki

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