Your monthly mortgage payment significantly impacts your budget. A lower monthly payment means you can spend more to pay down other debt, maintain your car and home, buy food and clothes and meet emergencies and unexpected needs. The ability to calculate your mortgage helps you decide the best possible loan terms for minimizing your mortgage payment and whether to refinance an existing loan.
Using a Spreadsheet
Create cells for the loan amount, annual interest rate and loan term in years. (Ref. 1.) By way of illustration, the loan principal can occupy A2; put the annual interest rate B2 and the number of years of the loan in C2.
Enter the payment function in a cell. Start with "=PMT." (Ref. 1, 2.) The formula is formatted as "=PMT(Rate, Nper, PV, FV, type)." "Rate" represents the monthly interest rate, "Nper" is for number of periods, "PV" stands for present value and "FV" is the future value. Disregard the future value and type arguments.
Divide the annual rate by 12 months for the "Rate" component. For example, if you assigned the annual rate to cell "B2," type the argument as B2/12.
Set up the argument for Nper, or number of periods, as the cell for the number of years multiplied by 12. For example, if C2 is the loan term, depict the number of periods as C2*12. The spreadsheet will treat a 30-year loan as 360 months, a 15-year loan as 180 months and a 10-year loan as 120 months.
Use the cell for the loan amount, such as A2, as the present value. With the example cells in step 1, the formula in its final form is "=PMT(B2/12,C2*12,-A2)"; the loan amount, or present value, is negative so that your spreadsheet will present the payment as a positive result.
Using Payment Table
Go to "How to Buy a Home With a Low Down Payment" from the Federal Citizen Information Center" (publications.usa.gov/epublications/low-down.htm). Scroll down to the "Payment Table."
Get the monthly payment for each $1,000 of your loan. Find the payment for your interest rate and length of your loan -- 15 years, 20 years or 30 years. For example, on a 30-year loan with an eight percent annual interest rate, your payment per $1,000 is $7.34.
Divide your loan principal by 1,000 and multiply the result by the monthly payment per $1,000 from the payment table. For a $75,000 loan, multiply 75 (75,000/1,000) by 7.34 to arrive at a monthly mortgage payment of $550.50.
Enter your current, not original, loan balance in the loan amount cell if you plan to refinance.
Use the annual interest rate and the years for number of payments if you pay by year. If you pay other than monthly, divide the annual rate by the number of payments in the year and multiply the number of payments per year by the number of years.
If your spreadsheet program has an automatic feature for calculating payments, enter the loan amount, the periodic interest and number of payment periods.
Check your credit history periodically. Higher credit scores can lead to better interest rates and lower monthly payments. (Ref. 4.) Also, you can lower your monthly payment by increasing the length of your loan, but you'll pay more in interest over the long run.
The spreadsheet and payment table methods calculate only the principal and interest. To account for property taxes, homeowner's insurance and private mortgage insurance, divide the yearly tax bill and the premiums by 12 months and add the result to the monthly principal and interest.
The payment table uses only increments of 0.5 percent for interest rates.
- New Hampshire Business Owners Association: Using the Excel Loan Payment
- Brigham Young University: Selected Intrinsic Financial Functions for Microsoft Excel® Spreadsheets
- Federal Citizen Information Center: How to Buy a Home With a Low Down Payment
- Federal Reserve Board: A Consumer's Guide to Mortgage Refinancings
- Stockbyte/Stockbyte/Getty Images