Is there a formula to calculate loan payments?


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
    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.



    Associate Director

    Copernicus Consulting Pte. Ltd.

    P: +65 9230 5657 | E:

    Feel free to reach out for licenses, services, and training on Smartsheet

  • Andrew Stills
    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.


    He who fails to plan is planning to fail. - Winston Churchill

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!