Spreadsheet programs like Google Sheets are extremely versatile and allow you to do many useful things.
You can do simple things like creating budgets, graphs, charts, and do simple math formulas. Or you can do more complex things like using the built-in financial functions in Google Sheets to create amortization tables.
Creating a loan amortization schedule is useful because you can look at how monthly payments affect the cost of a loan such as a mortgage, car loan, or other kinds of loans.
In this tutorial, I will show you how to create a loan amortization schedule in Google Sheets.
Table of Contents
What is an Amortized Loan?
An amortized loan is a type of loan that has regularly scheduled payments to pay down a loan’s principal and interest on the loan.
Any payment made for an amortized loan will first pay off the interest for the period and then after the interest is paid any leftover amount will then being to reduce the principal.
The most common kinds of amortized loans are home loans, auto loans, personal loans, and debt consolidation.
Amortized Loan Schedule
Amortization tables are useful because they can help you understand how your loan will be repaid.
An amortization schedule will show you each monthly loan payment and how much of that payment goes to paying the principal and how much goes towards paying the interest over time.
So an amortized loan schedule will have:
- Scheduled Payments – The regular monthly payment amounts to be made on the loan
- Principal Payment – The amount of the payment that goes towards the principal
- Interest Payment – The amount of the payment that goes towards paying the interest on the loan
Amortized loan tables can help you understand loan repayments much better, which is why it is very useful to learn how to make your own.
In the next section, I will show you how to create your own schedule in a spreadsheet.
How to Create Amortized Loan Schedule in Google Sheets
The first thing we need to do to create our loan schedule in our spreadsheet is make a basic template that we can fill in with the relevant information.
You will need to have an area in your template to enter:
- Principal amount
- Interest rate
- Loan term
- Loan start date
- Period
- Date
- Monthly payment
- Interest payment
- Principal payment
- Loan balance
Here is an example of how this template can be set up:
Loan Information
Once we have our template made, we can begin filling in the important information.
We will start with filling out the information in the top part of the sheet.
We need to enter the:
- Principal amount – the total loan amount that needs to be paid
- Interest rate – the interest rate for the loan
- Term (in Years)– the loan repayment period
- Start Date – the date that the loan repayment starts
I will fill in these values in my example with a home loan of $252,000 to be paid over a 30 year period with an interest rate of 2.875%.
Once the basic loan information is entered, we can begin filling out the rest of the table.
Period
The first thing we will enter is the period column. This will just be a serial number for each payment that is made. You can start your first line with 0, and then increment from there down your spreadsheet.
So you will end up with something that looks like this:
Date
The next column we will fill out in our table is the Date column. The first date will be the same as your starting date, and then each date afterward will increment by one month
On our first row, we can copy and paste the start date. To increment the month on the following rows we can enter a basic formula using the EDATE function.
In the second row, enter this formula to increment the month:
=EDATE(B7,1)
Your spreadsheet should now look something like this:
Monthly Payment
Next, we will calculate and enter the monthly payment on each row.
To do with, we can use the PMT function. This function will calculate the monthly payment that needs to be made on the loan.
For our loan schedule, will be using the PMT function with this basic syntax:
=PMT(rate, number_of_periods, present_value)
- rate – this is the interest rate
- number_of_periods – this is the number of payments that will be made
- present_value – this will be our principal amount
So in my spreadsheet, the formula will be:
=PMT($B$2/12,$B$3*12,$B$1)
Some important things about this formula are:
- All the cell references are locked using the dollar sign ($) symbol because the reference to the cell does not change as our rows change. We want to make sure we are taking from the same values every time
- Our interest rate is divided by 12 because there are 12 months in a year
- Our number of periods is multiplied by 12 because there are 12 months in a year
After entering this column here is how the spreadsheet looks now:
You can start your first row at 0 since a payment doesn’t need to be made for the first period. For the following rows, you will use the same formula and copy and paste it all the way down your table.
It is normal for your values in this column to be negative because it shows that the payments are outgoing.
Interest Payment
Next, we can begin to fill out the interest payment column in our spreadsheet. This will show the amount of the monthly payment that goes towards paying interest.
To calculate the interest payment we will be using the IPMT function.
The IPMT function will calculate the interest payment on an investment that requires payments that are constant and an interest rate that is constant.
The syntax of the IPMT function is:
=IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])
- rate – the interest rate
- period – this is the amortization period. In other words, this is a serial number for the payment that is being made
- number_of_periods – the number of payments that will be made
- present_value – the value of the annuity (the principal amount in our example)
- future_value – this is an optional argument. This is the future value remaining after the last payment is made
- end_or_beginning – this is an optional argument and will be set to 0 by default. This will determine whether payments are made at the beginning or end of each period
In our example, my formula for IPMT will be:
=IPMT($B$2/12,A8,$B$3*12,$B$1)
Here is how this looks in my spreadsheet:
In the example above, make sure to lock every cell reference except for the period argument (2nd argument). This one needs to change as you increment rows, so you want to make sure this is not locked in the formula.
Your first row can stay at 0 since no payment is being made. Put your formula into your second row and copy and paste it all the way down.
Each period you should begin to see the interest payment becomes lower and lower over time.
Principal Payment
Next, we will fill out the principal payment column.
To do this we will enter a pretty simple formula into our spreadsheet.
In the first row, put a zero since no payment is being made.
Then, in the second row, you will be entering this formula:
(Monthly Payment – Interest Payment)
In our example this will be:
(C8-D8)
This will look something like this:
Each period you should see that as your interest payment goes down over time, your principal payment increases.
Loan Balance
Lastly, we need to fill out our loan balance column.
This is the total amount of the loan that still needs to be paid.
To enter this into our spreadsheet, in the very first row we need to put the entire principal amount.
In this example, I will just put a cell reference to the principal amount in the spreadsheet like this:
After your first row is filled out, you need to enter a formula into the second row to get the rest of the loan balance column entered.
In this example, I will enter this formula into my sheet:
=E8+F7
This will subtract the principal payment for each period from the total loan balance. We are adding this formula because the principal payment value is already negative. So to subtract each principal payment from the loan balance, we just need to add it to the previous loan balance.
After you have this formula entered into the second row of your table, you can copy and paste it all the way down your sheet.
This is how this should look:
Closing Thoughts
That is it! If you have followed along this far, you have now created your very own amortization loan schedule in your spreadsheet.
This will help you better understand the interest and principal payments for a loan over time. You should see that interest payments go down and principal payments go up over time.
At the end of your table, the loan balance should reach zero.
If you want you can also sum up the interest payments and principal payments to get an idea of the total amount that will be paid on the loan throughout it’s life.
More Google Sheets Tutorials:
How to Use the NPV Function
How to Make a Budget
