asked on

Coldfusion PMT(rate,nper,pv,fv) function needed, similar to the one on excel

Coldfusion PMT(rate,nper,pv,fv) function needed, similar to the one on excel

i need a function with all four variable above

Brijesh Chauhan

Java POI already has a function PMT, for more info on the class,
http://www.oschina.net/uploads/doc/poi-3.1-FINAL/org/apache/poi/hssf/record/formula/functions/Pmt.html

You can create an object of the class and use it,

wb = createObject("java", "org.apache.poi.hssf.record.formula.FinanceFunction").init();

wb.Pmt(rate, nper, pv, fv, type)

rate the loan interest rate.
nper the number of loan repayments.
pv the present value of the future payments (or principle).
fv the future value (default zero) surplus cash at the end of the loan lifetime.
type whether payments are due at the beginning(1) or end(0 - default) of each payment period.

mugsinc

ASKER

i need it in coldfusion

Dushyant Sharma

there is a javascript PMT function provided in another post

<script>
function Pmt(r,np,pv,fv) {
r = r/1200
if (!fv) fv = 0;
pmt=-(r * (fv+Math.pow((1+r),np)*pv)/(-1+Math.pow((1+r),np)));
finalPmt=roundOff(pmt,2);
alert(finalPmt);
}

function roundOff(value, dplaces){
value=value.toString()

if((value.indexOf(".")!=-1)&&(value.length>(value.indexOf(".")+dplaces))){
three=value.substring(value.indexOf(".")+dplaces+1,value.indexOf(".")+dplaces+2)
one=value.substring(0,value.indexOf(".")+dplaces)
two=value.substring(value.indexOf(".")+dplaces,value.indexOf(".")+dplaces+1)
if(parseInt(three)>=5){value=one+(parseInt(two)+1);value=parseFloat(value)}
else{value=one+two;value=parseFloat(value)}
}
return value;
}

</script>

<body onLoad="Pmt(5.75,300,-200000);">

https://www.experts-exchange.com/questions/20401797/Urgent-Javascript-PMT-Function.html

Brijesh Chauhan

@mugsinc: -- YES that's in CF ONLY, if you are using CF9, then <cfspreadsheet tag users JAVA POI otherwise you can have JAVA POI download and installed separately http://cfsearching.blogspot.com/2008/08/how-to-install-poi-on-coldfusion-8.html

<sfscript>
wb = createObject("java", "org.apache.poi.hssf.record.formula.FinanceFunction").init();

wb.Pmt(rate, nper, pv, fv, type);
</cfscript>

the above is CF code...

Brijesh Chauhan

the above should be read as
<cfscript> <===
wb = createObject("java", "org.apache.poi.hssf.record.formula.FinanceFunction").init();

wb.Pmt(rate, nper, pv, fv, type);
</cfscript>

mugsinc

ASKER

i dont want it in java integrated to coldfusion, i am looking for the complete formula done on coldfusion

Brijesh Chauhan

Have taken the function from ddsh79 and converted to CF

>>
function Pmt(r,np,pv,fv) {
r = r/1200
if (!fv) fv = 0;
pmt=-(r * (fv+Math.pow((1+r),np)*pv)/(-1+Math.pow((1+r),np)));
finalPmt=roundOff(pmt,2);
alert(finalPmt);
}

<<

THE ABOVE FUNCTION IN CF
------

<cffunction name="Pmt" returntype="any" access="public">

<cfargument name="r" required="no" type="any">
<cfargument name="np" required="no" type="any">
<cfargument name="pv" required="no" type="any">
<cfargument name="fv" required="no" type="any" default="0">

<cfset r = r / 1200 />
     <cfset pmt = - ( r * (fv + power((1 + r), np) * pv) / (-1 + power((1 + r), np))) />

<cfreturn decimalFormat(pmt) />

</cffunction>

<cffunction name="power" returntype="any" access="public">

<cfargument name="a" type="numeric" required="yes" />
<cfargument name="b" type="numeric" required="yes" />

<cfset apowb = a />

<cfif b GT 1>

<cfloop from="1" to="#b-1#" index="i">
<cfset apowb = apowb * apowb />
</cfloop>

</cfif>

<cfreturn apowb />

</cffunction>

mugsinc

ASKER

i tried the cffunction out in a component, got a value of 0, is the below correct to invoke the function

<cfinvoke component="aFormula.cal" method="Pmt" returnVariable="gpmt"
r="5" np="120" fv="10000" pv="-465286">

<cfdump var="#gpmt#">

ASKER CERTIFIED SOLUTION

Brijesh Chauhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mugsinc

ASKER

brilliant, and spot on