Don’t forget to share this post

Google Sheets is known by spreadsheet users as the free online solution to store, structure, and analyze data. It’s also becoming more widely used among finance experts, as it has powerful built-in financial functions that allow you to perform from simple to more complex calculations, whether it’s for your business or personal use.

For example, you can use Google Sheets PMT, PPMT, and IPMT functions to calculate how much you’ll pay to settle a loan or investment, including the principal and interest. Moreover, you can build your loan amortization schedule to check how monthly payments impact the final amount.

In this article, you’ll learn what the Google Sheets loan amortization schedule is and how to create a standard one and a loan amortization schedule, including extra payments.

What is a Google Sheets loan amortization schedule?

Loan amortization is a process aimed at paying off the interest payment for a given period, and then the remaining amount is put towards reducing the principal payment. As these payments are periodic, you can schedule them beforehand to see the principal and interest amount you’ll need to pay until you fully settle the loan.

In Google Sheets, you can use the PMT, PPMT, and IPMT functions to create your own loan amortization schedule.

PMT, PPMT, and IPMT functions

Before diving into the steps to create your loan amortization schedule, let’s see how these functions are expressed as a formula and what each argument means.

PMT Syntax

= PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning])
  • rate is the interest rate for the payment period.
  • number_of_periods is the number of payments you need to make to settle the loan.
  • present_value is the principal amount.
  • future_value is an optional argument that represents the future value that will remain after making the final payment.
  • end_or_beginning is an optional argument that specifies if the payments are due at the end (represented by “0”) or start (“1”) of each period. This value is set to “0” by default.

PPMT Syntax

The PPMT syntax is almost the same as the PMT, but it includes the period argument:

=PPMT(rate, period, number_of_periods, present_value, [future_value, end_or_beginning])
  • period refers to the amortization period. For example, if you have 60 periods, this argument should be represented by any number between 1 - 60.

IPMT Syntax

Although the syntax is the same, whereas the PPMT is used to calculate the principal amount, the IPMT function calculates the interest amount, so you can determine the proportions for each payment.

How to create a loan amortization schedule in Google Sheets?

Now that you know the syntax for each function used to create your loan amortization schedule, let’s begin by preparing the amortization table format.

How to prepare the loan amortization schedule table format?

Before using the functions, make sure you set up the data and the table format for the amortization schedule. This is how you can prepare the loan amortization schedule.

  1. 1. Enter the values for your amortization schedule, including the rate, number_of_periods, and the present_value.
Google Sheets Amortization Schedule Easy Guide Amortization schedule values
Google Sheets Amortization Schedule (Easy Guide) - Amortization schedule values
  1. 2. Create a table with 5 columns, each corresponding to the data required to create the schedule.
Google Sheets Amortization Schedule Easy Guide 5 column table
Google Sheets Amortization Schedule (Easy Guide) - 5 column table

How To Use SPARKLINE in Google Sheets (+ Examples)

With the SPARKLINE function you can create one-cell miniature charts – perfect for financial reports. Here’s how to use SPARKLINE in Google Sheets.

READ MORE
How To Use SPARKLINE in Google Sheets Examples

How to fill the loan amortization schedule using the PMT, PPMT, and IPMT functions?

As you saw in the previous section, you’ll need to adapt three formulas to your data. This is how to fill the loan amortization schedule using the PMT, PPMT, and IPMT functions.

  1. 1. Depending on the number_of_periods, you can use this formula to quickly fill in the first column, =ArrayFormula(row(cell range)), as shown below.
Google Sheets Amortization Schedule Easy Guide Months
Google Sheets Amortization Schedule (Easy Guide) - Months
  1. 2. To fill in the data for “Monthly Payment”, use the PMT formula, including only the first 3 arguments. Once you do, press “Enter”.
=PMT(rate/12,number_of_periods*12, present_value)
Google Sheets Amortization Schedule Easy Guide Monthly payment
Google Sheets Amortization Schedule (Easy Guide) - Monthly payment

NOTE: Make sure to divide the rate by 12 and multiply the loan period (if in years) by 12, as there are 12 months in a year.

  1. 3. To fill in the data for the whole column, add the array formula to the PMT formula. Press “Enter” to apply the formula.
=ArrayFormula(if(len(cell range), PMT(rate/12,number_of_periods*12, present_value)
Google Sheets Amortization Schedule Easy Guide Apply array formula to PMT
Google Sheets Amortization Schedule (Easy Guide) - Apply array formula to PMT
  1. 4. Your schedule should now show the Monthly Payment for each month.
Google Sheets Amortization Schedule Easy Guide Monthly Payment column complete
Google Sheets Amortization Schedule (Easy Guide) - Monthly Payment column complete
  1. 5. To calculate the “Interest Payment”, type in the array formula and the IPMT formula, as shown below.
Google Sheets Amortization Schedule Easy Guide Interest Payment column complete
Google Sheets Amortization Schedule (Easy Guide) - Interest Payment column complete
  1. 6. To calculate the “Principal Payment”, type in the array formula and the PPMT formula, as shown below.
Google Sheets Amortization Schedule Easy Guide Principal Payment column complete
Google Sheets Amortization Schedule (Easy Guide) - Principal Payment column complete
  1. 7. To calculate the “Loan Balance” you need to subtract the first “Principal Payment” from the initial loan amount. If the values are negative, add a “+” not “minus” to subtract. Press “Enter” to obtain the result.
Google Sheets Amortization Schedule Easy Guide Loan Balance
Google Sheets Amortization Schedule (Easy Guide) - Loan Balance
  1. 8. Grab the cell where you included the loan balance formula, and drag it down until the last period. If you did it correctly, the last cell should contain the value of “0”, as shown below.
Google Sheets Amortization Schedule Easy Guide Loan Balance zero result
Google Sheets Amortization Schedule (Easy Guide) - Loan Balance zero result
Google Sheets FORECAST Function Examples
Google Sheets FORECAST Function (+ Examples)

The Google Sheets FORECAST function predicts future values based on your data. Here’s how to use the FORECAST function step-by-step, with examples.

READ MORE

How to create a loan amortization schedule with extra payments Google Sheets?

As you’ve seen, creating a loan amortization schedule in Google Sheets using PMT, PPMT, and IPMT functions is a straightforward process. However, if you need to include extra principal payments, you’ll need to follow different steps.

  1. 1. Include another row with “Payment”, under the three main input values and a new column for “Extra Payment”. You’ll need to leave an empty cell under each column name, except for the “Loan Balance”.
Google Sheets Amortization Schedule Easy Guide Include Payment and Extra Payment
Google Sheets Amortization Schedule (Easy Guide) - Include Payment and Extra Payment
  1. 2. This time, you’ll calculate the periodic payment in the cell that corresponds to “Payment”. You can use the PMT formula, using the first three arguments, as shown below.
Google Sheets Amortization Schedule Easy Guide Calculate PMT in Payment
Google Sheets Amortization Schedule (Easy Guide) - Calculate PMT in Payment
  1. 3. Include the value of the loan amount in the first cell of the “Loan Balance” column, by typing the “=” sign followed by the cell reference. Press “Enter”.
Google Sheets Amortization Schedule Easy Guide Loan Balance 2
Google Sheets Amortization Schedule (Easy Guide) - Loan Balance
  1. 4. To obtain the PMT value from the new “Payment” value, you’ll need to combine the IF formula with the PMT formula, as shown below. Once you type it in, press “Enter” to apply.
Google Sheets Amortization Schedule Easy Guide IFPMT
Google Sheets Amortization Schedule (Easy Guide) - IF+PMT

NOTE: This combination of IF+PMT checks the balance column in the row above. For example, cell D6 will check the value in cell H5. If the value in the “Payment” is less than the “Loan Balance” value, the PMT value remains the same. If the PTM is more than the balance value, the IF formula will run the second part of the formula.

  1. 5. To obtain the interest payment, combine the IF formula with the IPMT, as shown below.
Google Sheets Amortization Schedule Easy Guide IFIPMT
Google Sheets Amortization Schedule (Easy Guide) - IF+IPMT
  1. 6. To obtain the principal payment, you’ll also need to combine the IF formula with the PPMT, as shown below.
Google Sheets Amortization Schedule Easy Guide IFPPMT
Google Sheets Amortization Schedule (Easy Guide) - IF+PPMT
  1. 7. Include the amount in “Extra Payments” and drag it down to copy that same amount.
Google Sheets Amortization Schedule Easy Guide Extra Payments
Google Sheets Amortization Schedule (Easy Guide) - Extra Payments
  1. 8. Use this formula to balance the loan, in the corresponding cell, right under the initial loan amount, as shown below.
Google Sheets Amortization Schedule Easy Guide Loan balance formula
Google Sheets Amortization Schedule (Easy Guide) - Loan balance formula
  1. 9. Drag down until you reach “0”. As you can see, the rest of the cells have changed, and you now know how many payments you’ll need to make, with the extra payments, to pay off the loan.
Google Sheets Amortization Schedule Easy Guide Pay off loan
Google Sheets Amortization Schedule (Easy Guide) - Pay off loan

How to automate your data on top of Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

Conclusion

As you’ve seen, the amortization schedule in Google Sheets can be customized according to the type of loan by simply changing the values. Having a loan amortization schedule can also help you determine the interest and principal payments on your loan over a specified period. This way, you’ll be able to see how payments related to the interest decrease, whereas those related to the principal amount increase.

You should now have a better understanding of what the Google Sheets loan amortization schedule is, as well as how to prepare a loan amortization schedule using the PMT, PPMT, and IPMT functions. In case you need to include extra payments, you’re also ready to adapt the amortization schedule to any extra payments.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Aug 5 2022, Updated Nov 11 2022