Using Excel's Financial Functions

For versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007

Excel Loan Amortization, Loan amortization with Extra Payments

This tutorial demonstrates how to use Excel to create loan amortization table. It starts with a basic table using the IPMT and PPMT functions, and then goes through an advanced example that shows the effects of adding additional principal to the monthly payment. To simply calculate a base loan payment use the PMT function. See the PMT tutorial.

How Does Loan Amortization Work

Generally when you borrow money there is a cost associated with the priviledge known as interest, so not only do you pay back the money you borrow, but you also pay interest. The amount of interest paid is based on the amount of money you currently owe, or the principal balance. In other words, you pay the most interest with your first loan payment and gradually pay less and less as the loan matures.

Conveniently, the entities you borrow money from arrange things so that you make the same payment every month. It is possible to pay off the loan faster and reduce the interest paid by make extra payments or adding a little extra each month.

For example, in this tutorial you borrow $360,000 for 30 years at 5.875% annual interest. This makes your payment about $2300 per month. By adding $200 to your monthly payment, you will pay off the loan is just over 24 years and save $92,000 in interest.

You can download the file used in the video here