The Home Category The Home: family money matters

How to Calculate Mortgage Interest in Excel

When you take out a mortgage to pay for your home, you repay it over a set term at a specified interest rate.

When comparing multiple mortgage-loan options, you will want to determine how much interest you must pay over the life of the loan.

In order to calculate the total mortgage interest you will pay, you need to know the amount borrowed, the term of the loan and the interest rate. With Microsoft Excel, you can create a mortgage interest spreadsheet to see how different values affect the total interest you would pay.

Open Excel on your computer. Enter "Mortgage Amount" in cell A1, "Term in Years" in cell A2, "Interest Rate as a Percent" in cell A3, "Monthly Payment" in cell A4, "Total Payments" in cell A5 and "Interest Payments" in cell A6.

Enter the amount you intend to borrow in cell B1, the term of the mortgage in cell B2 and the interest rate as a percentage in cell B3. The amount should be entered as a number with no dollar sign or commas, and the term should be entered in years as a numeral only. For example, if you were calculating the interest on a £266,500, 30-year mortgage at 6 per cent, you would enter "410000" in B1, "30" in B2 and "6" in B3.

Type "=PMT(B3/1200,B2_12,B1)" into cell B4. Excel will automatically calculate and display the monthly payment on the loan. PMT is an Excel function to calculate the monthly payment on a loan, "B3/1200" represents the periodic interest rate, "B2_12" represents the number of payments made and "B1" represents the loan amount. Continuing the example from Step 2, you would see "($2,458.16)" as the monthly payment in cell B4.

Copy and paste "=B4_B2_12" into cell B5 to have Excel automatically calculate the total cost of the loan. "B4" represents the monthly cost of the loan, and "B2*12" represents the number of months you have to pay for it. Continuing the example, you would see "($884,936.58)" appear in cell B5 after you entered the formula.

Type "=B5+B1" into cell B6 to have Excel automatically calculate how much of the total cost of the mortgage goes toward interest payments. "B5" represents the total payments you make, and "B1" represents the cost of paying back the principal. The "+" is used because Excel returns a negative value for the total cost of the loan. Finishing the example, you would see "(£308,708.70)" appear in cell B6 after you entered the formula, meaning you would pay $474,936.58 in interest on the mortgage.

Things Needed

  • Computer
  • Microsoft Excel
  • Mortgage loan information