An amortization schedule helps you track loan payments, showing how much goes toward interest and how much pays down the principal. Let’s walk through creating an amortization schedule using Google Sheets, a free spreadsheet program you can access online.
Why Is an Amortization Schedule Tracker Important?
An amortization schedule tracker is important because it provides a clear and detailed roadmap of your loan’s lifetime, breaking each payment into principal and interest. This transparency allows borrowers to see exactly how much of their payment is reducing the loan balance versus going toward interest, offering a deeper understanding of the pace at which they build equity.
Additionally, it aids in financial planning by showing when the loan will be paid off and how extra payments can impact the schedule, potentially saving significant interest payments over time. An amortization schedule tracker turns what can be a confusing array of numbers into a useful tool for managing personal or business finances more effectively.
Step 1: Open Google Sheets
Open up a new Google Sheets spreadsheet. Once you have it open, give your sheet a title by clicking where it says “Untitled spreadsheet” in the top left corner and typing in a name like “Amortization Schedule.”
Step 2: Input the Loan Information
Before we proceed on the columns, let’s have the basic information of your loan first. At the top of the spreadsheet, select cells and put labels, such as:
- Loan Amount
- Interest Rate
- Loan Term (Years)
- Loan Period (Months)
- Monthly Payment
To easily calculate how many months you need to pay, click the cell then =C4*12 (assuming that C4 is the cell where the loan term (years) if found.
Step 3: Set Your Column Headings
At the top of your sheet, you’ll want to label your columns. Click on the first cell in each column and enter the following headings:
- Payment Date
- Starting Balance
- Payment
- Principal
- Interest
- Ending Balance
- Period (In Month)
- Total Pricipal
- Total Interest
Step 4: Input the Payment Information
Now, let’s start entering your payment information, such as Payment Date and Payment Period.
Then, let’s put the formulas for the payment information, such as Starting Balance and Ending Balance.
For the Starting Balance, click the cell and input =C2 (assuming that C2 is the cell where the value of your loan amount is found.)
Then for the second row under your first entry of Starting Balance, enter =G9 (assuming that G9 is the first cell under the Ending Balance column.
For the Ending Balance, use =C9-E9 (assuming that C9 is the first Starting Balance and E9 is the first Principal.)
Step 5: Calculate Your Monthly Payment
Next, you’ll want to calculate your monthly payment. You can use the PMT function in Google Sheets:
=ROUND(-PMT(C3/12,C5,C2),2)
Go to Payment column, then enter =$C$6 (assuming that C6 is your Monthly Payment field.
To automate the payment column, click on the next cell under your first payment and enter
=MIN(C10+F9,$D$9)
Assuming that C10 is the second cell under your Starting Balance, F9 is the first cell under Interest column, and D9 as the first payment.
To lock your formula, make sure to use $ or click F4 key on D10. This will make it easier to drag and paste the formula for the rest of the column.
Step 6: Calculate Principal and Interest for Each Payment
Under Principal category, use the formula:
=D9-F9 (assuming that D9 is your first payment and F9 is your first interest entry.)
To get the Interest, use the formula:
=ROUND(C9*$C$3/12,2) (assuming that C9 is the Starting Balance, C3 is the Interest Rate field)
Now, drag down the formulas to the rest of the fields.
Step 7: Keep Track of Total Principal and Total Interest
To track your Total Principal, enter =E9, assuming that E9 is the first entry under the Principal category. Then, on the second cell under Total Principal, enter the formula =I9+E10, assuming that I9 is the first entry under Total Principal and E10 is the second entry under the Principal category.
Use the same formula to get the total interest. Then, drag the formula down to the end of your amortization schedule.
Get a Free Amortization Schedule Template
Get a copy of the free Amortization Schedule Template. I’ve populated some cells as examples, but you can edit them as needed.
Final Thoughts
With this amortization schedule template, you can easily track how each payment is split between interest and principal, as well as your progress in paying down the loan. Google Sheets is a powerful tool can help you plan your finances effectively and understand how your payments are allocated over the life of your loan.
Frequently Asked Questions
How do I ensure the formulas update correctly when I add new rows to my amortization schedule?
When you’re dragging down formulas to new rows, using relative cell references (e.g., A1, B1) is important. Google Sheets will automatically adjust these references relative to the position of the formula. However, if you need a cell reference to remain constant (like the annual interest rate), make it an absolute reference by adding dollar signs (e.g., $B$1).
What if I have additional payments or irregular payments?
To account for additional or irregular payments in your amortization schedule, you can adjust the formulas in the Principal column to subtract any extra payment amounts. You’d add a new column to incorporate these amounts into your schedule and adjust the formulas accordingly.
Is there a way to calculate the principal amount paid in a specific month?
Yes, the formula =PPMT(rate, period, number_of_periods, present_value) lets you calculate the principal amount paid in any given period. Just like with the IPMT function, you’ll need to provide the monthly interest rate, the period number you’re interested in, the total number of payments, and the principal value of the loan.
