Difference between revisions of "Documentation/How Tos/Calc: PMT function"
From Apache OpenOffice Wiki
< Documentation | How Tos
(→Issues:) |
|||
Line 12: | Line 12: | ||
: <tt>'''type'''</tt>: when payments are made (optional - defaults to 0): | : <tt>'''type'''</tt>: when payments are made (optional - defaults to 0): | ||
:: 0 - at the end of each period. | :: 0 - at the end of each period. | ||
− | :: 1 - at the start 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. | + | : See the examples for how this function can be used for building up savings with fixed regular payments. |
=== Example: === | === Example: === | ||
Line 22: | Line 22: | ||
<tt>'''PMT(5%/12; 12*2; 0; 1000; 1)'''</tt> | <tt>'''PMT(5%/12; 12*2; 0; 1000; 1)'''</tt> | ||
: returns <tt>'''-39.54'''</tt> 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. | : returns <tt>'''-39.54'''</tt> 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. | ||
+ | |||
+ | <tt>'''PMT(5.5%/12; 12*2; 5000; 1000; 0)'''</tt> | ||
+ | : returns <tt>'''-259.99'''</tt> 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. You pay 259.99 currency units each month; it is given as negative because you pay it. | ||
=== See also: === | === See also: === | ||
Line 37: | Line 40: | ||
=== Issues: === | === 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). | * '''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 <tt>'''rate'''</tt> values, Calc (in common with Excel) may produce an error. This has no impact in a real world calculation. See [http://www.openoffice.org/issues/show_bug.cgi?id=91870 Issue 91870]. | * With tiny <tt>'''rate'''</tt> values, Calc (in common with Excel) may produce an error. This has no impact in a real world calculation. See [http://www.openoffice.org/issues/show_bug.cgi?id=91870 Issue 91870]. |
Revision as of 18:27, 20 July 2008
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. You pay 259.99 currency units each month; it is given as negative because you pay it.
See also:
CUMIPMT, CUMIPMT_ADD, IPMT, PPMT, CUMPRINC, CUMPRINC_ADD
Derivation of Financial Formulas
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.