function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Brock WiscottBrock Wiscott 

APR Formula

I am tring to create a formula that will provide the same output as Excel's Rate function. Havent been abe to find anything online or in the forum. Any help is greatly appreciated!

Example from Excel:  =RATE(120,-468.67,27483,,0)*12
This formula produces a value of 16.48%
 
Term Months120
Monthly Loan Payment468.67
Financed Amount27,483.00
Alain CabonAlain Cabon
With a formula in Salesforce, iterations are impossible.

Notes to Remember About the RATE Function:
#1. #NUM! error – If the results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value. 
https://corporatefinanceinstitute.com/resources/excel/functions/rate-function/

If there are periodic payments, there is no "simple formula".  RATE and IRR are determined by an iterative algorithm.

There are various iterative algorithms that Excel might use. 
Some Microsoft KB article states that Excel uses the Newton-Raphson method.  See the wiki page.

If there are no periodic payments, the RATE (IRR) can be computed directly as follows:

rate = (-fv/pv)^(1/nper) - 1

where fv and pv have opposite signs.

https://answers.microsoft.com/en-us/msoffice/forum/all/i-am-looking-for-the-mathematical-formula-behind/d344f41a-697a-41ec-80c3-5ef38f5e06fc
Alain CabonAlain Cabon
Formula
=RATE(nper, pmt, pv, [fv], [type], [guess])
 
The RATE function uses the following arguments:
  • Nper (required argument) – The total number of periods (months, quarters, years, etc.) over which the loan or investment is to be paid.
  • Pmt (required argument) – This is the payment for each period. This number must be unchanged over the life of the loan. Pmt includes principal and interest but no other fees or taxes. If pmt is omitted, fv must be inputted.
  • PV (required argument) – The present value of all future payments; what all future payments would be worth in the present.
  • FV (optional argument) – This is the future value that is the goal of the investment. This value is what we aim to have after the last payment is made. If we omit fv, it is assumed to be 0 (the future value of a loan, for example, is 0) and we must include a pmt argument instead.
  • Type (optional argument) – Determines how the formula will consider the due dates for payments. If type is omitted or 0 is inputted, payments are due at period end. If 1 is inputted, payments are due at period beginning.
  • Guess (optional argument) – Our guess of what the interest rate should be. This provides a start point for the RATE function so that it may converge on an answer easier before reaching 20 iterations.
  • When omitted, RATE assumes the guess to be 10%.
If RATE does not converge, attempt other values for this input.

Example from Excel:  =RATE(120,-468.67,27483,,0)*12

20 = nper
-468.67 = Pmt 
27483 =  pv
0 = fv : If we omit fv, it is assumed to be 0 (the future value of a loan, for example, is 0) and we must include a pmt argument instead.
0 = guess