EFFECT in Google Sheets

What is EFFECT in Google Sheets?

The EFFECT function in Google Sheets calculates the effective annual interest rate based on a given nominal interest rate and the number of compounding periods per year. It shows the actual return on an investment or the true cost of a loan, considering the effect of compounding. The EFFECT function compares different loan or investment options where interest is compounded at various frequencies. It allows you to see the true cost or return after accounting for compounding.

In the example below, let us calculate the effective annual interest rate if the nominal interest rate is 5% compounded monthly.

=EFFECT(0.05, 12).

The output of the EFFECT function is a decimal. To display as a percentage, go to the toolbar and click the % (percent) button.

Syntax

Before doing a deep dive into the function, let us look at the EFFECT formula in Google Sheets. The syntax of EFFECT in Google Sheets is as follows:

=EFFECT(nominal_rate, periods_per_year)

The arguments include:

• nominal_rate: The stated interest rate per year
• periods_per_year: The number of times interest is compounded per year (e.g., 1 for annually, 12 for monthly).

In brief, the EFFECT function will adjust the nominal rate thereby showing the true annual rate which one will earn or pay after consideration of compounding. This helps you take good financial decisions.

How To Use EFFECT Function in Google Sheets?

The EFFECT function is a financial formula that is used to calculate the effective annual interest rate using the nominal interest rate that is compounded many times per year. It is especially useful for comparing different loan or investment options with varying compounding periods.

One can use the EFFECT function in two ways:

• Typing the EFFECT formula manually
• Inserting it through the Google Sheets menu

Using the EFFECT Function Manually

Let’s go through a step-by-step example to understand how to enter the EFFECT function manually. A man has a loan with a nominal annual interest rate of 8%. This is compounded quarterly. He has to find out the effective annual interest rate.

Step 1: First, enter the details such as the nominal rate and the number of compounding periods in a sheet, as shown below.

Step 2: Click on a cell where you want to display the result; here we use cell B3.

Enter the EFFECT formula as shown below:

=EFFECT(B1, B2)

This formula calculates the effective interest rate.

Step 3: Press Enter. You get the effective annual interest rate, which in this case will be around 8.24%.

To convert it to a percentage, go to the % sign in the top menu.

Using EFFECT Through the Menu Bar

You can also insert the EFFECT function using Google Sheets’ built-in menus.

1. Go to the top menu.
2. Click on Insert → Function → Financial
3. From the list, scroll down and choose EFFECT. Fill in the arguments
4. Press Enter to see the effective annual rate.

Examples

Let’s walk through some practical examples to see how the EFFECT function works in Google Sheets. It will help you understand how to calculate the effective annual interest rate from a nominal rate with multiple compounding periods.

Example #1

A person is evaluating different savings accounts offered by various banks. Each one of the banks provides a nominal annual interest rate, but the number of times interest is compounded each year varies. Let us use the EFFECT function to calculate every bank’s effective annual rate to compare them fairly.

Step 1: Enter the following data into a sheet, as shown below.

• Column B contains the nominal interest rate as decimal.
• Column C shows how many times the interest is compounded annually.

Step 2: Enter the EFFECT formula

=EFFECT(B2, C2)

Then drag it down to fill in the other rows (D3, D4).

Step 3: Convert it to a percentage by clicking on the % symbol in the top menu. Review the effective rates calculated for each bank, considering the compounding frequency.

Example #2 – Using EFFECT with  PMT Function

A person has a loan of $200,000 with a nominal interest rate of 6%. It is compounded monthly, and they wish to pay it off over 30 years. Let us calculate the effective interest rate and the monthly payment to pay it off in 30 years.

Step 1: Let us first calculate the effective interest rate. We enter the details as shown below.

Step 2: Let us find the effective interest rate as follows:

=EFFECT(B2/100, B3)

(This returns approximately 0.061678)

Step 3: Let us now calculate the monthly payment. Here we use the PMT function. The syntax of the PMT function in Google Sheets is as follows:

=PMT( [effective_rate], nper, pv, [fv], [type] )

• effective_rate is the calculated effective annual interest rate.
• nper is the total number of payment; for 30 years, nper = 30 * 12 = 360).
• pv is the present value of the loan
• fv (optional) is the future value of the loan

=PMT(B6, B5, B1)

This returns approximately -$12335.56.

Example #3 – Using EFFECT with Conditional Formatting

Let us compare several loans from different lenders. Each loan has a nominal annual rate and a different compounding frequency. Let us calculate the effective annual rate for each loan and automatically highlight those with high rates above 9% using conditional formatting.

Step 1: Enter the loan data in a Google Sheet as shown below:

Step 2: In cell D2, let us calculate the effective interest rate as shown below:

=EFFECT(A2, B2)

Drag this formula down to apply it to the rest of the rows in column C.

Step 3: Let us apply conditional formatting to highlight all cells where the effective interest rate is greater than 9%.

1. Go to Format → Conditional formatting
2. Under Format cells if, choose “Greater than”
3. Enter the value: 0.09
4. Choose a formatting style (e.g., red fill, bold white text). Click Done.

With this, any loan with an effective rate above 9% will be automatically highlighted, helping one spot the most expensive options.

Important Things to Note

1. The #NUM! error occurs when the nominal rate is less than or equal to 0 or the number of compounding periods per year is less than 1.
2. We get the #VALUE! error when we use EFFECT in Google Sheets is if any of the arguments (nominal rate or compounding frequency) are non-numeric or contain text instead of numbers.
3. The EFFECT function expects the nominal rate as a decimal.
4. The compounding frequency must be a positive number like 1 for annually, 2 for semi-annually, 4 for quarterly, and so on.
5. The EFFECT function can be used to compare financial products with different compounding schedules.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand EFFECT Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What is EFFECT Function in Google Sheets. We learn how to use it to find the effective annual interest rate with step-wise examples. You can learn more from the following articles. –

PRICEDISC in Google Sheets

INTRATE in Google Sheets

ARRAYFORMULA in Google Sheets