How to Build Your Own Amortization Schedule
ShareThis
Looking for a dull, but financially eye-opening home project? Great! Today, we're going to build an amortization schedule. All the tools you need are: 1) an electronic spreadsheet with PMT (payment calculation), addition, subtraction, division, and multiplication capabilities (I use Excel) and 2) this guide.
Let me go ahead and answer the questions you may have after reading this guide and completing your project:
Hey, I have a 30-year fixed rate (6%) mortgage loan of $200,000, but why is my payment so much higher?
This schedule does not include amounts paid to escrow, such as homeowner's insurance and property taxes.
Won't I have more equity built up over time?
For the purposes of this demonstration, equity is based on principal paid down; in real life, equity is calculated as the value of your home less the amount of outstanding loans (payoff amounts).
Can I put my numbers (mortgage loan amount, interest rate, etc). in this spreadsheet and figure out my own mortgage amortization?
Yes, though you will need to make adjustments if you do not have a fixed rate loan.
Where the heck is the PMT function?
In Excel, go to "Insert," click on "Function," select "Financial" from the categories, and then click on "PMT." You can also click on that E-looking key on your tool bar.
Why bother?
This guide is the first step in a series to show you how to pay down your mortgage faster than the original schedule (with a discussion of whether you should pay it down to follow in a subsequent post). It also mirrors an explanation given by UFirst in regard to its MMA (Money Merge Account) program (again, with further explanations in subsequent, equally intriguing posts), which a reader has asked about.
So.... (the following information is also in the attached spreadsheet, which has the full schedule to Year 30, Month 360 -- http://www.wisebread.com/files/fruganomics/How_to_Build_Amortization_Schedule.xls)
Given the following:
Primary Mortgage $200,000
Term in Years 30 (360 months)
Annual Interest Rate 6.00% (.50%/month or .005/month)
Payment $1,199.10 PMT(6%/12,360,-200000,0,0)
Annual Interest Rate/Number of Months in a Year, Number of Payments, Mortgage Balance (note: don't use commas to separate thousands)
Value at the End of the Term, Payments Are Made at the End of the Month
Build the Spreadsheet
Enter Formulas into Spreadsheet in this Order
Month 1
- Balance= Primary Mortgage...1
- Payment= Payment (PMT)...2
- Principal= Payment - Interest...4
- Interest= Balance x (Annual Interest Rate / Number of Months in a Year)...3
- Equity= Principal...5
- Total Interest= Interest...6
- Total Payments= Payment...7
Months 2-360
- Balance= Prior Month's Balance - Principal...1
- Payment= Payment (PMT)...2
- Principal= Payment - Interest...4
- Interest= Balance x (Annual Interest Rate / Number of Months in a Year)...3
- Equity= Principal...5
- Total Interest= Total Interest from Prior Month + Interest from Current Month...6
- Total Payments= Total Payments from Prior Month + Payment from Current Month...7
Month Balance Payment Principal Interest Equity Total Int. Total Pmt
1 200,000.00 1,199.10 199.10 1,000.00 199.10 1,000.00 1,199.10
2 199,800.90 1,199.10 200.10 999.00 399.20 1,999.00 2,398.20
3 199,600.80 1,199.10 201.10 998.00 600.29 2,997.01 3,597.30
4 199,399.71 1,199.10 202.10 997.00 802.40 3,994.01 4,796.40
5 199,197.60 1,199.10 203.11 995.99 1,005.51 4,990.00 5,995.51
6 198,994.49 1,199.10 204.13 994.97 1,209.64 5,984.97 7,194.61
7 198,790.36 1,199.10 205.15 993.95 1,414.79 6,978.92 8,393.71
8 198,585.21 1,199.10 206.17 992.93 1,620.96 7,971.85 9,592.81
9 198,379.04 1,199.10 207.21 991.90 1,828.17 8,963.74 10,791.91
10 198,171.83 1,199.10 208.24 990.86 2,036.41 9,954.60 11,991.01
11 197,963.59 1,199.10 209.28 989.82 2,245.69 10,944.42 13,190.11
12 197,754.31 1,199.10 210.33 988.77 2,456.02 11,933.19 14,389.21
13 197,543.98 1,199.10 211.38 987.72 2,667.40 12,920.91 15,588.31
14 197,332.60 1,199.10 212.44 986.66 2,879.84 13,907.57 16,787.41
15 197,120.16 1,199.10 213.50 985.60 3,093.34 14,893.17 17,986.52
16 196,906.66 1,199.10 214.57 984.53 3,307.91 15,877.71 19,185.62
17 196,692.09 1,199.10 215.64 983.46 3,523.55 16,861.17 20,384.72
18 196,476.45 1,199.10 216.72 982.38 3,740.27 17,843.55 21,583.82
19 196,259.73 1,199.10 217.80 981.30 3,958.07 18,824.85 22,782.92
20 196,041.93 1,199.10 218.89 980.21 4,176.96 19,805.06 23,982.02
21 195,823.04 1,199.10 219.99 979.12 4,396.95 20,784.17 25,181.12
22 195,603.05 1,199.10 221.09 978.02 4,618.04 21,762.19 26,380.22
23 195,381.96 1,199.10 222.19 976.91 4,840.23 22,739.10 27,579.32
24 195,159.77 1,199.10 223.30 975.80 5,063.53 23,714.90 28,778.43
Tagged: Real Estate and Housing, amortization, amortization schedule, interest, MMA, mortgage, mortgage acceleration, mortgage loan
Disclaimer: The links and mentions on this site may be affiliate links. But they do not affect the actual opinions and recommendations of the authors.
Wise Bread is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to amazon.com.
A lot of people avoid doing these calculations because creating this type of worksheets is just too time consuming. Thanks for stepping up and providing us a great tool!
You can also download a free "pretty" version of an amortization excel spreadsheet at Mugrosoft... er, Microsoft Templates. Requires Excel 2000 or higher.
http://office.microsoft.com/en-us/templates/TC010566201033.aspx
Might want to mention that this template (as well as the one you posted here will work just fine with the free office suite OpenOffice.org (http://openoffice.org
Thanks for the links. I have used openoffice.org before to translate Word files to PDF files - it's a great tooll and as you mention there are more free ones. I know there are a ton of calculation tools out there but I wanted to 1) show people how to build their own schedule and 2) (more importantly) begin the process of showing how to play with the schedule to determine the impact of extra payments and also show the logic (or lack thereof) behind the MMA - Money Merge Account programs; articles to follow.
Amazingly enough, they never taught about the financial section of functions in my Excel class in college (or at least I don't remember them!). A very timely and helpful article as my husband and I are about to buy our first house. Thank you. :)
Julie, I am 11 yrs into my 30 yr. mortgage, which originally was 153,000.00 after putting down 70,000.00. My interest rate is 7%. I was wondering; do I now look at my mortgage as a 21 yr mortgage? Then how do I then proceed? I have 1,000.00 of discrecianary income.
If you are designing an amortization schedule and then want to see how additional money on the principal will affect your payoff, then you need to use (or download) a 30-year mortgage schedule (not a 21-year or 19-year schedule as the calculations wouldn't work correctly using this method) and then go to year 11 to see where you are in your payoff and what you can do to accelerate the payoff if that is your goal. You can download this schedule (http://www.wisebread.com/files/fruganomics/DIY_acceleration.xls), replace $153,000 with $200,000 and replace the 6% with 7%. You'll also need to refigure the payment amount (I should have designed the spreadsheet a bit better; but the formula for you should use is =PMT(7%/12,360,-153000,0,0) which will equal $1017.91 (principal and interest only, I haven't included escrow amounts for property taxes). If you are in year 11, your balance should be between $125K and $128K. So, starting there (say in month 132), you can add principal amounts to the monthly payment. If you made an extra payment of $1000 in month 132, you'd pay the loan off three months early.
Let me know if you have more questions or if you need some validation of the numbers.
I created an online version. Amortization Calculator
Can anyone either explain how to do in Excel or show me a link to where I can calculate an amortization schedule that includes escrow for property taxes and home owners insurance?
I am 2 years into a 20 year loan and would like to create an excel spreadsheet to track the additional principal payments I have recently started sending in, but the amount I can send every month varies. I would like to track my progress and project in "real time" so to speak.
Thanks in advance.
I created a spreadsheet that will allow you to put in your own random additional payments to the amortization schedule. It's on my DIY Mortgage Acceleration post.
I didn't include escrow because it just goes in and out of the account, and will vary every year.
If you download the acceleration spreadsheet, you can enter your information. I would create a normal, non-accelerated schedule to test my calculations. Then you can add in your extra payments to the principal when you make them and see the results.
Hey Julie, just a minor suggestion on your spreadsheet... the "Original Payment" cell at C7 can take values from the previous entries. It might be helpful for spreadsheet novices so they don't have to manually change the spreadsheet payment formula. I'm using this:
=PMT($C$6/12,$C$5*12,-$C$4,0,0)
Thanks for putting this together! I sat through a mortgage accelerator sales pitch last Saturday (Mortgage Accelerator Plus), and it sounded too good to be true, so in my web research I found your blogs and spreadsheets. Great stuff!
BTW, if anybody is interested, the MAP and MMA, and I'm sure the "supercharge" programs don't perform any magic that I can see. The reality of the MAP program is that if I simply apply an extra $1000 every month to principle, I will pay off my mortgage in a third of the time. But, if I pass that same $1000 through a HELOC and apply my total paycheck to the HELOC and let a software program tell me WHEN to pay extra principal, then things sound much more supernatural, and of course the $1500 fee sounds like a bargain!
Personally, I'm leaning towards your advice of investing my extra cash, with a better return and the flexibility to pull it out if I need it.
Thanks again!
umm--too "time consuming to make a spreadsheet???!!"
come on!
actually, I shouldn't be surprised that there are people that think this way. But if you are planning on getting ahead financially, you need to rely on yourself, not just others, to calculate things in order to have good information to make decisions. Learning how to assemble an amortization schedule is pretty basic. Also, knowing how to assemble a credit card payoff schedule on a spreadsheet has come in handy for seeing exactly which card is the most beneficial to devote more of my payments to in various situations.
if you have debt, or or planning to invest (in other words, if you have and use money, or plan to have and use money) you NEED to know this stuff!
You can make your formulas in Excel more comprehensible by defining the important variables (interest and period) as names.
it's under Insert>Name>Define and then you type in "interest" as the name and at the bottom of the box you can either type in "=$A$7" or whatever cell you are using to define the interest rate.
you could also define interest (in the Name control box) as a constant by typing in "interest" and then defining it as "=6%".
The only problem with this approach on the current spreadhseet is that to change the interest rate you would have to go into the Names menu. So having "interest" refer to an absolute cell address (the kind with the $ signs before the letters and the numbers of the address, i.e. "$A$7" instead of, for example, "A7") makes more sense for this schedule.
Do the same thing for period.
Now your PMT formula, for example, can be written in forms like this:
=pmt(a8,(interest/12),period*12)
which is a heck of a lot more comprehensible when you go to read it.
you are lots of other things you can do with naming bsides this, but this is a small sampling. naming comes in most handy when you have more complex spreadsheets.
Thanks Steve for the tips. I have been used to creating spreadsheets for myself (only) that after making ones for Wise Bread readers, I kept seeing ways to make them more user-friendly.
If I want to put the IPMT and PPMT function for the following, what would the formula look like?
I'm having problems with IPMT functions and can't get the correct answer. Loan $750,000, Annual Rate 7.50%, Years 5, Periods per year 4, Rate per Period 1.88%, Number of Periods 20, Period Payment (PMT) is ($45,316) I need to be able to calculate the Interest Payment. Then I need to find the PPMT function to calculate the principal payment for the current payment period. Please help!
Thank you.
Do you have a 5 year loan with quarterly payments on a principal of $750,000? How many payments have you made to date?
anyone have trouble printing this schedule. Mine has blank vertical lines in it leaving it unreadable. Checked my printer and it prints other things fine. Anyone????
The spreadsheet was created and uploaded a few years ago, using MS Excel (2003). There may be an incompatibility with system files and a conversion could be useful if Excel spreadsheets are printing correctly.
Wow that is just a great spreadsheet, I will be showing this to my partner who is the one with the homeloan on her house and already paying more off right from the beginning and knows is able to reduce it more and not be as silly as some of her friends buying a place way beyond their means.
I do not have excel but I do have openoffice. You said the link on how to build amortization schedule works with openoffice. How, please input the link.
Thanks, Jo E.
The link to the schedule should show you how to build your own amortization schedule (though it doesn't create the schedule automatically) if you have a digital spreadsheet. It's been a while now since I have used OpenOffice but any spreadsheet should work as long as you can calculate your payment correctly. A great free source for spreadsheets now is Google Drive (formerly Google Docs).
Julie,
I have a Mtg. that is unique in that the Mortgage balance grows by a predetermined rate over the life of the Mortgage and the interest rate can vary as well. I can not seem to find anyone to run a Amortization schedule over the next 7 years when there is a call on the Mortgage.
L.E.
This is a great tool- thank you!!
Glad you find it helpful!