Google Sheets Amortization Schedule (Easy Guide)

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.

1. 2. Create a table with 5 columns, each corresponding to the data required to create the schedule.

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.

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)

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)

1. 4. Your schedule should now show the Monthly Payment for each month.

1. 5. To calculate the “Interest Payment”, type in the array formula and the IPMT formula, as shown below.

1. 6. To calculate the “Principal Payment”, type in the array formula and the PPMT formula, as shown below.

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.

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.

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

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.

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

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.

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.

1. 6. To obtain the principal payment, you’ll also need to combine the IF formula with the PPMT, as shown below.

1. 7. Include the amount in “Extra Payments” and drag it down to copy that same amount.

1. 8. Use this formula to balance the loan, in the corresponding cell, right under the initial loan amount, as shown below.

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.

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

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.