(Quick note, if you had put 1 and 3 in the first boxes, it would have filled in the next as 5, 7, etc.
#CAR LOAN CALC WITH EXTRA PAYMENTS SERIES#
When you let go, Excel will automatically guess the series progression and fill in 3, 4, etc. Click and hold the lower right corner of the selection box and drag down. Fill in the cells for period 1 and period 2 and select the two cells. It would be tedious to type them all out, but Excel has a shortcut. We need a numbered list for all the payment periods.
Label columns for the payment Period, Interest amount, Principal (the amount going to pay down the debt), and the total Balance remaining. This table is going to have a lot of data that we don’t need to see on the main sheet. Start a new worksheet or move to an empty one in your workbook and call it “. Open the worksheet used last time, or download the example sheet from the last tutorial. So far, we have a great little calculation that gives us the monthly payment, but how much of that payment is going towards paying down the debt? How much is being wasted on servicing the interest charges each month? For that, we need to build a table of payments to break it all down. Today we’re going to take the simple tool we started with and start to add some of those missing features, including a deeper look at the interest payments and the balance over time. It was a great start, but like many of the calculators you can find online, it was missing a lot of details. Last time, we set out with the goal of building a car loan calculator that could tell us the monthly payment due and how much was owed. 2Calculating Interest and Principal Payments.