The EFFECT Function – Master Excels Financial Tools!

 

Interest computations are not always as simple as an equation of two plus two.

In fact, compound interest calculations can get super challenging.

However you are all covered as long as you can use the EFFECT function in Excel.

The article below will help you learn everything you need to know about the EFFECT function.

You can even use the methods found in this article to create a simple Interest Rate Calculator.

This is the type of real-world example we use on Excel courses when teaching new formulas.

Excel EFFECT Function

The EFFECT Function is one of the most powerful functions from the Excel Financial Functions Library.

The name of this function well describes its functionality: the EFFECT function returns the effective annual interest rate based on the number of compounding periods per year.

It is of great help to financial analysts as it helps them decide on loan packages that best suit their clients.

The EFFECT function is definitely not only meant for people from the fields of finances. Any regular user of Excel can employ it in various scenarios to form effective comparisons before they make the right decision.

– Syntax

Syntax of the EFFECT function consists of only two arguments.

 

– Arguments

The arguments offered by the EFFECT function syntax are broken down below.

• Nominal Rate (Required Argument) ─ It is the nominal or stated interest rate.
• Npery (Required Argument) ─ It is the number of compounding periods in a year.

Important Note!

The nominal rate fed in the formula must be an integer. Also, in the second argument, Npery must be a positive integer greater than or equal to1.

– Return Value

The EFFECT Function returns the effective annual interest rate based on the nominal annual interest rate and the number of compounding periods per year.

– Functions Library

Take the following route to quickly access the EFFECT function from Excel’s functions library.

 

Why would anyone want to use it?

The EFFECT function has a broad usage in the banking industry.

It helps banks build loan portfolios and also in deciding on the details of the financial products to be offered to their customers.

Let alone banks and other financial organizations; the EFFECT function is also super helpful for ordinary Excel users.

It helps them better decide on loans which meet their needs. Using the EFFECT Function, users can quickly compare various loan products available to them based on their compounding features.

People from different fields of life can apply it to their daily life to better plan their financial borrowings and lending matters.

Excel is great to use for finances because you can create and manage large databases with it!

excel-promo-2

EFFECT Function Use Cases

Listed below are some common instances where the EFFECT function can prove to be of great help.

• Banks massively use the EFFECT function to formulate various categories of loans to suit the needs of their clients.
• Semi-government and NGOs which offer public loans use the EFFECT function to attract clients with various packages.
• EFFECT function is very commonly used by loan awarding organizations in case of failure of their clients to pay back loans in time. The EFFECT function is used for interest compounding on overdue loans.

The EFFECT function offers an easy way to calculate the effective interest rate for people who are not from business and finance, and it can be used for more complex calculations when combined with logical functions like IF statements!

What does the EFFECT function do?

Before we delve into examples that help us learn how the EFFECT function works in Excel, let us look into the need for the EFFECT function.

Simply put, the EFFECT function computes the effective rate of interest when provided with a nominal rate of interest and the compounding periods per year. But what does that mean?

Let’s say you sign up for a loan amounting to $1,000 that is repayable in two equal annual instalments. The interest rate on the said loan stands at 10% (compounded semi-annually). How much interest are you paying?

The payments against the loan fall due to every year-end but the interest compounds semi-annually (two times a year). Here is how this works out numerically.

Start of Year 1
Outstanding Loan	$1,000	Principal amount of loan on Day 1
Interest	$50	($100 * 10% * 6 months / 12 months)
Middle of Year 1
Outstanding Loan	$1,050	(Sum of principal loan and the interest compounded till date)
Interest	$52.5	($1050 * 10% * 6 months / 12 months)
End of Year 1
Outstanding Loan	$1,102.5	(Sum of principal loan and the interest compounded till date)

 

By the end of year 1, the loan of $1,000 has increased up to $1,102.5 after being compounded semi-annually at the rate of 10%.

Had the same loan been subjected to interest at the rate of 10% compounded annually, the results would have been different.

Start of Year 1
Outstanding Loan	$1,000	Principal amount of loan on Day 1
Interest	$100	($100 * 10%)
End of Year 1
Outstanding Loan	$1,100	(Sum of principal loan and the interest compounded till date)

 

When compounded only once in a year, the loan at the end of year 1 amounts to $1,100 in contrast to $1,102.5 when compounded semi-annually.

 

What causes this difference in values?

Paying a little attention to the above math reveals that interest in both cases is charged on different values.

Under semi-annually compounding, the interest after half a year is charged at $1,050.

However, under the annual compounding method, interest is charged on $1,000 the whole year.

Greater the principal value, greater the amount of interest – simple, isn’t it?

 

Important Note!

Under the semi-annual compounding method, the interest rate is not a simple 10%. In fact, effectively, you are paying an interest rate of 10.25%.

How? The total interest to be paid in one year under the semi-annual compounding method amounts to $102.5 ($50 + $52.5).

When the nominal interest rate of 10% is applied to the principal loan amount of $1,000, the interest amounts only to $100.

However, the interest amounts to $102.5 only when the effective interest rate of 10.25% is applied to the principal loan amount of $1,000.

So, technically the effective interest rate suffered by you is not 10% but 10.25%.

 

 

With the EFFECT function to the rescue, you do not need to build up long tables to determine the effective interest to be paid under a loan agreement.

Calculating the percentage difference between two interest rates is a helpful way to compare different loans.

 

Use EFFECT Function To Calculate the Effective Rate of Interest – Example

Using the EFFECT function to calculate the effective rate of interest in Excel is pretty easy. Let’s take a look at the example below to see how it works.

The above data should help us learn how the EFFECT function can be used to calculate the effective interest rate under different compounding periods.

The above example is designed to calculate the compounding interest annually, semi-annually, quarterly, and monthly.

You can create a Data Model using this type of data.

Finding The Effective Interest Rate Under Annual Compounding

To find the effective interest rate under annual compounding, the EFFECT function needs to be set up as follows.

• • The first argument of the EFFECT function reflects the nominal rate, which is 15% in this case.
  • The second argument represents the number of compounding periods per year. Since the interest is only compounded annually, i.e., once a year, the number of periods is set to 1.

Select the Output cell, write the above formula, and hit Enter to see the results fetched by the EFFECT function.

The above image reflects the annual compounding interest rate.

Finding The Effective Interest Rate Under Semi-Annual Compounding

Semi-annual compounding means compounding twice per year. Hence the Npery will be set to 2, i.e. two times a year. The formula will look as follows.

Put the above formula in the designated cell and Press ‘Enter’ to see the results as follows:

The effective interest rate under the semi-annual compounding mechanism is 15.56%, in contrast to the nominal interest rate of 15%.

Pro-tip!

Do note how the interest rate takes an upward trend and goes on to increase as the number of compounding periods per year increases.

Finding The Effective Interest Rate Under Quarterly Compounding

Quarterly compounding equates to four (4) times compounding per year (at the end of each quarter). Replacing the Npery with 4 will calculate the effective interest rate under the quarterly compounding method. The EFFECT function will be set up as follows:

Input the above formula in the Output cell for quarterly interest rate calculation and Press ‘Enter’ to see the results as follows:

 

Finding The Effective Interest Rate Under Monthly Compounding

Monthly compounding would mean compounding 12 times a year. Fixing in the values, i.e. 12 for the Npery argument, will take the following form:

Insert the above formula in the corresponding output cell and Press ‘Enter’ to see the results as follows:

The above examples show how you can use the EFFECT function to calculate the effective interest rate under various compounding mechanisms.

Take a quick look into the formulas for calculating effective interest rates under different compounding periods – all collated together.

 

Effective Interest Rate Calculator in Excel

Alternatively, using the VLOOKUP function you can set up a quick, effective interest rate calculator in Excel .

Simply set up a key for compounding periods for once. Next, you only need to feed in the details of your loan product, and Excel will do the rest.

Work out your effective interest rate in an instant with this calculator. Take a look below!

 

EFFECT Function Troubleshooting

If you think your EFFECT function is not working as intended or is giving back inappropriate results, you may want to check for the following common issues:

#NUM! Error

If Excel gives back #NUM! Error to the EFFECT function, check for the following two reasons:

• • Is the nominal rate argument less than or equal to 0?
  • Have you set the number of periodic payments to below 1?

Take a look at the example below to find out the possible reasons why Excel might pose the #NUM error.

#VALUE! Error

The EFFECT function results in a #VALUE! Error when one or more of the arguments to the EFFECT function are text values.

In the example below, you will find that the text value specified for any argument of the EFFECT function will return a #VALUE error.

Inappropriate Results

At times, you might find the results of your EFFECT function to be inappropriate, or it may give an error message.

This is where learning how to audit a formula comes in handy as you learn to check if you’ve made a simple mistake, for example using a full stop instead of comma separator, or swapping theNominal rate with Npery.

Conclusion

That is how you can use the EFFECT function in Excel to calculate the effective interest rate for your loan products or even otherwise.

Practice this function using different sets of data with different compounding periods and payments falling due around different periods of the year.

Once you’ve mastered the EFFECT function, you won’t need to spend hundreds of dollars booking appointments with financial analysts!