How to Create an Amortization Schedule in Google Sheets Using the Built-in Functions

An amortization schedule is a table that breaks down the payment amount of a loan into its individual components - interest and principal - over a set period of time. In this article, we will explore how to create an amortization schedule in Google Sheets using the built-in PMT, PPMT, and IPMT functions.

To start, you need to prepare your amortization table format. You will require a 5-column table with the following headers: Period, Payment, Interest, Principal, and Balance. The period column represents the number of periods in sequential order, while the payment column calculates the monthly payments including interest and principal payment.

In cell G6, enter the below formula and drag down to calculate the principal payment:

`=-ppmt($B$2/12,C6,$B$3*12,$B$1)`

Alternatively, you can use an array formula to convert the above PPMT formula to an expanding array formula. This will allow you to fill all the 24 periods with the PMT value.

In cell D6, enter the following formula and drag down until it reaches cell D29:

`=ArrayFormula(if(len(C6:C),-pmt($B$2/12,$B$3*12,$B$1),))`

This formula uses an array formula to calculate the monthly payments. Since the payment is the same for each period (constant periodic payments), this formula will fill all the 24 periods with the PMT value.

To create an amortization schedule in Google Sheets, follow these steps:

Step 1: Input Values in Amortization Schedule

Enter the values as per the below image:

| --- | --- | --- | --- | --- |

| 1 | | | | |

| 2 | | | | |

| ... | | | | |

| 24 | | | | |

Step 2: Preparing Amortization Table Format

Arrange the values as per the above image.

You can also create an amortization schedule with extra principal payments in Google Sheets. However, this may require a different approach than the one described above.

In conclusion, creating an amortization schedule in Google Sheets using the built-in PMT, PPMT, and IPMT functions is a straightforward process. By following these steps and formulas, you can easily create an amortization schedule that breaks down your loan payments into their individual components over a set period of time.

Google sheets amortization schedule