Excel EFFECT Function

The Excel EFFECT function has the syntax shown below.

EFFECT(Nominal_rate, Npery)
Arguments used in the Excel EFFECT function
EFFECT = Effective Annual rate
Nominal_rate = Annual nominal rate of interest (i)
Npery = Number of compounding periods in a year (m)

Use of the Excel EFFECT Function

The Excel EFFECT function is used to calculate the effective annual rate.

The effective annual rate or EAR is the nominal rate adjusted for the number of compounding periods in a year. The effective annual rate allows for the effect of compounding, whereas the nominal annual rate does not.

Excel EFFECT Function and Semi Annual Compounding

Suppose for example, the nominal rate is 8% per year compounded every 6 months. The effective annual rate due to this compounding is given by the Excel EFFECT function as follows:

Effective annual rate = EFFECT(nominal_rate, npery)
nominal_rate = 8%
npery = 2 ( 6 month period compounding)
Effective annual rate = EFFECT(8%,2)
Effective annual rate = 8.160%

The effective annual rate is higher than the nominal rate due to the effect of compounding two times within a year.

Excel EFFECT Function and Quarterly Compounding

Using the same nominal rate compounded quarterly. The effective annual rate due to this compounding is given by the Excel EFFECT function as follows:

Nominal_rate = 8%
Npery = 4 (quarterly compounding)
Effective annual rate = EFFECT(nominal_rate, npery)
Effective annual rate = EFFECT(8%,4)
Effective annual rate = 8.243%

The effective annual rate is higher than the nominal rate due to the effect of compounding four times within a year.

Excel EFFECT Function and Monthly Compounding

Again using the same nominal annual rate as above only compounding monthly. The effective annual rate due to this compounding is given by the Excel EFFECT function as follows:

Nominal_rate = 8%
Npery = 12 (monthly compounding)
Effective annual rate = EFFECT(nominal_rate, npery)
Effective annual rate = EFFECT(8%,12)
Effective annual rate = 8.300%

The effective annual rate is higher than the nominal rate due to the effect of compounding twelve times within a year.

Excel EFFECT Function and Daily Compounding

If the compounding is now daily, assuming a 365 day year, the effective annual rate due to this compounding is given by the Excel EFFECT function as follows:

Nominal_rate = 8%
Npery = 365 (daily compounding)
Effective annual rate = EFFECT(nominal_rate, npery)
Effective annual rate = EFFECT(8%,365)
Effective annual rate = 8.328%

The effective annual rate is higher than the nominal rate due to the effect of compounding 365 times within a year.

Excel EFFECT Function and Biennial Compounding

In this case the compounding is every 2 years and the number of compounding periods in a year is 0.5. The Excel EFFECT function cannot deal with situations where the number of compounding periods is less than 1 and returns the #NUM! error.

To calculate effective annual rates in these circumstances the is used

r = nominal annual rate = 8%
m = number of compounding periods in a year = 0.5 (2 year compounding)
Effective annual rate = (1 + r / m )m - 1
Effective annual rate = (1 + 8% / 0.5 )0.5 - 1
Effective annual rate = 7.703%

In this instance, the effective annual rate is less than the nominal rate (8%) as the compounding takes place less than once per year.

Excel EFFECT Function and Annual Compounding

Finally in the special circumstance where compounding takes place annually, and the number of compounding periods is one, the effective annual rate due to this compounding is given by the Excel EFFECT function as follows:

Nominal_rate = 8%
Npery = 1 (annual compounding)
Effective annual rate = EFFECT(nominal_rate, npery)
Effective annual rate = EFFECT(8%,1)
Effective annual rate = 8.000%

As expected, as the compounding is taking place once per year, the effective rate is the same as the nominal rate.

The Excel EFFECT function, is used to convert a nominal rate, which does not allow for compounding, into an effective annual rate which does allow for the compounding effect.

As seen in the examples above, the more frequent the compounding the higher the effective rate will be. In the special situation of annual compounding, the effective annual rate and the nominal annual rate are the same.

The Excel EFFECT function is one of many Excel financial functions used in time value of money calculations, discover another at the links below.

Excel EFFECT Function November 6th, 2016Team

You May Also Like


Related pages


what are nsf checksirr functionnumber of compounding periodsdifference between capital and drawingsexcel depreciationhorizontal analysis of cash flow statementinventory turnover calculatorhow to calculate interest expense on a loanpresent value of increasing annuitybasic accounting concepts principlesimprest fundshow to calculate variable manufacturing overheadexplain debtorsstraight line depreciation schedulecalculating annuity factorclosing entries accountingtotal asset turnover ratio calculatorfreight collect fobdouble entry journal templatepercentage completion revenue recognitionwithholding tax accounting entriesdepreciation formula straight linecar loan accounting entrydefinition of return on capital employedday sales outstanding calculationdiscounted cash flow formula in exceloverheads accountingexamples of owners equitypv calculation exceldividend payable journal entryan accrued expenseshipping method fobmark up in accountingcalculator depreciationcalculating double declining balancetreatment of suspense account in balance sheetshipment fobsale of fixed asset entryquick ratio financepresent value continuous compoundingformula for quick ratiogearing definition financepurchases ledgerfuture value of an annuity due tablefreehold premisesperpetuity growth rateirr internal rate of return formulahow to calculate receivables turnoveraccounting notes payable journal entriesjournal entry for issuing common stocklcm valuationreconcile petty cashexample of a prepaid expensethe profitability indexowners equity debit or credithow to calculate gp percentagehow to calculate declining balance depreciationexplain pmt function in excelcalculate depreciation straight linensf checksaccelerated depreciation methodreceivable turnover perioddeffered tax calculationlifo examplesmanufacturing overhead calculatornote receivable vs account receivableexplanation of accounting cyclefree petty cash voucher templatehorizontal and vertical analysis of balance sheetfuture value of money formula exceldeferred tax capital allowancesdividends declared balance sheetaccounting quizesdefinition of current liabilities in accounting