function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Jason D MooreJason 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 MooreJason 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))))

All Answers

Ronty AhmadRonty Ahmad
/**
  * 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 MooreJason D Moore
wow that is awesome .... Thank you
Jason D MooreJason 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)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
Trailhead