8th April 2020

I have previously posted about the need to be very careful when using the PMT and similar functions, as mistakes can easily arise. One area of complexity that I didn’t cover earlier was the definition of the interest rate, and particularly the link to compounding.

The function dialogue describes the Rate parameter as “the interest rate per period of the loan. For example, use 6%./4 for quarterly payment at 6% APR”.

This reference to APR is confusing and generally wrong. The function is implicitly assuming that the periodic interest is compounded at the end of every period. But a loan with a “stated rate” of 6% p.a. that is compounded quarterly would have an APR of around 6.14% (i.e. 1.015^4 -1). Thus, if one wishes to use an interest rate as an input to this function, but where this interest rate is defined with reference to a period length (e.g. annual) that is not the same as the length of the periods for loan repayments (e.g. monthly), then two things need to be done. First, one must assume that the interest is compounded at the end of every loan period (in general this might not be valid e.g. if one were to use the function to try to calculate daily payment requirements for a loan that is compounded only monthly). Second, one would apply the relevant time conversion formula to find the equivalent loan-period rate from the stated annual rate. For example, if the stated rate is 12% p.a. and the loan period is monthly, then on the assumption that interest is compounded monthly, the periodic rate is 1.12^(1/12)-1 or approximately 9.5%.

The complexity of this function with respect to “Rate” is therefore due to the incorrect example within the Help menu, coupled with the subtleties of interest compounding.