Difference between revisions of "Documentation/How Tos/Calc: IPMT function"

From Apache OpenOffice Wiki
Jump to: navigation, search
m (→‎Syntax:)
(10 intermediate revisions by 4 users not shown)
Line 1: Line 1:
−
__NOTOC__
+
{{Documentation/MasterTOC
 +
|bookid=1234'''
 +
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
 +
|ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Financial_functions|<div style="font-size: 140%;">Financial Functions]]
 +
|ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_IRR_function
 +
|ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_FVSCHEDULE_function
 +
|ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Date_%26_Time_functions
 +
|ShowNextPart=block|NextPart=Documentation/How_Tos/Calc:_Information_functions
 +
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>
 +
 
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Depreciation</div>
 +
* [[Documentation/How_Tos/Calc:_AMORDEGRC_function|<div style="font-size: 120%;">Amordegrc]]
 +
* [[Documentation/How_Tos/Calc:_AMORLINC_function|<div style="font-size: 120%;">Amorlinc]]
 +
* [[Documentation/How_Tos/Calc:_DB_function|<div style="font-size: 120%;">Db]]
 +
* [[Documentation/How_Tos/Calc:_DDB_function|<div style="font-size: 120%;">Ddb]]
 +
* [[Documentation/How_Tos/Calc:_SLN_function|<div style="font-size: 120%;">Sln]]
 +
* [[Documentation/How_Tos/Calc:_SYD_function|<div style="font-size: 120%;">Syd]]
 +
* [[Documentation/How_Tos/Calc:_VDB_function|<div style="font-size: 120%;">Vdb]]
 +
 
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Payment Streams, Annuities, Loans</div>
 +
* [[Documentation/How_Tos/Calc:_CUMIPMT_function|<div style="font-size: 120%;">Cumipmt]]
 +
* [[Documentation/How_Tos/Calc:_CUMIPMT_ADD_function|<div style="font-size: 120%;">Cumipmt Add]]
 +
* [[Documentation/How_Tos/Calc:_CUMPRINC_function|<div style="font-size: 120%;">Cumprinc]]
 +
* [[Documentation/How_Tos/Calc:_CUMPRINC_ADD_function|<div style="font-size: 120%;">Cumpinc Add]]
 +
* [[Documentation/How_Tos/Calc:_FV_function|<div style="font-size: 120%;">Fv]]
 +
* [[Documentation/How_Tos/Calc:_FVSCHEDULE_function|<div style="font-size: 120%;">Fvschedule]]
 +
* [[Documentation/How_Tos/Calc:_IPMT_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Ipmt]]
 +
* [[Documentation/How_Tos/Calc:_IRR_function|<div style="font-size: 120%;">Irr]]
 +
* [[Documentation/How_Tos/Calc:_ISPMT_function|<div style="font-size: 120%;">Ispmt]]
 +
* [[Documentation/How_Tos/Calc:_MIRR_function|<div style="font-size: 120%;">Mirr]]
 +
* [[Documentation/How_Tos/Calc:_NPER_function|<div style="font-size: 120%;">Nper]]
 +
* [[Documentation/How_Tos/Calc:_NPV_function|<div style="font-size: 120%;">Npv]]
 +
* [[Documentation/How_Tos/Calc:_PMT_function|<div style="font-size: 120%;">Pmt]]
 +
* [[Documentation/How_Tos/Calc:_PPMT_function|<div style="font-size: 120%;">Ppmt]]
 +
* [[Documentation/How_Tos/Calc:_PV_function|<div style="font-size: 120%;">Pv]]
 +
* [[Documentation/How_Tos/Calc:_RATE_function|<div style="font-size: 120%;">Rate]]
 +
* [[Documentation/How_Tos/Calc:_RRI_function|<div style="font-size: 120%;">Rri]]
 +
* [[Documentation/How_Tos/Calc:_XIRR_function|<div style="font-size: 120%;">Xirr]]
 +
* [[Documentation/How_Tos/Calc:_XNPV_function|<div style="font-size: 120%;">Xnpv]]
 +
 
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Securities</div>
 +
* [[Documentation/How_Tos/Calc:_ACCRINT_function|<div style="font-size: 120%;">Accrint]]
 +
* [[Documentation/How_Tos/Calc:_ACCRINTM_function|<div style="font-size: 120%;">Accrintm]]
 +
* [[Documentation/How_Tos/Calc:_DISC_function|<div style="font-size: 120%;">Disc]]
 +
* [[Documentation/How_Tos/Calc:_DURATION_function|<div style="font-size: 120%;">Duration]]
 +
* [[Documentation/How_Tos/Calc:_DURATION_ADD_function|<div style="font-size: 120%;">Duration Add]]
 +
* [[Documentation/How_Tos/Calc:_EFFECTIVE_function|<div style="font-size: 120%;">Effective]]
 +
* [[Documentation/How_Tos/Calc:_EFFECT_ADD_function|<div style="font-size: 120%;">Effect Add]]
 +
* [[Documentation/How_Tos/Calc:_INTRATE_function|<div style="font-size: 120%;">Intrate]]
 +
* [[Documentation/How_Tos/Calc:_MDURATION_function|<div style="font-size: 120%;">Mduration]]
 +
* [[Documentation/How_Tos/Calc:_DURATION_ADD_function|<div style="font-size: 120%;">Duration Add]]
 +
* [[Documentation/How_Tos/Calc:_NOMINAL_function|<div style="font-size: 120%;">Nominal]]
 +
* [[Documentation/How_Tos/Calc:_NOMINAL_ADD_function|<div style="font-size: 120%;">Nominal Add]]
 +
* [[Documentation/How_Tos/Calc:_ODDFPRICE_function|<div style="font-size: 120%;">Oddfprice]]
 +
* [[Documentation/How_Tos/Calc:_ODDFYIELD_function|<div style="font-size: 120%;">Oddfyield]]
 +
 
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Coupons</div>
 +
* [[Documentation/How_Tos/Calc:_COUPDAYBS_function|<div style="font-size: 120%;">Coupdaybs]]
 +
* [[Documentation/How_Tos/Calc:_COUPDAYS_function|<div style="font-size: 120%;">Coupdays]]
 +
* [[Documentation/How_Tos/Calc:_COUPDAYSNC_function|<div style="font-size: 120%;">Coupdaysnc]]
 +
* [[Documentation/How_Tos/Calc:_COUPNCD_function|<div style="font-size: 120%;">Coupncd]]
 +
* [[Documentation/How_Tos/Calc:_COUPNUM_function|<div style="font-size: 120%;">Coupnum]]
 +
* [[Documentation/How_Tos/Calc:_COUPPCD_function|<div style="font-size: 120%;">Couppcd]]
 +
 
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Miscellaneous</div>
 +
 
 +
* [[Documentation/How_Tos/Calc:_DOLLARDE_function|<div style="font-size: 120%;">Coupdaybs]]
 +
* [[Documentation/How_Tos/Calc:_DOLLARFR_function|<div style="font-size: 120%;">Coupdays]]
 +
}}__TOC__
  
 
== IPMT ==
 
== IPMT ==
−
Returns the interest paid in a period for a fixed rate loan.
+
Returns the portion of the periodic payment which is interest for a fixed rate loan or annuity.
  
 
=== Syntax: ===
 
=== Syntax: ===
−
<tt>'''IPMT(rate; period; numperiods; principal; finalbalance; type)'''</tt>
+
<tt>'''IPMT(rate; period; numperiods; presentvalue; futurevalue; type)'''</tt>
−
: <tt>'''rate'''</tt>: the interest rate per period.
+
: <tt>'''rate'''</tt>: the interest rate, per period.
−
: <tt>'''period'''</tt>: the period for which interest is to be calculated.
+
: <tt>'''period'''</tt>: the period of the payment whose interest portion is to be calculated, numbered from 1.
 
: <tt>'''numperiods'''</tt>: the total number of payment periods in the term.
 
: <tt>'''numperiods'''</tt>: the total number of payment periods in the term.
−
: <tt>'''principal'''</tt>: the initial sum borrowed.
+
: <tt>'''presentvalue'''</tt>: the initial sum borrowed or invested.
−
: <tt>'''finalbalance'''</tt>: 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.
+
: <tt>'''futurevalue'''</tt>: 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.
 
: <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.
Line 18: Line 86:
 
: With a fixed rate loan, where you make a constant payment each period to pay off the loan over the term, some of each period payment is interest on the outstanding capital, and some is a repayment of capital. Over time (as you pay off capital), the interest becomes less and the capital repayment becomes more.
 
: With a fixed rate loan, where you make a constant payment each period to pay off the loan over the term, some of each period payment is interest on the outstanding capital, and some is a repayment of capital. Over time (as you pay off capital), the interest becomes less and the capital repayment becomes more.
  
−
: <tt>'''IPMT'''</tt> returns the interest paid in the specified period. <tt>'''PPMT'''</tt> returns the capital repaid in that period. Together they add up to the periodic payment, given by <tt>'''PMT'''</tt>.
+
: <tt>'''IPMT'''</tt> returns the interest in the payment of a specified period. <tt>'''PPMT'''</tt> returns the capital repaid in the payment of that period. Together they add up to the actual payment, given by <tt>'''PMT'''</tt>.
 +
 
 +
: When payments are made at the end of each period, the interest arises during that period.
 +
 
 +
: When payments are made at the start of each period, the interest arises <u>during the preceding period</u>.
 +
 
 +
: By convention, money that you receive is positive, and money you pay is negative. For a loan where you receive a lump sum at the start, <tt>'''presentvalue'''</tt> is positive. For an investment where you pay a lump sum at the start, <tt>'''presentvalue'''</tt> is negative.
  
 
=== Example: ===
 
=== Example: ===
Line 24: Line 98:
 
:  returns <tt>'''-12.72'''</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. In the 12<sup>th</sup> month you make your usual monthly repayment, of which 12.72 is interest.
 
:  returns <tt>'''-12.72'''</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. In the 12<sup>th</sup> month you make your usual monthly repayment, of which 12.72 is interest.
  
−
=== See also: ===
+
=== Issues: ===
−
[[Documentation/How_Tos/Calc: CUMIPMT function|'''CUMIPMT''']],
+
* '''IPMT''' formats the result as currency if the cell has default formatting. It thus displays a real currency amount. The amount returned by '''IPMT''' 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).
−
[[Documentation/How_Tos/Calc: CUMIPMT_ADD function|'''CUMIPMT_ADD''']],
+
* Excel claims that this function calculates the "interest payment for a given period". This seems to be wrong - it is the interest in the <u>payment</u> for that period. Calc and Excel produce the same results. Gnumeric returns slightly different results when payment is at the start of each period; possibly it calculates what Excel says it calculates.
−
[[Documentation/How_Tos/Calc: PMT function|'''PMT''']],
+
−
[[Documentation/How_Tos/Calc: PPMT function|'''PPMT''']],
+
−
[[Documentation/How_Tos/Calc: CUMPRINC function|'''CUMPRINC''']],
+
−
[[Documentation/How_Tos/Calc: CUMPRINC_ADD function|'''CUMPRINC_ADD''']]
+
  
−
'''[[Documentation/How_Tos/Calc: Derivation of Financial Formulas|Derivation of Financial Formulas]]'''
+
{{SeeAlso|EN|
 +
* [[Documentation/How_Tos/Calc: CUMIPMT function|CUMIPMT]]
 +
* [[Documentation/How_Tos/Calc: CUMIPMT_ADD function|CUMIPMT_ADD]]
 +
* [[Documentation/How_Tos/Calc: PMT function|PMT]]
 +
* [[Documentation/How_Tos/Calc: PPMT function|PPMT]]
 +
* [[Documentation/How_Tos/Calc: CUMPRINC function|CUMPRINC]]
 +
* [[Documentation/How_Tos/Calc: CUMPRINC_ADD function|CUMPRINC_ADD]]
  
−
[[Documentation/How_Tos/Calc: Financial functions|'''Financial functions''']]
+
* [[Documentation/How_Tos/Calc: ISPMT function|ISPMT]]
  
−
=== Issues: ===
+
* [[Documentation/How_Tos/Calc: Derivation of Financial Formulas|Derivation of Financial Formulas]]
−
* '''IPMT''' formats the result as currency if the cell has default formatting. It thus displays a real currency amount. The amount returned by '''IPMT''' 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).
+
 
 +
* [[Documentation/How_Tos/Calc: Financial functions|Financial functions]]
 +
 
 +
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
 +
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
 +
[[Category: Documentation/Reference/Calc/Financial functions]]

Revision as of 09:35, 17 July 2018


IPMT

Returns the portion of the periodic payment which is interest for a fixed rate loan or annuity.

Syntax:

IPMT(rate; period; numperiods; presentvalue; futurevalue; type)

rate: the interest rate, per period.
period: the period of the payment whose interest portion is to be calculated, numbered from 1.
numperiods: the total number of payment periods in the term.
presentvalue: the initial sum borrowed or invested.
futurevalue: 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).


With a fixed rate loan, where you make a constant payment each period to pay off the loan over the term, some of each period payment is interest on the outstanding capital, and some is a repayment of capital. Over time (as you pay off capital), the interest becomes less and the capital repayment becomes more.
IPMT returns the interest in the payment of a specified period. PPMT returns the capital repaid in the payment of that period. Together they add up to the actual payment, given by PMT.
When payments are made at the end of each period, the interest arises during that period.
When payments are made at the start of each period, the interest arises during the preceding period.
By convention, money that you receive is positive, and money you pay is negative. For a loan where you receive a lump sum at the start, presentvalue is positive. For an investment where you pay a lump sum at the start, presentvalue is negative.

Example:

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

returns -12.72 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. In the 12th month you make your usual monthly repayment, of which 12.72 is interest.

Issues:

  • IPMT formats the result as currency if the cell has default formatting. It thus displays a real currency amount. The amount returned by IPMT 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).
  • Excel claims that this function calculates the "interest payment for a given period". This seems to be wrong - it is the interest in the payment for that period. Calc and Excel produce the same results. Gnumeric returns slightly different results when payment is at the start of each period; possibly it calculates what Excel says it calculates.


See Also
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_IPMT_function&oldid=243608"
Views
Personal tools
Navigation
Tools