Calculating the principal and interest on a loan in Excel involves using a few simple formulas. Here’s a step-by-step tutorial:
- Open Microsoft Excel and create a new spreadsheet.
- In cell A1, type “Loan Amount” and in cell A2, type “Interest Rate”. In cell A3, type “Loan Term” (in years). In cell A4, type “Payment per period” and in cell A5, type “Total Payment”.
- In cell B1, enter the loan amount. For example, if the loan amount is $10,000, enter “10000” in cell B1.
- In cell B2, enter the interest rate as a decimal. For example, if the interest rate is 5%, enter “0.05” in cell B2.
- In cell B3, enter the loan term in years. For example, if the loan term is 3 years, enter “3” in cell B3.
- In cell B4, enter the formula to calculate the payment per period. The formula is “=PMT(B2/B3, B3*12, -B1)”. This formula uses the PMT function in Excel, which calculates the payment per period for a loan based on the interest rate, loan term, and loan amount. The “-B1” part of the formula is because the PMT function requires a negative value for the loan amount.
- In cell B5, enter the formula to calculate the total payment. The formula is “=B4*(B3*12)”. This formula multiplies the payment per period by the total number of periods (in months) in the loan term to get the total payment.
- Format cells B4 and B5 as currency to make them easier to read.
- Your spreadsheet should now look something like this:
