How to Mortgage Calculator In Microsoft 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.

How do I Calculate Mortgage Payments

Do you need to know how much interest you would pay on a mortgage, or how extra payments would affect your principal? Now you can build an Excel spreadsheet to answer those questions.
1. Launch Microsoft Excel. Open a new workbook by pressing "Ctrl" and "N."

2. Type "Principal" into cell A1 on the Excel worksheet. Type "Rate" into cell A2. Type "Months" into cell A3.

3. Enter the amount of the mortgage principal in cell B1.

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

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

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

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

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

9. Enter the following formula in cell A7, beginning with the "equals" sign: =A6*B1 This applies the multiplier to your loan principal.

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