Are you looking to create an amortization schedule in Google Sheets? It’s a breeze with the built-in PMT, PPMT, and IPMT functions. But what if you want to include extra principal payments in your schedule? Don’t worry, we’ve got you covered with a different approach. In this tutorial, we’ll walk you through creating an amortization schedule in Google Sheets and show you how to incorporate extra principal payments. Let’s dive in!
Amortization Schedule Calculation in Google Sheets Using the Built-in Functions
You can leverage some of the financial functions in Google Sheets to easily create a loan amortization schedule. Whether your periodic payments are weekly, fortnightly, quarterly, or monthly, the formulas remain the same. Let’s break down the steps to create an amortization schedule in Google Sheets.
First, let’s gather the necessary input values:
- Loan Amount
- Annual Interest Rate of the Loan
- Duration in Years
Now let’s proceed with the following steps:
Step 1: Input Values in Amortization Schedule
Arrange the input values as shown in the image.
Step 2: Preparing Amortization Table Format
Create a 5-column table to enter the formulas. Here’s an example format.
The Formulas to Create an Amortization Schedule in Google Sheets
In this example, the loan period is set to 2 years in cell B3, which corresponds to 24 periodic payments.
Number of Payments in Sequential:
In cell C6, enter the ROW formula below to get the number of periods in sequential order.
=ArrayFormula(row(1:24))
Payment Calculation (Monthly Payments Including Interest + Principal Payment):
In cell D6, use the PMT formula to calculate the monthly payments.
=-pmt($B$2/12,$B$3*12,$B$1)
Drag this formula down to cell D29. Alternatively, you can use the array formula below:
=ArrayFormula(if(len(C6:C),-pmt($B$2/12,$B$3*12,$B$1),))
Interest Calculation Using IPMT:
To calculate the interest payment in each period, use the IPMT formula. Enter the formula below in cell E6 and drag it down to cell E29.
=-ipmt($B$2/12,C6,$B$3*12,$B$1)
Principal Payment Calculation Using PPMT:
To calculate the principal payment in each period, use the PPMT formula. Enter the formula below in cell F6 and drag it down to cell F29.
=-ppmt($B$2/12,C6,$B$3*12,$B$1)
Alternatively, you can use the basic formula =D6-E6 to calculate the principal payment.
Remaining Balance Amount:
In cell G6, enter the formula below to calculate the remaining balance amount in each period.
=$B$1-sum($F$6:F6)
That’s it! You’ve successfully created an amortization schedule in Google Sheets.
Tips to Change Amortization Schedule Payment to Weekly, Fortnightly, or Quarterly Basis
You might be wondering how to change the periodic payment in financial functions to a weekly, fortnightly, or quarterly basis. Well, it’s easier than you think!
Here’s how you can adjust the interest rate based on different periodic payments:
- Weekly Interest Rate = Yearly Interest Rate / 52
- Fortnightly Interest Rate = Yearly Interest Rate / 26
- Monthly Interest Rate = Yearly Interest Rate / 12 (used in the formula in cell B5)
- Quarterly Interest Rate = Yearly Interest Rate / 4
You can also multiply the loan period in years (cell B3) by 52, 26, 12, or 4 to adjust it to your desired periodic payments.
Creating an Amortization Schedule With Extra Principal Payments in Google Sheets
If you want to make extra principal payments, the built-in functions in Google Sheets won’t be sufficient. You’ll need to follow a different approach, but don’t worry, it’s not complicated at all.
In this case, you’ll still follow the same input values and format as before, but you’ll need to include a new column for the extra principal payments in each period.
Here’s a quick breakdown of the formulas involved:
Sequential Numbers in the Range B10:B26:
Enter the numbers 1 to 17 in the range B10:B26. This is because you intend to make $500.00 as extra principal payments every month, with the final payment occurring at the 17th month.
PMT Formula to Calculate the Periodic Payment in Cell B5:
Use the PMT formula in cell B5 to calculate the payment amount.
Manual IPMT with IF Logical:
Calculate the interest payment using a custom formula in cell D10. Use the formula =IF(len(C10),G9*$B$2/12,0).
Manual PPMT with IF Logical:
Calculate the principal payment using a custom formula in cell E10. Use the formula =IF(len(C10),MIN(C10-D10,G9),0).
Remaining Balance Amount:
Calculate the remaining balance in cell G10 using the formula =IF(G9>0,G9-E10-F10,0).
That’s it! You now have an amortization schedule with extra principal payments in Google Sheets.
If you want to explore a fully functional example sheet, you can check out this link.
Creating an amortization schedule in Google Sheets and incorporating extra principal payments is now at your fingertips. Feel free to explore and customize these formulas to suit your specific needs. Happy crunching!
