How to use the EFFECT function
What is the effect function?
The EFFECT function calculates the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.
What is the effective annual interest rate?
The effective annual interest rate is the actual annual interest rate earned on an investment after accounting for compounding frequency.
What is the nominal annual interest rate?
The nominal annual interest rate, also known as the stated annual interest rate, is the rate of interest quoted on an investment or loan without accounting for compounding.
It does not consider compounding periods within the year, often stated as "per annum" or "per year". If interest compounds during the year, the effective annual rate will differ from the nominal rate.
What is the number of compounding periods per year?
The number of compounding periods per year refers to how often interest is compounded annually on an investment or loan.
Some common compounding periods:
- Annually - 1 compounding period per year
- Semiannually - 2 compounding periods per year
- Quarterly - 4 compounding periods per year
- Monthly - 12 compounding periods per year
- Weekly - 52 compounding periods per year
- Daily - 365 compounding periods per year
The number of compounding periods impacts the effective annual interest rate. More frequent compounding results in higher effective rates.
EFFECT function Syntax
EFFECT(nominal_rate, npery)
EFFECT function Arguments
nominal_rate | Required. The nominal interest rate. |
npery | Required. The number of compounding periods per year. |
EFFECT function example
Formula in cell D3:
How is the EFFECT function calculated?
This is how the EFFECT function is calculated:
n - nominal rate
p - number of compounding periods per year.
For example:
Annually: 10% interest compounded annually: Effective rate = 10%
Monthly: 10% interest compounded monthly:
Effective rate = (1 + 0.10/12)12Â - 1 = 10.47%
Daily: 10% interest compounded daily: Effective rate = (1 + 0.10/365)^365 - 1 = 10.52%
The effective annual rate will exceed the stated rate when compounding within a year. The effective rate translates the earned interest into the true annual return rate. It is higher than the periodic rate with frequent compounding.
Functions in 'Financial' category
The EFFECT function function is one of 29 functions in the 'Financial' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form