- Latest
- Views
- Loans
Formula/Algorithm Formula Derivation FICO/LTV Calculator What's Missing? Generic Loan Amortization Loan Schedule PDF Generator HELOC Cost Calculator Payoff vs. Borrow Blended Rate Calculator Prepayment Mortgage Reduction Payment per $1000 borrowed Rule of 78 Loan Calc Simple Loan Payment Calc Loan Amortization Schedule Loan Schedule on a Spreadsheet
- Retirement
- Saving/Investing
- Taxes
- Stats/Numbers
Cholesterol Ratios College/University Endowments 2014 Middle Class Income Calc Income/Wealth Percentile (2010) Household Income Percentile (2007) Wealth/Savings Comparison (2006) Stanley Wealth Equation Hourly/Weekly/Monthly Income Calc Average/Cumulative Return Calc College Tuition Cost Calc BMI/BMR Calorie Calc Feline to Human Age Calc
- Games
- ...More

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 on it, (typically Microsoft Excel within Office) and they are very good
tools for doing mortgage analysis. Most of them 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.

Here is hcmort.wks a very basic Lotus 123 compatible spreadsheet following the example above. Just about every spreadsheet made by mankind (and Microsoft too!) will accept WKS format spreadsheets. See if this works for you.

This example above uses the Excel version of **PMT**. Yours may differ.

Here is a Excel 5.0 module (Windows or Mac) for doing Canadian mortgages that I found at http://www.knowtech.ca/prod06.htm

Here is a shareware Excel 5.0 for example: Windows Version Mac Version

Mac users can also get a demo ClarisWorks amortization spreadsheet here which should work fine in Excel too.

If for some reason you do not have a spreadsheet like Excel or a similar office suite, you can download and use LibreOffice free for any platform. Windows or Linux GNOME users who do not want to use the full LibreOffice suite can use gnumeric too.

If anybody wants me to write up a simple Excel spreasheet for your particular case, I will do so for a small PayPal or check payment. Contact me if you are interested.