converting the formula

Posted by mysteryminds on 05-May-2015 11:22

how to convert this excel formula into rollbase 

Payment=(B4/b5,b3*b5,-b2)

{!Loan_Amount#value} =b2
{!Interest#value} =b4
{!Months}=b3

All Replies

Posted by pvorobie on 05-May-2015 11:47

And what this formula does?

Posted by mysteryminds on 05-May-2015 11:47

the real formula in excel is  =PMT(B4/B5,B3*B11)

The values from

B2 = 200000

B3 = 30

B4 = 6.75%

B5  =12

Result is $1297.20

Posted by mysteryminds on 05-May-2015 11:48

Hi robie I have this formula caliculated in Excel but I would like to transfer that formula in Rollbase Please help on this

I need to convert that excel formula in Rollbase formula 

Posted by pvorobie on 05-May-2015 11:51

Unfortunately we don't have PMT as built-in function.

Posted by mysteryminds on 05-May-2015 11:52

so how can we convert that PMT  and rest of the formula

Posted by mysteryminds on 05-May-2015 12:01

so is there a way to cut that formula into different parts and finally making up the result

Posted by pvorobie on 05-May-2015 12:02

Please check this out for PMT implementation in JavaScript:

stackoverflow.com/.../excel-pmt-function-in-js

Posted by mysteryminds on 05-May-2015 12:05

cant we have a better split directly using the fields functions internally and adding them

Posted by mysteryminds on 05-May-2015 12:09

robie can you give me a sample how can I implement the same sample javascript code in rollbase plz

Posted by Ricardo Rafols on 05-May-2015 12:16
Hi misteryminds,

Following provobie example : http://stackoverflow.com/questions/2094967/excel-pmt-function-in-js

Try this:

function PMT(i, n, p) {
return i * p * Math.pow((1 i), n) / (1 - Math.pow((1 i), n));
}

return PMT({!Interest#value}, {!Months}, {!Loan_Amount#value});


PS: it is just an example. Your formula might contain a different business logic.

Regards,
Ricardo
Posted by mysteryminds on 05-May-2015 12:24

Ricardo I tried this as such... but on validation itself its failing bad :(

Posted by Ricardo Rafols on 05-May-2015 12:49
What is the error message?

I need more details...
Posted by jsniemi79 on 05-May-2015 12:59

There were a couple plus signs missing and the return in front of the function fails.

Try this and replace the values in the function call with your tokens:

function pmt(rate_per_period, number_of_payments, present_value, future_value, type){

   if(rate_per_period != 0.0){

       // Interest rate exists

       var q = Math.pow(1 + rate_per_period, number_of_payments);

       return -(rate_per_period * (future_value + (q * present_value))) / ((-1 + q) * (1 + rate_per_period * (type)));

   } else if(number_of_payments != 0.0){

       // No interest rate, but number of payments exists

       return -(future_value + present_value) / number_of_payments;

   }

   return 0;

}

pmt(0.0675/12,12*30, 200000, 0, 0);

Posted by mysteryminds on 05-May-2015 13:35

well to make it more simpler I tried something simpler than everything

var loan_amount = {!total_loan_amount};

var interest_rate = {!interest_rate};

var loan_term = parseInt({!loan_term#value});

var rate = interest_rate/(100*12);

var r = rate + 1;

var calc = 1-(Math.pow(r, -(loan_term)));

var payment = (rate/calc)*loan_amount;

payment = payment.toFixed(2);

if (payment == "Infinity" || payment == "NaN") return "Cannot be computed because Loan Term is null."

else return "$"+payment ;

Posted by Ricardo Rafols on 05-May-2015 13:40
formula.png
 
This thread is closed