Effective vs Nominal Interest Rate

Excel Formulas › Financial

All versionsEFFECTNOMINAL

A “6% APR” compounded monthly actually earns more than 6% a year. EFFECT converts a stated (nominal) rate into the true effective annual rate; NOMINAL reverses it — essential for comparing offers on equal footing.

Quick formula: for a 6% nominal rate compounded monthly (12 periods):
=EFFECT(0.06, 12)
Returns ~0.0617 — the real 6.17% annual yield once monthly compounding is counted.

Functions used (tap for the full reference guide):

EFFECTNOMINAL

The example

A 6% nominal rate at different compounding frequencies.

AB
1CompoundingEffective rate
2Annual (1)6.00%
3Monthly (12)6.17%
4Daily (365)6.18%

The formula

The effective annual rate of 6% nominal, monthly:

=EFFECT(0.06, 12) // ≈ 6.17%

How it works

Compounding turns a nominal rate into a higher real one:

  1. EFFECT(nominal, periods) answers “what annual rate would I actually earn?” — 6% compounded monthly really yields ~6.17%.
  2. More frequent compounding → slightly higher effective rate (monthly beats annual; daily beats monthly).
  3. NOMINAL(effective, periods) goes the other way — the stated rate that produces a given effective rate.
  4. Use EFFECT to compare offers: a 5.9% daily-compounded loan vs a 6% annual one — convert both to effective rates first.

The formula behind it: EFFECT computes (1 + nominal/periods)^periods - 1. You can write that directly too, but EFFECT/NOMINAL make the intent (and the comparison) crystal clear.

Try it: interactive demo

Live demo

Set a nominal rate and compounding frequency.

Effective annual rate:

Variations

Nominal from effective

Reverse the conversion:

=NOMINAL(0.0617, 12)

By the raw formula

EFFECT spelled out:

=(1 + nominal/periods)^periods - 1

Compare two loans

Convert both APRs to EFFECT and pick the lower.

Pitfalls & errors

Rate as a decimal. Use 0.06, not 6. A whole number gives a nonsensical effective rate.

APR vs APY. APR is usually the nominal rate; APY is the effective rate. EFFECT converts APR→APY, which is what you compare.

Match the period count. Monthly is 12, daily 365 (or 360 in some conventions). Use the frequency the lender actually uses.

Practice workbook

📊
Download the free Effective vs Nominal Interest Rate practice workbook
A nominal rate at several frequencies with live EFFECT/NOMINAL, the raw-formula variant, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate the effective interest rate in Excel?
Use =EFFECT(nominal_rate, periods_per_year), e.g. =EFFECT(0.06, 12) gives about 6.17% for 6% compounded monthly. Enter the rate as a decimal.
What's the difference between nominal and effective rate?
The nominal rate is the stated APR; the effective rate (APY) reflects compounding within the year and is what you actually earn or pay. EFFECT converts nominal to effective.
How do I convert an effective rate back to nominal?
Use NOMINAL: =NOMINAL(effective_rate, periods) returns the stated rate that produces that effective rate.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Compound interest · Calculate a loan payment · CAGR

Function references: EFFECT · NOMINAL