Documentation/How Tos/Calc: PMT function
From Apache OpenOffice Wiki
< Documentation | How Tos
CALC FUNCTIONS
Depreciation
Payment Streams, Annuities, Loans
Securities
Coupons
Miscellaneous
Contents
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).
- Prior to OOo3.4, Calc could produce an error with tiny rate values. This had no impact in a real world calculation. Excel also had, and may still have, this problem. See Issue 91870.
See Also