excel data functions and amortization schedules

Now, if you need the table for audit purposes it may not be possible for you to do without an amortization table completely. On older versions of Excel, you won’t have access to these functions but if you’re using Microsoft 365 or Excel 2019, then these functions are available and can potentially serve as replacements for an amortization table. Should you use these functions instead of an amortization table? This matches what I would get by adding the interest payments in my amortization table over the same period, this time to the penny. This tells me that the cumulative interest payments during the first 10 payment periods is $3,887.87. It has the same arguments as the CUMPRINC calculation, so the formula will look very similar to what’s above: If you want to calculate the total interest payments, then that’s where you can use the CUMIPMT function. You can start from period 13, or the second year, and so on. The function gives you the flexibility to specify which periods you want to extract and so you aren’t limited in just tabulating the totals for the first 10 periods or starting from the beginning. When added to $100,000, it nets out to a balance of $85,016.64 - within just a few cents of the amount on the amortization table. To calculate the cumulative principal payments, I’ll enter the formula with the following arguments: The function takes on the following arguments: We can use the CUMPRINC function get to that total as well.

In the amortization schedule, we can see that the ending balance of the $100,000 loan by the end of period 10 is $85,016.67. However, there’s another way to arrive at those totals, and that’s using two functions that are available in the newest version of Excel: CUMIPMT and CUMPRINC.

You could use the table to determine what the balance is at the end of period 10 or to add up all the interest payments up until that point. When you set up an amortization schedule, you’ll track the balances, interest, and principal payments.

First, let’s start with what a typical amortization schedule looks like. However, you don’t have to create a full schedule to get these values and below I’ll show you two functions that can get you that information quickly and easily. An amortization table is a useful tool when you need to calculate interest payments, principal payments, and to track the balance that’s owed on a loan.

Excel data functions and amortization schedules