EFFECT

What it does

EFFECT(nominal_rate, npery)

EFFECT(nominal_rate, npery) converts a nominal annual rate compounded npery times a year into the effective annual rate — the single annual rate that produces the same year-end value with one compounding.

EFFECT = (1 + nominal_rate/npery)^npery − 1

The identity behind it: growing at r/n per period for n periods multiplies principal by (1 + r/n)^n, and the effective rate is that multiplier minus one. A 10% nominal rate compounded monthly is not 10% a year — it is about 10.47%, and the gap is the whole reason the function exists. Consumer-credit disclosure regimes worldwide are built on this conversion.

Domain and range: for nominal_rate > 0 and integer npery ≥ 1, EFFECT is strictly increasing in npery and strictly greater than nominal_rate for every npery > 1. At npery = 1 it is exactly nominal_rate.

The limiting case is the interesting one. As n → ∞,

(1 + r/n)^n → e^r        so        EFFECT → e^r − 1

which is continuous compounding. That limit is approached but never reached through this function, because npery is truncated to an integer — there is no "continuous" setting. EXP(r) − 1 is the formula for that, and EFFECT with a very large npery approximates it from below.

The function is not an annualization of a period rate in the simple-interest sense. EFFECT compounds; INTRATE and DISC do not. Feeding a money-market discount quote to EFFECT produces a number with no financial meaning.

Arguments

Two documented rules:

1. npery is truncated to an integer. npery = 12.9 compounds twelve times, not 12.9 times.
2. npery must be at least 1 and nominal_rate must be greater than zero; both violations are #NUM!.

npery is not restricted to the conventional 1, 2, 4, 12 — 365 (daily) and 52 (weekly) are accepted and are exactly what the function is for.

Try it

Quirks

Result and edge cases

Returns Number — an effective annual rate as a decimal fraction.

• npery = 1 returns nominal_rate — up to floating-point rounding. It is not guaranteed to return the input's exact bits, because the computation still forms (1 + r)^1 − 1.
• nominal_rate ≤ 0 is an error, not a computation. This is a documented restriction, not a mathematical boundary: (1 + r/n)^n − 1 is perfectly well defined for r = 0 (giving 0) and for small negative r. Excel refuses both. A model that needs the effective rate of a zero or negative nominal rate cannot use this function and must write the formula out.
• Large npery approaches EXP(nominal_rate) − 1 from below and stays finite; there is no overflow for any realistic rate.
• Non-integer npery truncates toward zero, so 0.9 becomes 0 and therefore #NUM!.
• Empty, missing and error arguments follow the shared call model; see The value universe and Coercion and lifting.

Errors

As documented on Microsoft's EFFECT page:

How to implement

Numerical notes

The formula (1 + r/n)^n − 1 is the textbook expression and it is the worst way to evaluate the quantity when r/n is small.

Two error sources compound. First, 1 + r/n loses the low bits of r/n when the ratio is tiny — for a 1% rate compounded daily, r/n ≈ 2.7×10⁻⁵, so roughly five significant digits of the input are discarded in the addition. Second, the result of the power is close to 1, so the final subtraction of 1 is a cancellation that exposes exactly those damaged bits. The two effects are multiplicative in the relative error, and the damage grows with n.

The standard remedy is the expm1/log1p pair:

EFFECT = expm1(npery × log1p(nominal_rate/npery))

log1p computes ln(1 + x) accurately for small x without forming 1 + x, and expm1 computes e^x − 1 without forming the intermediate that cancels. Together they hold full relative accuracy across the whole admissible domain. This is the same pairing that appears throughout the annuity functions in this family, and the standard reference treatment is in the fdlibm/Cody-and-Waite lineage that every modern libm inherits.

The Handbook does not claim what Excel does internally for either function.

Rust code

15 effect_fn "EFFECT" (2, 2) [V "nominal_rate", V "npery"] |s, a, sv| {
    tv_out(s, tv::effect(num(s, a, 0)?, num(s, a, 1)?))
}

pub fn effect(nominal_rate: f64, periods_per_year: f64) -> Result<f64, FinancialError> {
    validate_finite(&[nominal_rate, periods_per_year])?;
    let periods = periods_per_year.trunc();
    if nominal_rate <= 0.0 || periods < 1.0 {
        return Err(FinancialError::Num);
    }
    // W109 G6-12 / BUG-FUNC-043: EFFECT's integer power is LSB-first binary
    // exponentiation, but it belongs to the legacy x87 spill-loop body class:
    // EVERY accumulator multiply and squaring is RN53(RN64(a*b)). POWER's
    // ordinary-f64 integer helper is therefore not interchangeable. The base
    // wrapper is also x87-spilled (`1 + nominal/n`; four targeted add-rounding
    // probes distinguish it 4/4). The loop is selected only while the truncated
    // count is below `u32::MAX`; at and above that exact boundary Excel switches
    // to the stored-base raw x87 LN/product/EXP chain. Banked grid 315/315;
    // fresh blind grid 870/870; fresh boundary/extreme-domain battery 160/160.
    let base = excel_x87_add(1.0, excel_x87_div(nominal_rate, periods));
    let compounded = if periods < u32::MAX as f64 {
        let mut exponent = periods as u32;
        let mut accumulator = 1.0;
        let mut factor = base;
        while exponent > 0 {
            if exponent & 1 == 1 {
                accumulator = excel_x87_mul(accumulator, factor);
            }
            exponent >>= 1;
            if exponent > 0 {
                factor = excel_x87_mul(factor, factor);
            }
        }
        accumulator
    } else {
        excel_pow_chain(base, periods)
    };
    let result = excel_x87_sub(compounded, 1.0);
    if result.is_finite() {
        Ok(result)
    } else {
        Err(FinancialError::Num)
    }
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• NOMINAL — the exact inverse. NOMINAL(EFFECT(r, n), n) should return r up to floating-point rounding, and the round trip is the natural self-test for both. The two functions carry identical domain restrictions.
• RRI — the compound rate implied by a present value, a future value and a period count. Where EFFECT translates a quoted rate between conventions, RRI extracts a rate from observed values.
• FV / FVSCHEDULE — the growth these rates describe. FV(EFFECT(r,n), 1, 0, −P) and FV(r/n, n, 0, −P) agree by construction; that agreement is the definition of the effective rate, and it makes a good check.
• EXP — the continuous-compounding limit, EXP(r) − 1, which EFFECT approaches as npery grows and never reaches.
• XIRR / IRR — internal rates of return, which are already effective rates over their own period; do not pass them through EFFECT a second time.
• Confused with: APR versus APY in consumer lending. EFFECT maps APR to APY. Which of the two a jurisdiction requires you to disclose is a legal question, not a spreadsheet one.

Named variants

No named variants are recorded yet.