Track Your Next Loan in Excel

Back

About MrExcel

Consulting Services

Learn Excel Resources

Challenge of the Month

Member of the National Speakers Association

Track Your Next Loan in Excel

The tip in this show is from Special Edition Using Excel 2007.

While I have shown how to use the PMT function in past shows, today I want to show how to create an amortization table.

Build the input section of the worksheet as shown in A1:B3. The formula for cell B4 is shown in red in C4.
Enter the headings shown in Row 6.
The initial balance in D7 is a formula that points to B1.
In A8:A67, enter the numbers 1 through 60. (Tip: Enter 1 in A8. Select A8. Hold down the Ctrl key while you drag the Fill Handle downwards. The Fill Handle is the square dot in the lower right corner of the cell).
Use the PPMT function to calculate interest principal for any given payment in column B. Enter this formula in B8:
Use the IPMT function to calculate interest for any given payment in column C. Enter this formula in CB8:
The formula in D8 is =D7-B8.
Copy B8:D8 down to rows 9 through 67

One tip mentioned in the show is to replace column A with a reference to ROW(A1). You can change the formula in B8 to be =PPMT($B$3/12,ROW(A1),$B$2,-$B$1).

To download a sample, get Tip150.xls.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.