Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Creating an Amortization Schedule.
by Allen Wyatt
(last updated April 12, 2019)
Mary would like to use Excel to create an amortization schedule for her home mortgage. Problem is, she doesn't know enough about finance to know which of the financial worksheet functions she should use to do the calculations.
It actually is fairly easy to come up with the right calculations. At its simplest, a mortgage payment consists of two parts: principle and interest. Given some basic information such as how much you are borrowing (your principal), what your interest rate is, and how many monthly payments you need to make, you can then come up with your amortization schedule. Try this out:
Figure 1. A simple amortization schedule.
Remember that I said that this creates a simple amortization schedule. It doesn't take into account varying interest rates, refinancing, non-monthly payments, additional payments, escrow amounts, or any number of other variables. In such instances you would be better to look for a ready-made amortization template. There are any number of them available online, including these from Microsoft:
Microsoft also provides some built-in amortization templates that may have been installed when you installed Excel on your system. If you are using Excel 2007, follow these steps:
If you are using Excel 2010 the steps are a bit different:
You can also find a very good explanation of amortization schedules at this page:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11628) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Creating an Amortization Schedule.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Need to figure out if a value is outside of some arbitrary limit related to a different value? There are a number of ways ...Discover More
If you need to randomly match up items in two lists, there are a variety of techniques you can use. Here are a couple of ...Discover More
If you have a mixture of numbers and letters in a cell, you may be looking for a way to access and use the numeric ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.