Calculating a Mortgage Payment in Microsoft Excel

Mortgage companies are like everyone else: they make mistakes. Not surprisingly most of the time, the mistakes are in their favor, so it is best to check their calculations. Here is a simple tutorial on creating an amortization table to recalculate your mortgage payment. The amortization schedule will show the principal and interest portions of each payment and the remaining loan balance at the end of each period. One can also use this tutorial to calculate car loans, home equity lines and personal loans.

Hypothetical Loan

For this example, our mortgage/loan amount is $1,000 with an annual interest rate of 6% for 1 year (12 months). (Obviously a loan amount will usually be larger and the payment over a longer period)

Setting up the Calculation in Excel

Set up a list in excel to put the loan information into and to calculate your payment.


I have filled in the top portions with our hypothetical values. Now lets calculate the rest.

To calculate the monthly rate, we need to divide the annual interest rate (cell B3) by 12 for 12 months. In Cell B5, enter the formula =B3/12.

To calculate our monthly payment is trickier, we need to use the PMT formula in excel. In the formula, you need to enter the monthly interest rate (rate), months (nper) and loan balance (pv) in that order. The formula reads like this =PMT(rate,nper,Pv). So for our example we enter into cell B6; =PMT(B5,B4,B2). This gives us our constant monthly payment for over the life of the loan. Check this payment amount against what you lender is currently charging you….your payment maybe higher due to escrow for taxes and insurance, so make sure you know those amounts.

Below see our loan information filled in with the formulas used next to the actual calculations in column C.



Now that we have our monthly constant payment, we can compute our principal and interest portions of the payment by setting up an amortization table. The table should look like this:

See that we start with year 0 and the beginning loan balance. Now let's calculate the first payment (period 1).

Under period 0 enter 1 into Cell C10, now we can calculate the principal portion of the first payment. We will be using the Excel formula PPMT which is designed to give the principal payment when payments are constant on a loan. The formula uses the monthly rate (rate), current period (per), number of payment periods (nper) and total value of the loan (pv) to calculate the current principal payment. The formula reads like this =PPMT(rate,per,nper,pv). Now here is where it may get tricky since we are using constant numbers for monthly rate, number of payment periods and total value of the loan. To keep a number constant in a formula, use a “$” in front of both the row number and the column letter, e.g. to keep A1 constant the cell is entered $A$1. By placing the “$” in the formula, we assure that even if the formula is copied it will always pull from the constant cell rather than shifting the cell based on what cell the formula is copied into. So here is how our formula in Cell D10 will look using the formula PPMT and keeping certain cells constant: =PPMT($B$5,C10,$B$4,$F$9).



Now to calculate the interest portion of the monthly payment in Cell E10, we simply subtract the monthly principal payment (D10) from our constant monthly payment (B6). Remember that our constant monthly payment remains the same every month, so in order to copy the formula we must use “$” in the formula. Here is what our formula will look like: =$B$6-D10.



To calculate the remaining loan balance after a monthly payment, we add the loan balance in the previous month to the principal portion paid off in the current month. In Cell F10, we enter the formula: =F9+D10.



We have now successfully computed the breakdown of principal and interest and the remaining loan balance after our first month’s payment. Since we added the “$” to the formulas, computing the remaining months will be easy. Highlight Cells C10 to F10 and drag the little black in the bottom right corner down 11 cells. (See below) This simply copies the formulas and numbering those cells into the 11 cells below them, giving us our total principal and interest payments over the life of the loan and showing the loan balance as 0 in the last month (Month 12).



The amortization table should now be complete and look like this:



Excel turns this complicated computation into a simple calculation that my save you thousands of dollars over the life of your loan.

-Dave