Mr. Tim’s Mortgage Machine
The “How much loan can I afford?” window
This spreadsheet displays affordability (“How much can I afford?”) and amortization (“What will my loan cost?”) windows.
The affordability (“How much can I afford?”) plot lets you determine the amount of a loan that you can qualify for, based on interest rates, your current income and the amount of debt you already carry. Affordability is based on typical rules for loan qualifications, but this spreadsheet also allows you to make calculations based on your own comfort level.
This window allows you to input values for your annual income, your current monthly debt, and the typical taxes and insurance rates in your locality. It then calculates the maximum monthly payment for which you can qualify. You can adjust this rate to whatever value you feel comfortable with, and you can also indicate the amount of money you can afford for a down payment.
Once you have entered all of the information, the spreadsheet displays a plot showing the loan amount you can qualify for, for a range of interest rates.
The “What will my loan cost?” window
The amortization (“What will my loan cost me?”) plot displays the total amount you will spend on your loan over its entire life. This plot shows things like monthly payment, predicted monthly payments for future months (for adjustable loans), amount still owed, house value, equity (difference between house value and amount still owed), and total profit or loss if you sell the house and payoff your loan before its maturity.
This window allows you to input values for your loan’s interest rate, the value of the house at time of purchase, the percentage down payment made when the house is purchased, the yearly tax and insurance rates assumed for your house, and the yearly amount by which you assume your house will appreciate or depreciate over its life.
Other types of loans supported by this tool
The types of loans supported are 30 yr and 15 yr fixed-rate loans, fixed-rate for a time period that you specify, adjustable-rate loans with 30 yr, 15 yr or variable lifetimes, and 5/1 and 3/1 adjustable-then-fixed loans. Amortization is calculated based on monthly payments, quarterly payments, bi-weekly payments, or monthly plus an extra year-end payment.
The amortization results can also be shown in tabular form rather than as a plot. Every scenario that the user plots can be saved as a separate plot, so you can examine and compare multiple scenarios.
This spreadsheet uses custom macros, so you will have to enable Excel to run macros when you are running it. We do not include any malicious macros in our tools, but it is still a good idea to protect your computer by examining macros before running them. You can do this using the “Edit Macros” function, which you will find in the TOOLS -> MACRO -> MACROS… window in earlier versions of Excel, or in the DEVELOPER -> CODE -> MACROS window in Excel 2007.
To download Mr. Tim’s Mortgage Machine, click here. (You don’t need to right-click. Just use the normal “left” button).
The zip file includes the Excel Spreadsheet and a .pdf file with complete instructions.
Usage of this spreadsheet is allowed under the terms of our Freeware Usage Policy.