PMT function

[Frank Wade]

The help notes for the PMT function in all FM versions advise that to find the monthly interest rate all you have to do is divide the annual interest rate by 12.

This is incorrect. MS Excel also advise to the do the same calculation.

The correct calculation is as follows:

Monthly interest rate = (( 1 + annual interest rate /100)^ (1/12) -1) x 100

The simple method of dividing the annual interest rate by 12 would only be applicable if the interest was simple interest and not compound interest. Most loan repayments today are based on compound interest rates.

FileMaker Inc. may need to update their help notes. Please note Microsoft need to do the same in Excel.

I hope that the monthly interest rate formula is of value to other members.

Edited March 24, 2006 by Guest

[comment]

This subject is a bit more complex than you make it appear.

Normally rates are given as NOMINAL. To fully understand the financial impact of a given nominal rate, it must be accompanied by compounding terms, e.g. "5.25% per year, compounded quarterly".

To use a nominal annual interest rate in a financial function such as PV or FV, you must divide it by the number of compounding periods per year. Both Filemaker and Excel are perfectly correct in this. They do NOT assume simple interest. They expect you to input the nominal rate - as this is the accepted convention.

Sometimes, an EFFECTIVE rate is quoted. The effective rate is a way to express the annual financial impact of the loan in a single number. If you have been quoted an effective annual rate, you need to convert it into nominal before you can use it in a financial function. That is what your formula attempts to do. However, you assume the compounding period is always a month. If this assumption is wrong (for example, if the loan is compounded quarterly), your results will be incorrect.

The correct formula to convert an effective rate into nominal is:

npery * ( ( 1 + effect_rate ) ^ ( 1 / npery ) - 1 )

where effect_rate is the effective annual interest rate and npery is the number of compounding periods per year. This is similar to the NOMINAL() function in Excel.