With the Google Sheets EFFECT function, you can calculate the effective interest rate for an investment or a loan. The nominal interest rate does not give you the actual return on an investment like the fixed deposit or from a savings account. In contrast, the effective interest rate gives you the actual return.
Let's consider a scenario to understand the effective interest rate: Assume you want to invest Rupees 5000 in a fixed deposit, and have considered two banks for the same: The first bank offers a nominal interest rate of 5.0%, compounded quarterly. The second bank offers 5.25%, compounded semi-annually.
At the first glance on the interest rates, the second bank offer looks attractive. However, when you calculate the effective interest rate, the offer from the first bank is better. The reason is it compounds the interest quarterly.
Remember that more the frequency of compounding, the better the return from an investment or higher the interest amount you have to pay on a loan.
The formula for the effective interest rate is:
(1 + i / n) ^ (n) - 1
Here
i=nominal interest rate in percent
n=number of compounding periods
The value of n could be 365, 12, 4, 2, or 1 for the daily, monthly, quarterly, semi-annual, or annual compounding of interest respectively.
Let's look at the format of the Google Sheets EFFECT function formula:
=EFFECT(nominal_rate, periods_per_year)
Start the formula with an equal-to symbol.
EFFECT is the name of the formula that returns the effective interest rate.
nominal_rate is the nominal interest rate per year in percent.
periods_per_year is the compounding periods per year.
Here is an example of the EFFECT function:
=EFFECT(5%, 4)
In this example, 5% is the nominal interest rate and 4 is the compounding periods per year. The number 4 also indicates that interest is compounded quarterly.
The EFFECT returns 0.051, which is the annual effective interest rate of 5.1%.
Take a look at this video, which gives the steps to use the Google Sheets EFFECT function with an example.
Ещё видео!