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 :
The Excel EFFECT function returns the effective annual interest rate for a given nominal interest rate and number of compounding periods per year.
The format of the function is :
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.
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.
Further information and examples of the Excel Effect function can be found on the Microsoft Office website.
If you get an error from the Excel Effect function this is likely to be one of the following :
|#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|
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.