How to Calculate Loan Repayments with Google Sheets

Are you planning to take a loan? If so, what is your monthly payment would be? Will you be able to afford that payment? What is the best repayment period for you? You can easily find answers to these questions by performing a few easy calculations on spreadsheets. In this post, I will show you how you can Calculate Loan Repayments with Google Sheets.

For these calculations, you need to know the bank interest rate on advances of your bank for your intended loan type.

Calculating Monthly Loan Repayment Amount

You can calculate the monthly payment amount directly from the Google Sheet function PMT().

PMT(): The PMT function calculates the periodic payment for an annuity investment based on constant-amount periodic payments and a constant interest rate.

=PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning])

Let;

  • Annual Interest Rate is 6%
  • Your loan amount (PV) is $40,000
  • The repayment period is 7 years

You can apply the above values to the PMT function as bellow, then you will get the monthly payment amount.

=PMT(6%/12,7*12,-40000)

Finding the best combination

Depending on your repayment capacity, your requirement, and the interest rate you need to find the best combination of interest rate, repayment period, and loan amount. You can perform what-if analysis using the above function as demonstrated in the following image to find the best combination. You can use the conditional formatting option to highlight values based on your limitations.

Calculating the amount of principal and interest for each month

In the above section, we calculated the total payment you need to pay to the bank each month. However, your principal and interest are not the same each month. You can calculate these principal and interest payments using using PPMT() and IPMT() equation.

PPMT(): The PPMT function calculates the payment on the principal of an investment based on constant-amount periodic payments and a constant interest rate.

=PPMT(rate, period, number_of_periods, present_value, [future_value, end_or_beginning])

IPMT(): The IPMT function calculates the payment on interest for an investment based on constant-amount periodic payments and a constant interest rate.

=IPMT(rate, period, number_of_periods, present_value, [future_value],[end_or_beginning])

With the PPMT function you can calculate the principal amount for a specific month and the relevant interest component is given by the IPMT function. The sum of the PPMT and IPMT components is the total amount that you have to pay each month. By subtracting the first-month principal from the loan amount you can get the balance principal amount after the first month. Subtracting the second-month principal amount from the first month’s balance you can get the balance principal amount after the second month. Likewise, you can calculate the balance principal amount after each month.

In the following example, I am calculating the monthly principal amount, monthly interest rate, monthly installment, and balance principal amount for a loan amounting to $40,000 with a 6% annual interest rate and 7 years repayment period.

By combining the above calculations, you can find the optimum loan amount and the best interest rate and repayment period to match your requirements. You can save your workings in your Google Drive and use it whenever you required to calculate loan repayments.

You can make a copy of the Google Sheets with the above workings to your drive from this link

Share via
Copy link