Effective annual rate: EFFECT and NOMINAL

◈ 9 cards

Compare investments on the effective annual rate, never on the quoted one — convert nominal to effective with =EFFECT, back with =NOMINAL, and pick the better GIC.

Two rates with the same name

A rate quoted as "8 % compounded monthly" is a nominal (or stated) rate: 8 % is what you would earn if the twelve monthly credits did not compound. They do, so the money actually grows by more. The effective annual rate (EAR) is the single once-a-year rate that produces the same growth:

$E A R = (1 + \frac{r}{p})^{p} - 1$

Excel has both directions built in: =EFFECT(nominal_rate, npery) and its inverse =NOMINAL(effect_rate, npery), where npery is the number of compounding periods a year. The EAR is the rate to compare on, because it removes the frequency from the quote and leaves only what a dollar becomes in a year.

Worked example — 8 %, four ways

Monthly: =EFFECT(0.08,12) = (1 + 0.08/12)¹² − 1 = 0.0830, or 8.30 %.
Semi-annual: =EFFECT(0.08,2) = 1.04² − 1 = 0.0816.
Quarterly: =EFFECT(0.08,4) = 0.0824. Annual: 0.0800, by definition.

The more often it compounds, the higher the EAR — but with a ceiling. The table in the code block below runs p out to daily (0.0833) and then to the limit, continuous compounding, $e^{0.08} - 1 = 0.0833$ . Daily and continuous agree to four places; the effect of frequency is real but small.

The choice the paper sets

Northshore Timber's treasurer has two one-year GIC quotes: 7.9 % compounded monthly and 8.0 % compounded semi-annually. The second looks better. On EAR:

$7.9 % monthly: (1 + \frac{0.079}{12})^{12} - 1 = 0.081924 8.0 % semi-annual: 1.0 4^{2} - 1 = 0.0816$

=EFFECT(0.079,12) beats =EFFECT(0.08,2) by about 3 basis points. On $1,000,000 that is roughly $324 a year in favour of the lower quoted rate. Compare on EAR, every time.

Going the other way

A lender wants a product whose EAR is exactly 6 % with monthly compounding. What rate goes on the sign? =NOMINAL(0.06,12) = 0.058411 — 5.8411 % compounded monthly grows a dollar by exactly 6 % in a year. Check: =EFFECT(0.058411,12) returns 0.0600.

Two to convert, cold

=EFFECT(0.06,4) = 0.0614 (6 % quarterly is 6.14 % effective). The trap answer is (1.06)^4-1 = 0.2625 — the annual rate compounded four times, the p-appears-twice error in a new coat. And =EFFECT(0.05,365) = 0.0513: a daily-compounded 5 % is 5.13 %.

The EAR rises with frequency but flattens fast: daily and continuous agree to four decimals. The quoted 8 % is the floor, never the figure to compare on.