Top 4 Most Important Financial Excel Formulas Explained

The Best Excel Financial Functions

4. The Effect Function

The Microsoft Excel PMT function returns the payment amount for a loan based on an interest rate and a constant payment schedule.

Syntax

The syntax for the Microsoft Excel PMT function is:

=EFFECT( nominal_rate, npery )

Parameters or Arguments

  • nominal_rate : The interest rate for the year.
  • npery The number : of compounding periods per year.

Where the nominal_rate argument is a numerical value, between 0 and 1, which represents the nominal interest rate, and the npery argument is a positive integer that gives the number of compounding periods per year.

Effective Annual Interest Rate The Effective Annual Interest Rate is a measure of interest, that incorporates the compounding of interest and is frequently used to compare financial loans with different compounding terms.

The effective annual interest rate is calculated using the following equation:

EFFECT Equation

where nominal_interest is the nominal interest rate and npery is the number of compounding periods per year

Excel Effect Function Example

Column A of the spreadsheet below, shows examples of the Excel Effect Function, used to calculate the effective annual interest rate for 3 different nominal interest rates and numbers of compounding periods.

The formulas are shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.

Formulas:
A
1 =EFFECT( 10%, 4 )
2 =EFFECT( 10%, 2 )
3 =EFFECT( 2.5%, 2 )
Results:
A
1 10.38%
2 10.25%
3 2.52%

Common Errors

If you get an error from the Excel Effect function this is likely to be one of the following :

Common Errors
#NUM! – Occurs if the nominal_rate argument is ≤ 0 or > 1, or if the npery argument is < 1
#VALUE! – Occurs if either of the supplied arguments is non-numeric
#NAME? – Occurs when Analysis ToolPak add-in is not enabled in your Excel. You will need to enable the Analysis ToolPak if you wish to use the Excel Effect Function.— Hide How to Do This in Excel 2003:

  • From the Tools drop-down menu, select the option Add-Ins …
  • An ‘Add-Ins’ window will pop up. From this, select the option Analysis ToolPak and click OK

— Hide How to Do This in Excel 2007:

  • Click the Microsoft button on the top left of your spreadsheet and select the Excel Options button
  • From the menu on the left hand side, select Add-Ins
  • In the ‘Manage:’ box, select Excel Add-ins and click Go…
  • An ‘Add-Ins’ window will pop up. From this, select the option Analysis ToolPak and click OK

— Hide How to Do This in Excel 2010 or Excel 2013:

  • Click the File tab (top left of your spreadsheet) and select Options
  • From the menu on the left hand side, select Add-Ins
  • If the window that pops up doesn’t show the ‘Add-ins’ list, use the ‘Manage:’ drop-down menu (at the bottom of the window) to select Excel Add-ins. Click Go…
  • From the ‘Add-Ins’ window, select the option Analysis ToolPak and click OK
 

Formatting the Result of the Excel Effect Function

If your function result is presented as a decimal, or shows 0%, both of these problems are likely to be due to the formatting of the cell containing the function.

Therefore the problem can be fixed by formatting the cell as a percentage, with decimal places :

  • Use the mouse to right click on the cell containing the function
  • Select the Format Cells … option and ensure the Number tab is selected
  • Under the Category heading, make sure the option Percentage is selected, and in the box to the right (entitled Decimal Places), change the value to 2 (or more).
  • Click OK
REMARKS
  • In Excel 2007 (and later) this function has been removed from the Analysis ToolPak add-in and is available as standard.
  • In Excel 2003 this function was only available when you had the Analysis ToolPak add-in loaded.
  • In Excel 2007 the accuracy of this function has been improved because the exponent is now calculated using internal routines.
  • This function is closely related to the NOMINAL function.
  • An effective interest rate is an interest rate that is compounded annually making it easy to compare different interest rates.
  • The Effective Rate is also known as the Annual Rate, Effective Annual Rate (EAR) or Annual Equivalent Rate (AER).
  • If any of the arguments are not numeric, then #VALUE! is returned.
  • The “nominal rate” can either be entered with a percentage sign or as a decimal.
  • If “nominal_rate” = 0, then #NUM! is returned.
  • If “npery” is not an integer, it is truncated.
  • If “npery” < 1, then #NUM! is returned.
  • Example 1 – If you have an nominal interest rate of 5% compounded annually, what is the effective interest rate.
  • Example 2 – This is the same as Example 1.
  • Example 3 – If you have an nominal interest rate of 5% compounded semi-annually, what is the effective interest rate.
  • Example 4 – If you have an nominal interest rate of 5% compounded monthly, what is the effective interest rate.
  • Example 5 – If you have an nominal interest rate of 5% compounded daily, what is the effective interest rate.
  • Example 6 – where “nominal” > 1
  • Example 7 – what if npery < 0
  • Example 8 – what if “nominal_rate” is not numeric
  • Example 9 – what if “npery” is not numeric

 

EXAMPLES
A
1 =EFFECT(5/100,1) = 5.000%
2 =EFFECT(5%,1) = 5.000%
3 =EFFECT(5%,2) = 5.062%
4 =EFFECT(5%,12) = 5.116%
5 =EFFECT(5%,365) = 5.127%
6 =EFFECT(5,2) = 1125.000%
7 =EFFECT(5%,-2) = #NUM!
8 =EFFECT(“some text”,2) = #VALUE!
9 =EFFECT(5%,”some text”) = #VALUE!

Go To

Also Read:

Courtesy: gobankingrates.com , techonthenet.com

Pages: 1 2 3 4 5
Tagged:

Leave a Reply

Your email address will not be published. Required fields are marked *

four + 17 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.