Description

EffAnnRateλ() is one of the hard-to-remember-formula functions. Function names are easier to remember than formulas. This function calculates effective annual interest rate (EAR, EIR, AER) adjusted for compounding over a given period. EffAnnRateλ uses this formula:

(1 + AnnualRate/PeriodsPerYear) ^ PeriodsPerYear -1

The effective annual interest rate (EAR) is a key tool for evaluating the true return on an investment or the true cost of a loan. Because it accounts for compounding, the EAR can differ significantly from the stated annual interest rate. This makes it essential when comparing loans or determining which investment offers the highest return. When compounding is present, the EAR is always higher than the stated annual interest rate.

Transparent:

5g functions' inner workings are visible in Name Manager or, better still, Microsoft's free add-in: Advanced Formula Environment and their formulas are 100% native Excel which should be familiar to all modelers. 

Simplified:

5g functions are simpler to enter than the formulas they replace.

Benefit:
Packaging difficult to remember formulas into functions prevents errors due to remembering incorrectly or mistyping the formula. Functions also prompt for inputs with human readable language so its less likely we will use the wrong assumptions.

Example

In this example the bank offers our deposit of $10,000 at 12% stated interest rate compounded monthly. The corkscrew account shows the impact of compounding 12% monthly. At the end of 12 months, the principal has increased by 1,268; thus, the EAR is 12.68% as compared to the quoted 12%: