Written by Allen Wyatt (last updated June 29, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
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:
http://office.microsoft.com/en-us/templates/TC001056620.aspx
You can also find a very good explanation of amortization schedules at this page:
http://www.tvmcalcs.com/calculators/apps/excel_loan_amortization
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11627) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
If you have a bunch of ZIP Codes or part numbers in a list, you may want to "condense" the list so that sequential series ...
Discover MoreThe formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can ...
Discover MoreYou can use the Alt+Enter keyboard shortcut while entering information in order to force your data onto multiple lines in ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments