Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

excel's cumipmt function 1

Status
Not open for further replies.
FALCONSEYE

FALCONSEYE

Programmer
Jul 30, 2004
1,158
US
does anybody know the equation for excel's cumipmt function ? i am trying to convert this to some coldfusion function but i cannot find any details on the equation. thanks.


ColdFusion Ninja for hire.
 
Sort by date Sort by votes
Google is your friend!


CUMIPMT(rate; numperiods; presentvalue; start; end; type)

rate: the interest rate per period.
numperiods: the total number of payment periods in the term.
presentvalue: the initial sum borrowed.
start: the first period to include. Periods are numbered beginning with 1.
end: the last period to include.
type: when payments are made:
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.
CUMIPMT returns the total interest in payments during the periods start to end inclusive - that is, the sum of IPMT over that time.
Example:
CUMIPMT(5.5%/12; 12*2; 5000; 4; 6; 0)

returns -57.80 in currency units. You took 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. The interest you pay in the 4th-6th months inclusive is 57.80 currency units. It is given as negative because you pay it.
Issues:
CUMIPMT formats the result as currency if the cell has default formatting. It thus displays a real currency amount. The CUMIPMT_ADD function is compatible with Excel; it does not apply formatting and can thus show fractional currency amounts, for example 57.5412415... The amount returned by CUMIPMT 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).
According to the forthcoming ODFF standard, this function is to be removed, and CUMIPMT_ADD is to be renamed CUMIPMT.
In contrast to PMT, IMPT, PPMT, this function has no futurevalue parameter.


Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey
 
Upvote 0 Downvote
I understand the concept. I need the algebra, formula, equation like as in: x = 2 + 1


ColdFusion Ninja for hire.
 
Upvote 0 Downvote
Does this help?
Code: 
<!--- <cfset downpayment = Form.property_price * ( Form.down_payment / 100 ) />
<cfset financed = Form.property_price - downpayment />
<cfset monthly_interest = Form.interest_rate / ( 12 * 100 ) />
 --->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
	<title>Amortization Chart</title>
</head>

<body>
<cfset monthly_payment = 1617.89> 
<cfset Financed=180000>
<cfset NumberOfPeriods=180>
<cfset interest_rate=7>
<cfset monthly_interest = interest_rate / ( 12 * 100 ) />
<cfset CumulativeInterest=0>
<cfset CumulativePrincipalPaid=0>
<cfset PreviousBalance=Financed>
<table border="1">
	<tr>
		<td colspan="7">Amortization Chart</td>
	</tr>
	<tr>
		<td align="right">Period</td>
		<td align="right">Payment Amount</td>
		<td align="right">Interest</td>
		<td align="right">Cumulative Interest</td>
		<td align="right">Principal</td>
		<td align="right">Principal Paid</td>
		<td align="right">Balance</td>
	</tr>
	<cfoutput>
		<tr>
			<td colspan="6" >#DecimalFormat(interest_rate)#</td>
			<td align="right">#DecimalFormat(Financed)#</td>
		</tr>
		<cfloop from="1" to="#NumberOfPeriods#" index="PaymentNumber">
			<cfset Interest=PreviousBalance*monthly_interest>
			<cfset CumulativeInterest=CumulativeInterest+Interest>
			<cfset CumulativePrincipalPaid=CumulativePrincipalPaid+(monthly_payment-Interest)>
			<tr>
				<td align="right">#NumberFormat(PaymentNumber)#</td>
				<td align="right">#DecimalFormat(monthly_payment)#</td>
				<td align="right">#DecimalFormat(Interest)#</td>
				<td align="right">#DecimalFormat(CumulativeInterest)#</td>
				<td align="right">#DecimalFormat(monthly_payment-Interest)#</td>
				<td align="right">#DecimalFormat(CumulativePrincipalPaid)#</td>
				<td align="right">#DecimalFormat(PreviousBalance-(monthly_payment-Interest))#</td>
			</tr>
			<cfset PreviousBalance=PreviousBalance-(monthly_payment-Interest)>
		</cfloop>
	</cfoutput>
</table>
</body>
</html>

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey
 
Upvote 0 Downvote
awesome. here is the excel version:

Code: 
=CUMIPMT( 0.07/12, 180, 180000, 1, 12, 0 )

i added start and end periods just to find out the cumulative int paid for month 1 through month 12.
Code: 
<cfset start = 1>
<cfset end = 12>

then
Code: 
<cfif PaymentNumber gte start and PaymentNumber lte end>
            	<cfset CumulativeInterest=CumulativeInterest + Interest>
            </cfif>

gives the exact result as the excel. Thanks for taking time to put this code together!


ColdFusion Ninja for hire.
 
Upvote 0 Downvote
No problem. One thing I left out was the amount of the final payment. You could put a <cfif> around the output for the monthly payment column and if it's the last payment, print the balance due instead...

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

cohan123
  • Locked
  • Question
Hypertext Jump function not working
Replies
0
Views
321
cohan123
cohan123
szeiss
  • Locked
  • Question
List Function Confusion
Replies
2
Views
130
r937
r937
LuciGoose
  • Locked
  • Question
Does ArrayClear function differently in CF5 and CF7
Replies
1
Views
85
cfSearching
cfSearching
LAdProg2005
  • Locked
  • Question
coldfusion list &amp; each function
Replies
3
Views
91
LAdProg2005
LAdProg2005
BadChough
  • Locked
  • Question
Needs a Password Reminder function
Replies
1
Views
67
lebisol
lebisol

Part and Inventory Search

Sponsor

Back
Top