Jump to content

PMT function

Frank Wade

This topic is 5729 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5729 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.