Another slightly geeky post from me 😊 about the PMT, PPMT and similar functions. It is very easy to use the functions in ways that can give incorrect answers. Incorrect answers are also quite easy to overlook in practice, not least as the functions are often embedded within compound calculations or within error-checking statements, such as “IFERROR(PPMT(…),0)”. This blog points out a couple of frequent errors that may arise.
The PMT function has the default parameters PMT(rate,nper,pv,[fv],[type]), and returns the constant-per-period payment required to repay a loan of amount “pv” over “nper” periods, where the periodic interest rate is “rate”. The optional arguments are the future value of the loan “fv”, and the timing type (payments made at the end or beginning of the periods). It is worth noting that that if the interest rate is set to 0%, then the PMT function effectively shows a straight-line amortisation of the principal value. This can be a useful cross-check method. The PPMT function calculates only the principal part of the repayment within each period and is different in each period (when interest rates are not zero).
In terms of mistakes that are easy to make when using these functions:
First, the functions by default return negative values. For example, a loan of $1200 to be completely repaid over 12 periods with an interest rate of 0% would have a PMT of -$100, rather than (the perhaps expected value) +$100. This can be overlooked when the function is embedded within other calculations.
Second, a more subtly, the optional fv may need to have opposite sign to the mandatory pv, even though one may have not expressed this in the Excel entry cells or model calculations. For example, if a loan of $1400 is to be paid down to $200 over 12 periods when the interest rate is zero, the periodic amount to pay down is $100. One might expect the function PMT(0%, 12, 1400, 200) to return -$100. However, the return value is -$133.33. The correct value is returned by using PMT(0%, 12, 1400, -200). This is overlooked in many texts, which provide examples of the function, as they very often do not use the fv argument (so that it is implicitly zero, and therefore its sign is not important). However, the issue becomes clear if a non-zero value is used and the formula checked with simple values – something that is often hard in practice as the inputs are often themselves items that cannot be manually varied, and the function may be embedded in more complex calculations that mask incorrect formulae (e.g. within Min, MAX or IF statements).