How do I Calculate Mortgage Payments in Excel?
When you take out a fixed-rate mortgage to buy or refinance a home, your lender takes three numbers and plugs them into a formula to calculate your monthly payment. Those three numbers are your principal, or the amount of money you re borrowing; your interest rate; and the number of months in your loan term. You can quickly create a spreadsheet in Microsoft Excel to perform the calculation for you–and, in the process, gain a greater understanding of just how a mortgage loan works.
Launch Microsoft Excel. Open a new workbook by pressing “Ctrl” and “N.”
Type “Principal” into cell A1 on the Excel worksheet. Type “Rate” into cell A2. Type “Months” into cell A3.
Enter the amount of the mortgage principal in cell B1.
Enter the interest rate in cell B2. Just enter the number; don’t use the percent sign. So, if your rate is 7 percent, just enter 7. If it’s 5.75 percent, enter 5.75.
Enter the number of months in the loan term in cell B3. Most mortgages are for either 15 or 30 years. Enter 180 for a 15-year mortgage or 360 for a 30-year loan. If your loan is for some other number of years, simply multiply that number by 12 and enter the result in cell B3.
Enter the following formula in cell A4, beginning with the “equals” sign: =B2/1200 This converts your annual interest rate to a decimal figure by dividing it by 100, then breaks it down into a monthly rate by dividing it by 12.
Enter the following formula in cell A5, beginning with the “equals” sign: =(1+A4)^B3 This step takes into account the compounding of the interest over the life of the loan.
Enter the following formula in cell A6, beginning with the “equals” sign: =(A4*A5)/(A5-1) This takes all the data and boils it down to a multiplier that’s applied to your principal to determine your monthly payment.
Enter the following formula in cell A7, beginning with the “equals” sign: =A6*B1 This applies the multiplier to your loan principal.
Right-click on cell A7 and select “Format Cells.” Set the formatting to “Currency.” Set “Decimal Places” to 2. Set the “Currency Symbol” to the dollar sign. Click “OK.” This cell now gives you the amount of your mortgage payment based on your principal, interest rate and loan term.
Things You Will Need
- Microsoft Excel
- Experiment with different principal amounts, interest rates and loan terms just by changing the values in cells B1, B2 and B3. The total payment in cell A7 will change to reflect the new figures.
- Most lenders require that you pay your property taxes and homeowners’ insurance premiums on a monthly basis, with 1/12 of the total tacked on to each mortgage payment. Those amounts are not included in this calculation. The result in cell A7 includes only the amount that goes to your lender–the total principal and interest due each month.
About the Author
Cam Merritt is a writer and editor specializing in business, personal finance and home design. He has contributed to USA Today, The Des Moines Register and Better Homes and Gardens publications. Merritt has a journalism degree from Drake University and is pursuing an MBA from the University of Iowa.