Syntax. EFFECT(Nominal_Interest,Periods)
Definition. This function calculates the equivalent effective annual interest yield (in arrears), also called the conform interest rate, from the nominal interest rate (in arrears) that is distributed to different periods of the same length within a year. Even though this is uncommon, a different time unit can also be substituted for the year.
Arguments
Nominal_Interest (required) Returns the annual interest rate which, because of the distribution over the periods, is used for a respective intra-annual interest yield.
Periods (required) The number of interest days per year. This number is usually 2, 4, or 12.
Each argument must be evaluated as a number, otherwise, the #VALUE! error is displayed. The Periods argument is always turned into an integer by truncating the decimal places. If Nominal_Interest is less than or equal to zero, or Periods is less than 1, EFFECT() returns the #NUMBER! error.
Background. For various financial transactions (such as mortgage loans, building-and-loan savings, interest for certain checking accounts, current accounts, and overdraft credits), an annual interest rate is specified, but it is only used to prepare further modalities. This means that interest is paid in intra-annual periods, not annually. The interest rate used is determined by dividing the nominal interest rate by the number of periods.
To be able to compare different conditions, the interest rate that returns the same result as the intra-annual interest yield for a fixed amount with a one-time interest payment is called the effective annual interest rate. The following relationship exists between the two interest rates:
Examples. The following examples illustrate how to use this function.
Saving. Assume that an investor has an account with monthly interest payments and has deposited $1,000.00 at the beginning of the year. The given annual nominal interest rate is 5 percent. How high is the balance at the end of the year, and what is the effective interest yield?
To answer this question, you can create an account that is credited a monthly interest (make sure to round it correctly to two decimal places after the period). The account balance at the end of the year is $1,051.16.
You get to the same result if you use the FV() function with the corresponding arguments:
=FV(5%/12,12,,-1000)
If you create a ratio between the final account balance and the capital used, you come up with an interest yield of 5.12 percent. This is exactly what =EFFECT(5%,12) calculates as well.
Mortgage Loans. A bank advertises a mortgage loan that has a nominal interest (debit interest) of 2.42 percent and promises an (initial) effective annual interest rate of 2.45 percent over five years with a monthly repayment and complete loan disbursement (without disagio).
In this case, you can recreate the effective annual interest rate with EFFECT(), but using this function is not always the way the bank determines its value. The fact that the number is the same down to the two decimal places after the period may be just a coincidence.