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