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.
- 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