Among the most popular Excel formulas, the EFFECT formula is often used by financial professionals to determine the effective interest rate based on the nominal interest rate.
Excel, also called Annual Percentage Rate (APR) and Annual Percentage Yield (APY), makes it easy to calculate effective interest rates on mortgages, car loans, and small business loans based on nominal rates often quoted by lenders.
Comparison of nominal interest rates
Nominal interest rates are often quoted by lending institutions because they can make the cost of the loan lower than if the actual cost of the loan was indicated. This is due to the fact that usually several payments and interest accruals are made during the year.
Let’s say you take out a loan that requires monthly payments. As a result, interest is also charged monthly. The nominal interest rate, also called the annual interest rate (APR), is simply the monthly interest rate (say, 1% per month) multiplied by twelve (the number of periods in a year). This speaks of an interest rate of 12%.
However, since interest is charged on a monthly basis, the actual or effective interest rate is higher because the interest in the current month is made up of the interest in the previous month.
As it turned out, the loan at 12% per annum (nominal) has an effective (APY) interest rate of about 12.68%.
For a loan for a period of one year, the difference between 12% and 12.68% is minimal. For a long-term loan, such as a mortgage, the difference can be significant.
Read on to learn how to use the Excel EFFECT formula to calculate the effective interest rate (APY) based on the nominal interest rate (APR).
Use the Excel effect formula
Suppose you want to calculate the effective interest rate (APY) for a 12% nominal interest rate (APR) loan that is compounding monthly. You have customized your Excel sheet to look like below.
Note that we have the nominal interest rate (APR) in cell B1 and the number of payment periods in cell B2.
To calculate the effective interest rate (APY), click cell B3, click the Insert Function button, and choose Financial from the drop-down menu that says Or Select a Category.
Find and click the function titled EFFECT, and then click OK.
This will open the function argument window. Enter B1 in the Nominal_rate field and B2 in the Npery field. Then click OK.
Note that Excel places the number 0.1268 in cell B3. You can optionally change the format of cell B3 to a percentage.
Note that you can now change the values ??in both B1 and B2, and Excel will calculate the effective interest rate (APY) in cell B3. For example, change the nominal interest rate (APR) in B1 to 6% and change the effective interest rate (APY) in B3 to 6.17%.
Using the EFFECT function in Excel, you can calculate any effective interest rate for any nominal rate and the number of compounding periods per year.
–