


This is the first of a two-part tutorial on amortization schedules. In such instances you would be better to look for a ready-made amortization template.Are you a student? Did you know that Amazon is offering 6 months of Amazon Prime - free two-day shipping, free movies, and other benefits - to students? Click here to learn more It doesn't take into account varying interest rates, refinancing, non-monthly payments, additional payments, escrow amounts, or any number of other variables. Remember that I said that this creates a simple amortization schedule. Add any explanatory labels desired in the ranges A1:A3 and A5:E5.Copy row 7 down as many rows as you need.This cell now contains the new principal balance for your loan. Change cell B7 to the following formula: =B6+C6.

Change cell A7 to the following formula: =A6+1.In cell E6 put this formula: =PMT(Rate/12,Term,Principal).(The IPMT function returns the amount of interest for a given payment.) This is the amount you will pay in interest in this payment. In cell D6 put this formula: =IPMT(Rate/12,$A6,Term,Principal).(The PPMT function returns the amount of principle for a given payment.) This is the amount you will pay toward your principal in this payment. In cell C6 put this formula: =PPMT(Rate/12,$A6,Term,Principal).The amount you put into cell B3 should now also appear in cell B6. In cell B6 put this formula: =Principal.Give cell B3 a name, such as "Principal".In cell B3 put how much you are borrowing.In cell B2 put the number of months you need to pay.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. At its simplest, a mortgage payment consists of two parts: principle and interest. It actually is fairly easy to come up with the right calculations. Problem is, she doesn't know enough about finance to know which of the financial worksheet functions she should use to do the calculations. Mary would like to use Excel to create an amortization schedule for her home mortgage.
