where “rate” is the named range H4. The EFFECT function calculates the effective annual interest rate based on the nominal annual interest rate, and the number of compounding periods per year. To demonstrate how this works, the table shown in the example is set up with various compounding periods in column C. The nominal interest rate is provided in cell H4, which is the named range “rate”. The formula in D5 is: Because named ranges behave like absolute references, this formula can simply be copied down the table. The EFFECT function returns the calculated EAR for each of the given periods.
Manual check
The generic formula for calculating EAR (in Excel formula syntax) is: where n stands for periods, and i is the stated interest rate. This formula is used to check the results from EFFECT. In E5, the formula is: When this formula is copied down the table, the results in columns D and E match:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
