Is there a formula to calculate loan payments?

Jimmy Patton

I need to calculate the periodic payment of a amortizing loan with level payments and a constant rate of interest. Is there a formula that I can use in Smartsheet to make this calculation?

Aravind GP

Hi Jimmy,

There is no specific formula for loan payments. However, you can create a simple sheet with basic formulas. Given below link to a published sheet with some basic calculations. The numbers will vary based on the initial loan amount, percentage of interest, and the monthly payment amount.

https://app.smartsheet.com/b/publish?EQBCT=62be43e03e7e4981a4a0ff90721d45c6

Andrew Stills

@Jimmy Patton if you just want the payments would look something like this formula:

=((Monthly interest rate * Loan Amount) / (1 - (1 + Monthly Interest Rate) ^ (-Total number of payments)))

For example: $100,000 Loan at 6% interest for 15 years would look like

=(((.06 / 12) * 100000) / (1 - (1 + (.06 / 12)) ^ (-(15*12))))

If you want a full amortization I suggest a quick look at the Loan Amortization Schedule in the Templates section.

Obviously, the descriptors of "Monthly" and "Years" would more aptly be "Period" & "Term".

I hope this didn't come too late to be useful to you.