Documentation/Calc Functions/EFFECT
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
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.
- For more background information, visit Wikipedia's Effective interest rate page.
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:
ODF standard:
Related (or similar) Excel functions:
EFFECT