XL: Examples of Interest Calculations Outside the United States (294396)

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: That is 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: That is 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.