Apex Code Development You need to sign in to do that
Don't have an account?
WesHB 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...
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))
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
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?
Adjusted_Loan_Amount__c * Monthly_Rate__c / 1 - (1 + Monthly_Rate__c) ^ ( Term_in_years__c * 12) * -1
This follows this formula
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.
(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)
It is the closest found formula of excel
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
Take a look! Thanks.