Attached is an app with an embedded iterative macro. That's where an iterative macro can help and save you a lot of time. If this had been a home loan with a 30-year term (360 months), you would only need to update this spreadsheet 359 more times! Just do that 59 more times, incrementing 'Loan Payment Number' by 1 until you reach 60 and you will create your very own amortization schedule. Payment applied towards interest (payment number 1 of 60): Payment applied towards principal (payment number 1 of 60): You can even take it a step further and calculate how much you'll pay in interest (=IPMT) and principle (=PPMT) for your first payment and each payment thereafter. To calculate your payment, you use the =PMT function.
Excel has a couple of functions that can help you. Before visiting with a banker you'd like to run some numbers and see if payments fit within your budget. Let's say you've got your eye a new car or home.
