When taking out a loan, it’s essential to understand the two key components that make up your monthly payments—the principle and interest. The principal refers to the actual amount borrowed from the lender. The interest, on the other hand, is the cost of borrowing that amount, calculated as a percentage of the principal and paid to the lender as compensation for the use of their money.
As such, it is important to understand how to calculate these two numbers. First and foremost, these calculations will give an accurate determination of what is owed. Second, in many cases the interest paid is tax deductible (a tax calculator can be quite helpful). Thankfully, this calculation can easily be done in Excel using a few simple formulas. Here’s a step-by-step tutorial on Calculating the Principal and Interest.
- 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:
