Microsoft KB Archive/294396

= XL: Examples of Interest Calculations Outside the United States =

Article ID: 294396

Article Last Modified on 5/13/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q294396



SUMMARY
Microsoft financial functions presume customary financial practices in the United States. Other countries have different laws about quoting and computing interest. This article provides examples that demonstrate how to compensate for these differences when you use the financial functions.



MORE INFORMATION
Microsoft financial examples generally reference a nominal annual interest rate. When you want to apply this rate in a financial function, you must calculate an effective rate for the given period.

For example, a 12% annual rate is given, and you want a monthly payment. The effective period rate in the United States is 12% divided by 12 months, to yield a 1% monthly effective rate. Other countries may have a different monthly effective rate, depending on their laws.

Example 1: United Kingdom
In the United Kingdom, all rates are considered effective rates. A 12% annual rate does not yield a 1% monthly effective rate. Instead, the 12% annual rate requires a conversion to find the effective monthly rate. One method to calculate this is to convert the 12% effective annual rate to a nominal annual rate, and then divide by 12 to get a monthly rate. To do this, you can use the NOMINAL function:

=NOMINAL(Effective_Annual_Rate,Periods_Per_Year)

That is

=NOMINAL(12%,12)

which yields 11.3866% nominal annual interest.

If you divide 11.3866% by 12 months per year, it yields 0.9489% monthly effective interest, as opposed to the 1% effective monthly interest in the United States.

Example 2: Canada
In Canada, the computation is a bit more involved. Canadian law permits a compounding frequency of 2 per year, even though there may be 12 payments per year. When you compute the effective rate for Canada, use a frequency factor of 2 to compute effective period rate and payments, as follows:

=(1+NOMINAL/FREQUENCY)^( FREQUENCY/NUMBER_OF_PAYMENTS)-1

Where

NOMINAL = 12%

FREQUENCY = 2

NUMBER_OF_PAYMENTS =12

That is

=(1+0.12/2)^(2/12)-1 = 0.9759%

Notice that in Canada, a 12% nominal interest rate is equivalent to 0.9759% effective monthly interest, not the 1% effective monthly interest as in the United States.

Additional query words: inf XL2000

Keywords: kbinfo KB294396

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.