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
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:
http://www.tvmcalcs.com/calculators/apps/excel_loan_amortization
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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Circular references occur when a formula includes a reference to the cell in which the formula appears. Here's how you ...
Discover MoreWant to know the letters assigned by Excel to a particular column? Excel normally deals with column numbers, but you can ...
Discover MoreIf you want to figure out the sum of all cells that contain formulas, there are a couple of ways you can go about it. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-09-25 11:30:21
ocs
This is great, would there be any way to add a column for an additional principal payment?
2014-07-02 10:51:21
crozelle
i have an amortization shedule. i need a column for the escrow payment.
i need it to add to the full payment column or make another column for the full payment. how do i do this? i do not want to change calculation for principal and interest...
thanks!
2013-03-14 16:58:45
JMich
Whether the formula is right or wrong really depends on what type of interest compounding is being done. Some banks use a 360 day year for compounding purposes. The formula would look something like this (rate*(365/360)/12) This takes into account that you are making monthly payments and that you interest is being compounded daily over a 360 day year.
2012-04-25 03:53:20
K Mac
But how do you know its right?. Excel has certainly been a great product to introduce a lot of sophisticated formulas (financial and other) to users who may have not have had any theoretical tuition in these areas. Applying formulas such as above is great but can have poor outcomes if they encompass an error (Murphy likes excel a lot!). Your article should therefore have explicitly stated the need to extend the formulas down until the loan conclusion and that the amount owed one period after the last payment is indeed zero
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments