[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Google Sheets is known by spreadsheet users as the free online solution to store, structure, and analyze data. It\u2019s 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\u2019s for your business or personal use.<\/p>\n
For example, you can use Google Sheets PMT<\/a>, PPMT, and IPMT functions to calculate how much you\u2019ll pay to settle a loan or investment, including the principal and interest. Moreover, you can build your loan amortization<\/a> schedule to check how monthly payments impact the final amount.<\/p>\n In this article, you\u2019ll learn what the Google Sheets loan amortization schedule is and how to create a standard one and a loan amortization schedule, including extra payments.<\/p>\n [\/et_pb_text][et_pb_text module_id=”what-is-a-google-sheets-loan-amortizatio” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 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\u2019ll need to pay until you fully settle the loan.<\/p>\n In Google Sheets, you can use the PMT, PPMT, and IPMT functions to create your own loan amortization schedule.<\/p>\n [\/et_pb_text][et_pb_text module_id=”pmt-ppmt-and-ipmt-functions” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Before diving into the steps to create your loan amortization schedule, let\u2019s see how these functions are expressed as a formula and what each argument means.<\/p>\n The PPMT<\/a> syntax is almost the same as the PMT, but it includes the period argument:<\/p>\n 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.<\/p>\n [\/et_pb_text][et_pb_text module_id=”how-to-create-a-loan-amortization-schedu” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now that you know the syntax for each function used to create your loan amortization schedule, let\u2019s begin by preparing the amortization table format.<\/p>\n 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.<\/p>\n 1.<\/strong> Enter the values for your amortization schedule, including the rate, number_of_periods, and the present_value.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-amortization-schedule-input-values.webp” alt=”Google Sheets amortization schedule \u2014 annual interest rate 6%, loan period 5 years and loan amount $70,000.00 in a sheet named Schedule” title_text=”Annual interest rate 6%, loan period 5 years and loan amount $70,000.00 in a sheet named Schedule” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> Create a table with 5 columns, each corresponding to the data required to create the schedule.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-amortization-schedule-table-columns.webp” alt=”Google Sheets amortization schedule \u2014 Month, Monthly Payment, Interest Payment, Principal Payment and Loan Balance column headers” title_text=”Month, Monthly Payment, Interest Payment, Principal Payment and Loan Balance column headers” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you saw in the previous section, you\u2019ll need to adapt three formulas to your data. This is how to fill the loan amortization schedule using the PMT, PPMT, and IPMT functions.<\/p>\n 1.<\/strong> 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.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-amortization-schedule-month-column.webp” alt=”Google Sheets amortization schedule \u2014 ArrayFormula with ROW numbering the Month column from 1″ title_text=”ArrayFormula with ROW numbering the Month column from 1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> To fill in the data for \u201cMonthly Payment\u201d, use the PMT formula, including only the first 3 arguments. Once you do, press \u201cEnter\u201d.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-amortization-schedule-pmt-formula.webp” alt=”Google Sheets amortization schedule \u2014 PMT formula typed in the first Monthly Payment cell” title_text=”PMT formula typed in the first Monthly Payment cell” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n NOTE:<\/strong> 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.<\/p>\n 3.<\/strong> To fill in the data for the whole column, add the array formula<\/a> to the PMT formula. Press \u201cEnter\u201d to apply the formula.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-amortization-schedule-pmt-arrayformula.webp” alt=”Google Sheets amortization schedule \u2014 ArrayFormula wrapped around PMT in the first Monthly Payment cell” title_text=”ArrayFormula wrapped around PMT in the first Monthly Payment cell” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 4.<\/strong> Your schedule should now show the Monthly Payment for each month.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-amortization-schedule-monthly-payment.webp” alt=”Google Sheets amortization schedule \u2014 Monthly Payment column filled with the same payment for every month” title_text=”Monthly Payment column filled with the same payment for every month” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 5.<\/strong> To calculate the \u201cInterest Payment\u201d, type in the array formula and the IPMT formula, as shown below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-amortization-schedule-ipmt-interest.webp” alt=”Google Sheets amortization schedule \u2014 ArrayFormula with IPMT filling the Interest Payment column” title_text=”ArrayFormula with IPMT filling the Interest Payment column” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 6.<\/strong> To calculate the \u201cPrincipal Payment\u201d, type in the array formula and the PPMT formula, as shown below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-amortization-schedule-ppmt-principal.webp” alt=”Google Sheets amortization schedule \u2014 ArrayFormula with PPMT filling the Principal Payment column” title_text=”ArrayFormula with PPMT filling the Principal Payment column” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 7.<\/strong> To calculate the \u201cLoan Balance\u201d you need to subtract the first \u201cPrincipal Payment\u201d from the initial loan amount. If the values are negative, add a \u201c+\u201d not \u201cminus\u201d to subtract. Press \u201cEnter\u201d to obtain the result.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-amortization-schedule-loan-balance.webp” alt=”Google Sheets amortization schedule \u2014 first Loan Balance cell after subtracting the first principal payment” title_text=”First Loan Balance cell after subtracting the first principal payment” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 8.<\/strong> 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 \u201c0\u201d, as shown below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-amortization-schedule-balance-zero.webp” alt=”Google Sheets amortization schedule \u2014 Loan Balance column dragged down to $0.00 at month 60″ title_text=”Loan Balance column dragged down to $0.00 at month 60″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”how-to-create-a-loan-amortization-schedu” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you\u2019ve 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\u2019ll need to follow different steps.<\/p>\n 1.<\/strong> Include another row with \u201cPayment\u201d, under the three main input values and a new column for \u201cExtra Payment\u201d. You\u2019ll need to leave an empty cell under each column name, except for the \u201cLoan Balance\u201d.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/amortization-schedule-extra-payments-new-columns.webp” alt=”Google Sheets amortization schedule with extra payments \u2014 new Payment row and Extra Payments column added to the table” title_text=”New Payment row and Extra Payments column added to the table” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> This time, you\u2019ll calculate the periodic payment in the cell that corresponds to \u201cPayment\u201d. You can use the PMT formula, using the first three arguments, as shown below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/amortization-schedule-extra-payments-pmt-payment.webp” alt=”Google Sheets amortization schedule with extra payments \u2014 PMT formula in the Payment cell under the loan amount” title_text=”PMT formula in the Payment cell under the loan amount” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Include the value of the loan amount in the first cell of the \u201cLoan Balance\u201d column, by typing the \u201c=\u201d sign followed by the cell reference. Press \u201cEnter\u201d.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/amortization-schedule-extra-payments-start-balance.webp” alt=”Google Sheets amortization schedule with extra payments \u2014 first Loan Balance cell showing the $70,000.00 loan amount” title_text=”First Loan Balance cell showing the $70,000.00 loan amount” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\nWhat is a Google Sheets loan amortization schedule?<\/h2>\n
PMT, PPMT, and IPMT functions<\/h2>\n
PMT syntax<\/h3>\n
=PMT(rate, number_of_periods, present_value, future_value, end_or_beginning)<\/code><\/p>\n\n
PPMT syntax<\/h3>\n
=PPMT(rate, period, number_of_periods, present_value, future_value, end_or_beginning)<\/code><\/p>\n\n
IPMT syntax<\/h3>\n
=IPMT(rate, period, number_of_periods, present_value, future_value, end_or_beginning)<\/code><\/p>\nHow to create a loan amortization schedule in Google Sheets?<\/h2>\n
How to prepare the loan amortization schedule table format?<\/h3>\n
How to fill the loan amortization schedule using the PMT, PPMT, and IPMT functions?<\/h3>\n
How to create a loan amortization schedule with extra payments in Google Sheets?<\/h2>\n