Here you’ll learn how to track principal and interest payments on Notes Payable in Google Sheets.
I am using Google Sheets for a specific reason.
- One: If you use Excel instead of sheets, what works in Sheets will also work in Excel. So if you value privacy, you can follow along in Excel also.
- Two, more people have been using Google sheets recently for their spreadsheets.
- Third, you can share a file with your bookkeeper or vice versa, and it is updated in real time. That way, you don’t need to send a new version every time you make a change.
If you want to use Google Sheets but value privacy, you can just use the loan number from your lending institution as the file name, or something that people can’t automatically connect to you. You don’t even need your name on it.
These concerns aside, let’s get into how to track principal and interest payments.
You’ll need four rows, or types of information: The Loan Amount, the Loan Interest Rate, the Term of the loan (in yrs), and the number of Installments per year (usually monthly, or 12). Let’s assume you’re the farmer from my last blog post, borrowing $40,000 over 2 years at 5.125% interest to fence a 20 acre piece of land. The loan amount is $40,000, the interest rate is 5.125% or 0.05125, and the installments are 12 (monthly).
Let’s do a quick formula called pmt, which means payment. In a new cell below our rows, we’ll write ‘installment’ then in the next column, we’ll type an equal sign followed by ‘pmt(
Then click on Rate (cell B2), type ‘/’ for dividing, then click Installments (cell B4), then a ‘,’ comma, followed by term (cell B3), then a ‘*’ for multiplying, followed by Installments again (cell B4), then another ‘,’ comma, and the ‘-’ minus sign followed by our loan amount (cell B1), then a ‘,’ comma and 0 for the future value of money, then finally a ‘,’ comma, and a value of 0 if you didn’t have to pay any down payment, or 1 if you did. Then a ‘)’ closed parenthesis. If at any time you want help, inside the cell where you’re typing, just to the left side there is a question mark. Click the question mark to know what kind of data you are entering at each step.
Complicated, I know, so I’ll show a picture of the equation.
I get $1,757.10. I know this is right based on the calculator I used in the previous blog post.
Now we’ll create the schedule.
In cell A9, type in ‘0’. This is day 1 of your loan.
In cell A10, type ‘=sequence(B3*B4)’ then hit enter. This multiplies the years of your loan by the number of months, and gives you the numbers 1-24. If the last equation gave you PTSD, you can just write in 1 through 24. You can also write 1, then grab the circle on the lower right side of the cell with your mouse and drag down until you have 24 cells selected, and let go. It should automatically list that far numerically.
Let’s name the rows of our schedule. Installment, Balance, Installment, Principal, and Interest.
Under balance (Cell B9), type = then click B1. That puts our balance at the beginning of the schedule into the table.
Two rows under ‘Installment’ (Cell C10), type ‘=’ then click cell B7, then hit the F4 key. This turns it into an absolute cell reference. With absolute cell references, you can click and drag anywhere on different equations referencing that cell, and that part of the equation will always stay the same. That will be useful later. If you don’t have an F4 Key, you can make an absolute cell reference manually by putting dollar signs in front of each row and column designation ‘$B$7’ into cell C10.
Here we’ll enter another payment formula, but this time it’s called ipmt. This isolates only the part of our payment that is interest. Go to cell E10 (under ‘interest’ and to the right of ‘Installment’. Enter this equation: =ipmt($B$2/$B$4, A10, $B$3*$B$4, -$B$1, 0, 0)
Notice the dollar signs are for absolute cell references. You can do this by hitting F4 after clicking each cell or typing in the dollar signs. Hit enter after the equation is typed in.
You’ll get the first interest payment of $170.83. Again, the previous blog post used a debt amortization calculator that comes up with the same result.
To find out what the principal is for that first period is super easy. Click cell D10, type ‘=C10-E10’ and hit enter. You’re just subtracting your monthly payment by the interest to find the principal.
You do a similar action to find how much your balance drops after that first payment. Click cell B10, type ‘=B9-D10’ then hit enter. It subtracts your principal payment from your starting balance.
Now highlight B10-E10, click and hold the circle on the lower right side of the highlighted region and drag it down to row 33, where the number 24 is entered in column A. Then release the mouse button. And Bob’s your uncle! Your amortization table is complete. You know you’ve finished paying off the loan when B33 shows a value of 0.
Great thanks to Brent Coleman at his youtube channel for tutorials like this.
To have me as your bookkeeper, and have me keep track of loan payments using accounting software and spreadsheets like this, schedule a call with me. The call is free, and you’ll learn how I can save you time and money tracking things that are crucial to your business, and giving you actionable information. Typically, I can save business owners like you 80+ hours a year compared to doing the bookkeeping yourself.
Get the spreadsheet here: