I keep getting requests about how to do amortizations for many more complicated scenarios, and 4 out of 5 times, the answer is probably already sitting in front of you. Just about every PC or Mac has a spreadsheet application of some sort installed on it. Typically it is Microsoft Excel from within Office, but if you do not have a copy, you can grab LibreOffice for free which is just as good. You can also use an online spreadsheet from Google or Microsoft for free. All of these have a built-in PMT type function that will calculate your monthly payment given a loan balance, interest rate, and the number of terms. Here is an example of how to make a spreadsheet that allows variable prepayments:

       A              B               C                D             E
1   Number of Years  30 
2   Interest Rate    8.0%
3   Principal        100000
4   Payment          =-PMT(b2/12,b1*12,b3)
5
6   Balance          Interest         Principal Paid   Pre-Paid  New Balance
7   =b3              =a7*$b$2/12      =$b$4-b7           200     =a7-c7-d7
8   =e7              =a8*$b$2/12      =$b$4-b8           300     =a8-c8-d8
9   =e8              =a9*$b$2/12      =$b$4-b9           150     =a9-c9-d9

Here the values in column D "Pre-Paid" can be added by the user to show how much additional principal is paid off that pay period. You can then add up the interest values in column B for each year to see how much tax deductible interest you have paid.

To do ARM's, you use the same idea, but stop the table after X payments and shift to a new interest rate which generates a new $b$2 value for column B interest values as well as a new payment (not $b$4 any more in column C). You can do all sorts of different scenarios this way by having the interest in a single cell you can change and watch the whole amortization table shift.

• For Borrower: Viewable Only version
• For Lender: Lender Edit version