Documentation/How Tos/Calc: PMT function

PMT

Returns the payment per period for a fixed rate loan.

Syntax:

PMT(rate; numperiods; principal; finalbalance; type)

rate: the interest rate per period.

numperiods: the total number of payment periods in the term.

principal: the initial sum borrowed.

finalbalance: the cash balance you wish to attain at the end of the term (optional - defaults to 0). With a loan, this would normally be 0.

type: when payments are made (optional - defaults to 0):

0 - at the end of each period.

1 - at the start of each period (including a payment at the start of the term).

See the examples for how this function can be used for building up savings with fixed regular payments.

Example:

PMT(5.5%/12; 12*2; 5000; 0; 0)

returns -220.48 in currency units. You take out a 2 year loan of 5000 currency units at a yearly interest rate of 5.5%, making monthly payments at the end of the month. You pay 220.48 currency units each month; it is given as negative because you pay it.

PMT(5%/12; 12*2; 0; 1000; 1)

returns -39.54 in currency units. You wish to save 1000 currency units over 2 years, making monthly payments, beginning today. You assume the rate will remain the same at 5%. Interest is compounded monthly. If you save 39.54 currency units each month, the value at the end of 2 years will be 1000 currency units.

PMT(5.5%/12; 12*2; 5000; 1000; 0)

returns -259.99 in currency units. You take out a 2 year loan of 5000 currency units at a yearly interest rate of 5.5%, making monthly payments at the end of the month. You wish to build up a lump sum of 1000 currency units, to be paid to you at the end of the term. Interest is compounded monthly.

This is a hypothetical example, which makes the unlikely assumption that the interest you are charged when in debt is the same as the interest that you get when in credit (when building up your 1000 lump sum). It illustrates the necessary sign of each parameter: 5000 is positive, because you are paid the loan sum; 1000 is positive, because you are paid the lump sum; 259.99 is negative, because you pay the monthly sum.

Issues:

• PMT formats the result as currency if the cell has default formatting. It thus displays a real currency amount. The amount returned by PMT may still be fractional - the display rounds this to the nearest real currency. Note that your loan provider might round in a different way (for example always downwards).
• With tiny rate values, Calc (in common with Excel) may produce an error. This has no impact in a real world calculation. See Issue 91870.