# Way to use excel formula

edited January 9, 2018
Is there a way to use excel formula in formual fields?
Like gross annual percentage rate o other finance formula
Thank you

• edited May 30, 2017
Not at this time. The skuid table is being more and more robust but we are not yet at a time where an excel formula could be copied and pasted into a formula field.
• edited January 9, 2018
A way to calculate in a ui-field something like:

private double calculateRate(double nper, double pmt, double pv, double fv, double type, double guess) {
//FROM MS http://office.microsoft.com/en-us/excel-help/rate-HP005209232.aspx

integer FINANCIAL_MAX_ITERATIONS = 20;//Bet accuracy with 128
double FINANCIAL_PRECISION = 0.0000001;//1.0e-8

double y, y0, y1, x0, x1 = 0, f = 0, i = 0;
double rate = guess;
if (Math.abs(rate) < FINANCIAL_PRECISION) {
y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
} else {
f = Math.exp(nper * Math.log(1 + rate));
y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
}
y0 = pv + pmt * nper + fv;
y1 = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;

// find root by Newton secant method
i = x0 = 0.0;
x1 = rate;
while ((Math.abs(y0 - y1) > FINANCIAL_PRECISION) && (i < FINANCIAL_MAX_ITERATIONS)) {
rate = (y1 * x0 - y0 * x1) / (y1 - y0);
x0 = x1;
x1 = rate;

if (Math.abs(rate) < FINANCIAL_PRECISION) {
y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
} else {
f = Math.exp(nper * Math.log(1 + rate));
y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
}

y0 = y1;
y1 = y;
++i;
}
return rate;
}
• edited June 1, 2017
Andrea,

You can setup a UI only field to show the value, but you'll need to calculate the value in a JavaScript snippet.

Is this calculation done on values in 1 row of an object?  You may want to put this logic into a trigger.  It will be much faster to load this value from Salesforce rather than calculate it each time it is displayed.

Thanks,

Bill
• edited January 9, 2018
Hi Bill,
thanks a lot for your help,
right now the process is executed in a UI-only model, and this is giving me some problems, due to the fact that I can't order the results using a particular uionly field.
Do you think that I can resolve the problem creating an sf object linked to the User, so that I can temporarily store in it the values? If the answer is yes, do you now any way to copy rows from the uionly model to the model based on new sf object? I tried with "adopt rows in a model" but for this action the two models need to be based on the same object, and it's not my case.

Thank again, andrea
• edited June 1, 2017
Andrea,

When you sort a column in Skuid, it does a new SOQL pull from Salesforce sorted by whatever fields you selected for sorting (i.e. Salesforce handles the sorting).  I still think your best option is to add a field to your object that holds the values you use for your calculation.  Then add a trigger to update this field in Salesforce.  Then your Skuid page is simple.

Since you have the UI only model working, I'd say that you should add a sort function to your calculation or a separate snippet that you can run by pressing a button to sort the results.

I am not sure about your use case.  Is your usage just to provide a way to calculate gross annual percentage but not 'save' the numbers?

Thanks,

Bill