How Solo YouTubers Track Loans & Crush Interest (DIY Amortization Schedule)

Does Google Sheets Have a Loan Amortization Schedule? One of the biggest fears of small business or people just starting out is getting a loan. Filling out the paperwork for the bank or car dealership is scary. Come with us and take some control by learning how to make and use a loan amortization schedule to understand and keep track of your loan like we do. Let’s get Started! We like to hike. So, as we walk you through this schedule in Google Sheets I hope you don’t mind us sharing some views of the Grand Canyon. If you would like to download a copy of this loan amortization schedule head over to our website: https://www.smallbusinesscoo.com/loan... What is the PMT function in Google Sheets? The PMT function in Google Sheets or PMT is used to calculate the payment for a loan based on a constant interest rate, the total number of payments, and the loan amount. It's particularly useful when you want to determine the monthly payment on a car loan or any other type of loan with a fixed interest rate. How do you insert sequential numbers in Google Sheets? We are going to have a row for each month in our loan amortization schedule in Google Sheets. So the first column will be titled month. Under that I am going to type in 1,2,3,4 in subsequent rows. Now to insert sequential numbers I can use my mouse and highlight a7:a10 and then left click on the cell anchor and while holding the click dragging the mouse down the column. When I’ve reached a good spot release the click and the cells will populate with a sequential list of numbers. Here you can see me go to 40. You can also do this with dates and other numbers in a series. But, there is another way to insert a list of numbers. I can use the formula =+the cell above, in this case a46 + 1. Now in a47 you can see the number 41. Now I can copy and paste that formula as far down the sheet as I would like. For our loan amortization schedule we want to get to 360 months or 30 years * 12 months. Now to get back to the top let’s use the shortcut keys CTRL+Home How can I track loan payments in Google Sheets? The information we need to track loan payments are the Beginning loan amount, the payment, the interest amount, and the principal amount and the ending loan amount. Interest is the amount you pay the bank each month for borrowing money from them. But, the principal amount is the amount of money the loan was reduced by with the payment. As you make payments these columns in Google Sheets will help you understand where you’ve been with the loan, where you are going, and as important where you are. How do I create an amortization schedule for a loan? The Beginning amount column will be the loan amount outstanding before each payment for that month. Here in cell b7 we will type in =b1 to capture the amount financed. In cell b7 we will type in =$b$4, the strings are also known as dollar signs to capture the monthly payment amount calculated above and we will use the $ as we will copy that cell later. The interest formula in cell d7 will be =b7*(b2/12) and we will use relative cell referencing or strings to lock B2 . The result is $875.00. The principal will be the payment amount less the interest amount so type in cell e7 =c7-d7 which is $122.95. The ending amount is the ending amount of the loan after the payment so we will type in f7 =b7-e7 How can I tell how my loan payment changes over the life of a loan? The beginning loan amount for month 2 will be the ending loan amount from month 1. So, in cell b8 type in =f7. The payment doesn’t change so you will want to copy cell c7 to c8 or type in cell c8 =$b$4. Again the relative cell referencing so we always pick B4 if we copy and paste this cell. But, the interest amount will be calculated so in cell d8 type in =b8*($B$2/12). The principal amount will again be the payment less interest or type in cell e8 =c8-d8. The ending amount will be the Beginning Amount less the Principal or type in cell f8 =b8-e8. Now row 8 can be copied down the schedule. So, copy B8 - F8 and paste down every row until you get to the 360th month row or row 366. So, now you can see each month detailed in the Google Sheet. What does a loan payment tracker in Google Sheets do? So, now that we have the loan amortization schedule built in Google sheets let’s analyze what it can do. You can see in this example in column D that the loan starts off with monthly payments mainly going to interest. In column E only $122.95 is going to principal. Long duration loans like this will provide a lower payment but a lot of interest expense. Discover our blog: https://www.smallbusinesscoo.com/post... music by creatormix.com