How to Use Excel to Calculate How Much a Loan Will Cost

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

Microsoft Excel can make calculating the total cost of a loan much easier than trying to calculate values one step at a time. In addition, you can format a spreadsheet in Excel to allow you to see how changing one or more of the variables affects the total cost of the loan. When determining the total cost of the loan, you need to know the amount you are borrowing, the interest rate and how long it will take to repay the loan.

Advertisement

Step 1

Format your spreadsheet. In cell A1, write "Amount Borrowed." In cell A2 write "Interest Rate." In cell A3 write "Term (Years)." In cell A4, write "Monthly Payment." In cell A5, write "Total Cost."

Video of the Day

Step 2

Enter the appropriate values next to the first three cells. For example, if you are borrowing $120,000 at a 5 percent interest rate over 30 years, you would enter "120000" in B1, "0.05" in cell B2 and "30" in cell B3.

Advertisement

Step 3

Enter "=PMT(A2/12,A3*12,A1)" into cell B4. This will calculate the monthly payment on your loan. The interest rate is divided by 12 to find the monthly interest rate and the term is multiplied by 12 to determine how many monthly payments you will make.

Advertisement

Step 4

Enter "=B4_B3_12" into cell B5 to calculate the total cost of the loan by multiplying the number of monthly payments times the cost of each monthly payment.

Video of the Day

Advertisement

Advertisement

references