converting the formula - Forum - Rollbase - Progress Community

converting the formula

 Forum

converting the formula

  • how to convert this excel formula into rollbase 

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

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

  • And what this formula does?

  • 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

  • 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 

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

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

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

  • Please check this out for PMT implementation in JavaScript:

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

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

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

  • 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
  • Ricardo I tried this as such... but on validation itself its failing bad :(

  • What is the error message?

    I need more details...
  • 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);

  • 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 ;