Difference between revisions of "Manuals/calci/CUMIPMT"

From ZCubes Wiki
Jump to navigation Jump to search
(Created page with "= CUMIPMT(Rate,NoPaymentPeriods,PresentValue,StartPeriod,EndPeriod,Type) = *Where <math>Rate</math> is the interest rate, per peroid, *<math>NoPaymentPeriods</math> is the nu...")
 
Line 4: Line 4:
 
*<math>NoPaymentPeriods</math> is the number of periods over which the loan or investment is to be paid,  
 
*<math>NoPaymentPeriods</math> is the number of periods over which the loan or investment is to be paid,  
 
*<math>PresentValue</math> is the present value of loan or investment,
 
*<math>PresentValue</math> is the present value of loan or investment,
−
*<math>StartPeriod</math> is the number of the first period over which the interest is to be calculated (must be an integer between 1 and <math>NoPaymentPeriods</math>)
+
*<math>StartPeriod</math> is the number of the first period over which the interest is to be calculated,
−
*<math>EndPeriod</math> is the number of the last period over which the interest is to be calculated (must be an integer between 1 and <math>NoPaymentPeriods</math>)
+
*<math>EndPeriod</math> is the number of the last period over which the interest is to be calculated,
−
*<math>Type</math> is specifies whether the payment is made at the start or end of the period.
+
*<math>Type</math> specifies whether the payment is made at the start or end of the period.
  
 
CUMIPMT() calculates the cumulative interest paid on a loan or an investment, between two specific periods.
 
CUMIPMT() calculates the cumulative interest paid on a loan or an investment, between two specific periods.
Line 13: Line 13:
  
 
CUMIPMT(Rate,NoPaymentPeriods,PresentValue,StartPeriod,EndPeriod,Type)
 
CUMIPMT(Rate,NoPaymentPeriods,PresentValue,StartPeriod,EndPeriod,Type)
 +
 +
* <math>StartPeriod</math> must be an integer between 1 and <math>NoPaymentPeriods</math>.
 +
 +
* <math>EndPeriod</math> must be an integer between 1 and <math>NoPaymentPeriods</math>.
  
 
*<math>StartPeriod</math> should be smaller than <math>EndPeriod</math>. Else Calci displays #NUM !error message.
 
*<math>StartPeriod</math> should be smaller than <math>EndPeriod</math>. Else Calci displays #NUM !error message.
 +
 
*If <math>StartPeriod</math> or <math>EndPeriod</math> is <=0, then Calci displays #NUM !error message.
 
*If <math>StartPeriod</math> or <math>EndPeriod</math> is <=0, then Calci displays #NUM !error message.
 +
 
*If <math>StartPeriod</math> or <math>EndPeriod</math> is > <math>NoPaymentPeriods</math>, then Calci displays #NUM !error message.
 
*If <math>StartPeriod</math> or <math>EndPeriod</math> is > <math>NoPaymentPeriods</math>, then Calci displays #NUM !error message.
 +
 
*<math>Type</math> value is an integer value (either 0 or 1).
 
*<math>Type</math> value is an integer value (either 0 or 1).
−
'''0''' indicates the payment is made at the end of the period;
+
'''0''' indicates the payment is made at the end of the period; <br/>
 
'''1''' indicates the payment is made at the start of the period.
 
'''1''' indicates the payment is made at the start of the period.
 +
 
*If <math>Type</math> value is other than 0 or 1, Calci displays #N/A error message.
 
*If <math>Type</math> value is other than 0 or 1, Calci displays #N/A error message.
  
 
== Examples ==
 
== Examples ==
  
−
Consider the following example that shows the use of COUPNUM function:
+
Consider the following example that shows the use of CUMIPMT function:
 +
 
 +
Cumulative interest during each year of a loan of $30,000 that is to be paid off over 4 years, with an interest rate of 4% per year (payment is made at the end of each month).
 +
 
 +
*The payments are made monthly, so we have converted the annual interest rate of 4% into a monthly rate (=4%/12).
 +
 
 +
*The number of years into months (=4*12).
 +
 
 +
 
 +
'''Formulas:'''
  
 
<div id="2SpaceContent" class="zcontent" align="left">
 
<div id="2SpaceContent" class="zcontent" align="left">
Line 30: Line 47:
 
{| id="TABLE3" class="SpreadSheet blue"
 
{| id="TABLE3" class="SpreadSheet blue"
 
|- class="even"
 
|- class="even"
−
| class=" " |  
+
| class=" " | A
 +
| class=" " | B
 +
| class=" " |
 +
 
 +
|- class="odd"
 +
| class="sshl_f" | Year 1
 +
| class=" " | =CUMIPMT(4%/12,48,30000,1,12,0)
 
| class=" " |
 
| class=" " |
 +
 +
|- class="even"
 +
| class=" " | Year 2
 +
| class=" " | =CUMIPMT(4%/12,48,30000,13,24,0)
 
| class=" " |
 
| class=" " |
  
 
|- class="odd"
 
|- class="odd"
−
| class="sshl_f" |  
+
| class=" " | Year 3
 +
| class=" " | =CUMIPMT(4%/12,48,30000,25,36,0)
 
| class=" " |
 
| class=" " |
 +
 +
|- class="even"
 +
| class=" " | Year 4
 +
| class=" " | =CUMIPMT(4%/12,48,30000,37,48,0)
 
| class=" " |
 
| class=" " |
 +
|}
 +
 +
 +
'''Results:'''
 +
 +
<div id="2SpaceContent" class="zcontent" align="left">
  
 +
{| id="TABLE3" class="SpreadSheet blue"
 
|- class="even"
 
|- class="even"
−
| class=" " |  
+
| class=" " | A
 +
| class=" " | B
 
| class=" " |
 
| class=" " |
 +
 +
|- class="odd"
 +
| class="sshl_f" | Year 1
 +
| class=" " | $-1,071.56
 +
| class=" " |
 +
 +
|- class="even"
 +
| class=" " | Year 2
 +
| class=" " | $-784.05
 
| class=" " |
 
| class=" " |
  
 
|- class="odd"
 
|- class="odd"
−
| class=" " |  
+
| class=" " | Year 3
 +
| class=" " | $-484.82
 
| class=" " |
 
| class=" " |
 +
 +
|- class="even"
 +
| class=" " | Year 4
 +
| class=" " | $-173.41
 
| class=" " |
 
| class=" " |
 
|}
 
|}
  
−
=CUMIPMT() ''displays '' as a result.
+
*The calculated interest payments are negative values, as they represents outgoing payments (for the individual taking out the loan).
−
=CUMIPMT() ''displays '' as a result.
 
  
 
==Related Videos==
 
==Related Videos==

Revision as of 20:03, 16 March 2017

CUMIPMT(Rate,NoPaymentPeriods,PresentValue,StartPeriod,EndPeriod,Type)

  • Where is the interest rate, per peroid,
  • is the number of periods over which the loan or investment is to be paid,
  • is the present value of loan or investment,
  • is the number of the first period over which the interest is to be calculated,
  • is the number of the last period over which the interest is to be calculated,
  • specifies whether the payment is made at the start or end of the period.

CUMIPMT() calculates the cumulative interest paid on a loan or an investment, between two specific periods.

Description

CUMIPMT(Rate,NoPaymentPeriods,PresentValue,StartPeriod,EndPeriod,Type)

  • must be an integer between 1 and .
  • must be an integer between 1 and .
  • should be smaller than . Else Calci displays #NUM !error message.
  • If or is <=0, then Calci displays #NUM !error message.
  • If or is > , then Calci displays #NUM !error message.
  • value is an integer value (either 0 or 1).

0 indicates the payment is made at the end of the period;
1 indicates the payment is made at the start of the period.

  • If value is other than 0 or 1, Calci displays #N/A error message.

Examples

Consider the following example that shows the use of CUMIPMT function:

Cumulative interest during each year of a loan of $30,000 that is to be paid off over 4 years, with an interest rate of 4% per year (payment is made at the end of each month).

  • The payments are made monthly, so we have converted the annual interest rate of 4% into a monthly rate (=4%/12).
  • The number of years into months (=4*12).


Formulas:

A B
Year 1 =CUMIPMT(4%/12,48,30000,1,12,0)
Year 2 =CUMIPMT(4%/12,48,30000,13,24,0)
Year 3 =CUMIPMT(4%/12,48,30000,25,36,0)
Year 4 =CUMIPMT(4%/12,48,30000,37,48,0)


Results:

A B
Year 1 $-1,071.56
Year 2 $-784.05
Year 3 $-484.82
Year 4 $-173.41
  • The calculated interest payments are negative values, as they represents outgoing payments (for the individual taking out the loan).

Related Videos

CUMPRINC and CUMIPMT

See Also

References


Retrieved from "http://wiki.zcubes.com/index.php?title=Manuals/calci/CUMIPMT&oldid=207515"