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

How to Use Excel to Calculate How Much a Loan Will Cost "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."" 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. 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. Tip Change the interest rate, amount borrowed or term values to see how a small change could affect both your monthly payment and your total cost of the loan. Warning Make sure you enter the interest rate as a decimal. For example, 5 percent should be entered as ""0.05"" not ""5."" Advertisement" - https://www.affordablecebu.com/