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.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
When you discover that there is an invalid reference in a workbook, you can have a bear of a time tracking down the ...
Discover MoreSometimes making sure that a reference in a formula doesn't get changed is not as simple as putting dollar signs in front ...
Discover MoreIf you have a range of values that can be either positive or negative, you might wonder how to determine the largest ...
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