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
WesHBWesHB 

Formula for Amortization "Payment" please help!

My premium support inside Salesforce staff can not help.
 
We have always calculated a principle and interest payment using, Microsoft Excel, a financial calculator or loan software.
 
They all have a "PMT" function and salesforce does not.
 
This is absolutely critical for us and has stopped us in our tracks. Using "simple" interest will NOT cut it for us, as we are a commercial loan company.
 
Has anyone successfully written a principle & interest formula in salesforceeaze?
 
Please (begging) help...
fifedogfifedog
Have you engaged your CSM, customer success manager?  If you don't know who they are get a hold of your rep and ask.
UO G_DaviesUO G_Davies
http://www.hughchou.org/calc/formula.html

How to calculate amortization tables by hand
I have gotten numerous requests from individuals wondering what the simple formula is for calculating the monthly payment and also the amortization table. Instead of just showing some boring source code, I thought I would try to explain it.

Spreadsheet (Excel, Lotus, Quattro) users should look here

NEW! Want to see how this is derived? Find a full derivation here! (Thanks goes to "Hans" Gurdip Singh.)

NOTE: This first part is for United States mortgages. Look here for the Canadian formula.

First you must define some variables to make it easier to set up:

P = principal, the initial amount of the loan
I = the annual interest rate (from 1 to 100 percent)
L = length, the length (in years) of the loan, or at least the length over which the loan is amortized.

The following assumes a typical conventional loan where the interest is compounded monthly. First I will define two more variables to make the calculations easier:

J = monthly interest in decimal form = I / (12 x 100)
N = number of months over which loan is amortized = L x 12

Okay now for the big monthly payment (M) formula, it is:

J
M = P x ------------------------

1 - ( 1 + J ) ^ -N

where 1 is the number one (it does not appear too clearly on some browsers)

So to calculate it, you would first calculate 1 + J then take that to the -N (minus N) power, subtract that from the number 1. Now take the inverse of that (if you have a 1/X button on your calculator push that). Then multiply the result times J and then times P. Sorry, for the long way of explaining it, but I just wanted to be clear for everybody.

The one-liner for a program would be (adjust for your favorite language):

M = P * ( J / (1 - (1 + J) ** -N))
GraylabGraylab

The solution is to break the equation into multiple fields:

Step 1:

1 / ( ( 1 + Interest ) ^ ( (Term in years) * 12 )

Step 2:

1 - {StepOne}

Step 3:

{Principal} * {Monthly Interest} /
{StepTwo}

Logically you will need to have a field that provides monthly interest as well.

~Grayson
nathand0071.3898152410885881E12nathand0071.3898152410885881E12
thanks uo, that worked. Now the only formula I cant find is the one to calculate monthly interest. I know it is a spin off of that main formula I just can't figure out what
nathand0071.3898152410885881E12nathand0071.3898152410885881E12
I realize this thread is 8 years old and am blown away that their are not more solutions out there for this
tylersutherland1.3899025762871362E12tylersutherland1.3899025762871362E12
Did you ever Figure this out?

I am also trying to figure out how to write a formula to calculate Monthly payment.

I have broken it down as explained above, even created a separate field for the monthly interest calculation... The syntax on all of the formulas work... But on my object, it populates the field with #Error!

Any help would be greatly appreciated...

Do you by chance have any information on calculating an APR as well?
Josh BolesJosh Boles
Does anyone have any insight that can add value on how to accomplish monthly debt service payment via SFDC? Thanks!
John DevineJohn Devine
The formulas posted do not work.  One has a "**" function which SF does not recognize.  The other does not amortize correctly (or something).  Here's the formula I used:
Adjusted_Loan_Amount__c * Monthly_Rate__c / 1 - (1 + Monthly_Rate__c) ^ ( Term_in_years__c * 12) * -1

This follows this formula  
P = L * i / 1 - (1 + i) ^ t * -1
Where
P = Monthly payment
L = Loan Amount (Adjusted Loan Amount in my formula)
i = monthly interest as a decimal  or (Interest rate /12)
t = loan term in months (or as in my formula, Term in years * 12)

With a loan amount of $225,000 at 4% over 30 years, the monthly payment should be $1074.  The formula above returns $794.  Which is better than the ($1.00), $0.00, and $225,000 results I had initially recieved.  And I can actually use this for demo purposes but obviously not for real transactions.  Not sure if we can do this formula without Apex.  I'm going to post this as a new topic as this is now a decade old.
 
Stephen ArrivelloStephen Arrivello
I am also interested in finding out how to do this.
Dan EvelandDan Eveland
This has worked for me...

(Est_Total_Loan_Amt_del__c*((Est_Int_Rate__c/12)*((1+(Est_Int_Rate__c/12))^VALUE(TEXT(Est_Term_Y__c))*12))/((1+(Est_Int_Rate__c/12))^(VALUE(TEXT(Est_Term_Y__c))*12)-1))

Loan Amount = Est_Total_Loan_Amt_del__c
Interest Rate = Est_Int_Rate__c
Loan Term Years = Est_Term_Y__c (if you have it in months (remove *12 after this in formula)
Nash ZgonjaninNash Zgonjanin
Thank you @Dan Eveland. Your Formula worked very well. It need to be marked as BEST ANSWER
Tania SamuelTania Samuel
PMT = ((InterestRate/100)/12)*(NetLoan*((((InterestRate/100)/12)+1)^PeriodInYears*12))/((((InterestRate/100)/12)+ 1 )*(((((InterestRate/100)/12)+1)^PeriodInYears*12)-1))

It is the closest found formula of excel
 
nsweeneynsweeney
It is unbelievable to me that this is not incorporated into the standard functionality.  How can Salesforce sell to the financial services industry yet not have the ability to calculate a payment?  Even paid tech support people can't do this.  It's almost as bad as when Salesforce did not include a middle initial / name field.  For those of you who are new to Salesforce, you're fortunate that you didn't have to deal with that problem.  Very poor planning Salesforce regarding your calculation feature.
 
nsweeneynsweeney
Can anyone assist in getting this formula to work in SF?  Thank you in advance!

Escel formula is PMT(Rate*(365/360)/12,Amort*12,-Loan Amount)

Rate = Rate_Quoted__c
Amort = Amort_Period_Quoted_Months__c
Loan Amount = Loan_Amount_AUTO__c
3 Creeks3 Creeks
If anyone on this thread is still looking for a loan payment or amortization calculator, my company just released a new app on the AppExchange that does that.  The listing is here: https://appexchange.salesforce.com/appxListingDetail?listingId=a0N4V00000GXIktUAH
Take a look!  Thanks.
Nik VetterNik Vetter
I spent some time looking for this and discovered the answer provided on this thread by @Dan Eveland was the formula that worked after trying many others.