How to Build an Amortization Table in Excel 2016

In this HowTech written tutorial, we’re going to show you how to create an amortization schedule in Excel 2016.
Don't forget to check out our main channel youtube.com/c/HowTech for more free how-to videos!
Join us on Facebook
Facebook.com/howtechtv
Our App
bit.ly/how-tech

To get started, you will need a rate, a number of payments, and the amount. Use the PMT() function with arguments in the respective order above for the Payment column. For each of the cells, you will need to use hit F4 with the cursor in each of the cell references when typing or selecting them as arguments for the formula. Type the number 1 in the Payment Number column as well.

For the Principal amount, use PPMT(). For the second argument (per) in PPMT, use the Payment Number cell and do not hit F4. We will not want this argument anchored.

For Interest, use IPMT().

Finally, for the Balance, subtract the Principal from the Amount. Neither argument needs to be anchored.

Select the first four columns excluding the Balance. Click and drag the bottom right-hand corner and autofill it down to however many payments are necessary.

In the second cell under the first Balance cell, subtract the Principal of the second Balance column from the first Balance cell. Neither of these cells should be anchored with F4.

Autofill the second Balance cell down.

The last cell will appear oddly (a zero value in accounting). To remedy this, right-click it and click Formal Cell.

Under Number, choose Currency instead of Accounting.

This is a HowTech tutorial, visit our website and watch our videos for more.