The basic business points of most mortgages can be summed up in four numbers. The mortgage's interest rate determines how much you pay for the money you're borrowing, the term specifies how long the loan lasts, the present value is the amount of money that you're borrowing, and the payment is what you have to pay every month. If you know three of those variables, you can solve for the fourth. You can do it on a financial calculator, but any spreadsheet can also do the mortgage math for you. Solving these statistics by hand requires elementary calculus, so, for most people, a spreadsheet is the best way to go.
Open a blank spreadsheet in your chosen spreadsheet program.
Enter the interest rate for your mortgage in cell A1 as a percentage. For instance, if you're looking at a loan with a 5.25-percent interest rate, enter "5.25%" without the quotation marks. If you want to solve for the interest rate, leave this cell blank.
Enter the loan's term in months in cell A2, right below the interest rate or the blank cell. For a 30-year loan, enter "360" without the quotation marks. If you don't know the term in months, you can enter "=X*12" without the quotation marks, with X as the number of years on your mortgage.
Enter the mortgage's monthly payment in cell A3. For instance, if you have a 30-year $195,000 mortgage and know that the payment is $1,076.80, enter "1076.80" without the quotation marks. If you don't know the payment amount, leave this cell blank.
Enter the loan amount as a negative number in cell A5. If you're taking out a $195,000 mortgage, enter "-195000" without the quotation marks.
Solve for the payment by entering the following formula, without the quotation marks, in cell A6 "=PMT(A1/12,A2,A4)" into Microsoft Excel or the Google Docs spreadsheet. If you're using OpenOffice, enter "=PMT(A1/12;A2;A4)" without the quotation marks.
Enter this command, without the quotation marks or period, into cell A6 on Excel or Google Docs to find the yearly interest rate: "=RATE(A2,A3,A4)*12." The format is the same for OpenOffice, but with semicolons instead of commas -- "=RATE(A2;A3;A4)*12." (ref 5)
Items you will need
- If the rate shows up as a single digit followed by a percentage sign in Excel, right-click it and click the "Increase Decimal" button.
- The instructions in this article apply to the versions of Google Docs, Microsoft Excel and OpenOffice that were current as of the date of publication. They may or may not apply with different versions of these programs or with other spreadsheets.
- Stockbyte/Stockbyte/Getty Images