Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Jason D Moore

# Calculating the "LOAN PERIOD"

I am trying to create a SALESFORCE formula that calculates the "Loan Period" using the following fields:

Interest Rate [ 6.00% ]
Loan Period [ ? ]
Annual Loan Instalments [ 52 ]
Total Loan Instalments [ 1,560 ]
Loan Repayment [ 150 ]

Similar Microsoft Formula:  NPER (rate, pmt, pv, [fv], [type])

Does anyone have the SALESFORCE  formula that can calculate the "Loan Period" using these fields? Thanks

Best Answer chosen by Jason D Moore
Jason D Moore

THIS FORMULA HAS THE SAME RESULT AS THE MS EXCEL "NPER" FORMULA

Loan_Period__c = LOG((Loan_Repayment__c /(Loan_Repayment__c - (Loan_Amount__c * ( Interest_Rate__c/52))))) / (52 * LOG((1+(Interest_Rate__c /52))))

```/**
* Soda_NPER(rate, PMT, PV, FV, type)
*
*
* @param r
* @param y
* @param p
* @param f
* @param t
*/
public static double Soda_NPER(double r, double y, double p, double f, boolean t) {
double retval = 0;
if (r == 0) {
retval = -1 * (f + p) / y;
} else {
double r1 = r + 1;
double ryr = (t ? r1 : 1) * y / r;
double a1 = ((ryr - f) < 0) ? Math.log(f - ryr) : Math.log(ryr - f);
double a2 = ((ryr - f) < 0) ? Math.log(-p - ryr) : Math.log(p + ryr);
double a3 = Math.log(r1);
retval = (a1 - a2) / a3;
}
return retval;
}```
```/**
* Soda_PMT(rate, NPER, PV, FV, type)
*
*
* @param r
* @param n
* @param p
* @param f
* @param t
*/
public static double Soda_PMT(double r, double n, double p, double f, boolean t) {
double retval = 0;
if (r == 0) {
retval = -1 * (f + p) / n;
} else {
double r1 = r + 1;
retval = (f + p * Math.pow(r1, n)) * r / ((t ? r1 : 1) * (1 - Math.pow(r1, n)));
}
return retval;
}```
```/**
* Soda_FV(rate, NPER, PMT, PV, type)
*
* Future value of an amount given the number of payments, rate, amount of individual payment, present value and
* boolean value indicating whether payments are due at the beginning of period (false => payments are due at end of
* period)
*
* @param r
*            rate
* @param n
*            num of periods
* @param y
*            pmt per period
* @param p
*            future value
* @param t
*            type (true=pmt at end of period, false=pmt at begining of period)
*/
public static double Soda_FV(double r, double n, double y, double p, boolean t) {
double retval = 0;
if (r == 0) {
retval = -1 * (p + (n * y));
} else {
double r1 = r + 1;
retval = ((1 - Math.pow(r1, n)) * (t ? r1 : 1) * y) / r - p * Math.pow(r1, n);
}
return retval;
}

/**
* Soda_PV(rate, NPER, PMT, FV, type)
*
* Present value of an amount given the number of future payments, rate, amount of individual payment, future value
* and boolean value indicating whether payments are due at the beginning of period (false => payments are due at
* end of period)
*
* @param r
* @param n
* @param y
* @param f
* @param t
*/
public static double Soda_PV(double r, double n, double y, double f, boolean t) {
double retval = 0;
if (r == 0) {
retval = -1 * ((n * y) + f);
} else {
double r1 = r + 1;
retval = (((1 - Math.pow(r1, n)) / r) * (t ? r1 : 1) * y - f) / Math.pow(r1, n);
}
return retval;
}```

I have an Apex Helper call SodaFinancialHelper. I jsut copy past some of my function here . hope this help

Jason D Moore
wow that is awesome .... Thank you
Jason D Moore

THIS FORMULA HAS THE SAME RESULT AS THE MS EXCEL "NPER" FORMULA

Loan_Period__c = LOG((Loan_Repayment__c /(Loan_Repayment__c - (Loan_Amount__c * ( Interest_Rate__c/52))))) / (52 * LOG((1+(Interest_Rate__c /52))))

This was selected as the best answer
Jeff Douglas (Personal)
For future reference, I put together an Apex class (with unit tests) to calculate PV, PMT, FV, IPMT & PPMT Excel functions.

You can find it at: https://github.com/jeffdonthemic/apex-financials

Jeff Douglas