How to Calculate Interest & Full Amortization in Excel

Consideration of interest is important when applying for a loan.

Amortization is the process of determining interest and principal for a loan over time. Generally, especially with bank loans, the initial payments mostly are interest and the final payments largely are principal. Calculating interest is a vital part of determining if a loan is affordable for your circumstances because it increases the payments. There is a template available in Microsoft Excel that calculates interest and payments as well as creating an amortization schedule for a loan. Alternatively, formulas can be used to figure interest and payments if those templates are not available.

Instructions

1. Enter the loan amount, interest rate, length of loan and the loan start date in the boxes indicated on the Excel amortization template. After you have filled in the information, Excel will automatically fill in the amortization schedule with preset formulas and show the interest and payments calculated for the loan.

2. Start a new Microsoft Excel worksheet If you are not able to access a template for amortization or only need the interest charges and payment information. You can still calculate the periodic payments, the interest charged and the total cost of a loan by using formulas.

3. Title the new worksheet in cell A1 and then in cells A3, A4 and A5 write: "Amount financed," "Annual interest" and "Duration of loan (in years)" respectively. The start date is used only for the amortization schedule, which lists the due date of each payment, and can be omitted in calculating the interest and payments.

4. Enter the amounts of your particular loan in cells B3, B4 and B5, using the descriptions in column A as your guide. Enter the interest as a decimal. For example, 0.0525 if the interest is 5.25 percent.

5. Write in cells A7, A8, A9, A10, A11 and A12: "Monthly payments," "Total number of payments," "Yearly principal + interest," "Principal amount," "Finance charges" and "Total cost."

6. Create the calculation for the interest and payments by writing in B8 the formula "=B5*12"; in B7, "=PMT((B4/12),B8,-B3)"; in B9, "=SUM(B7*12)"; in B10, "=SUM(B3)"; in B12, "=B9*B5"; and in B11, "=SUM(B12-B10)." Since the formula in B7 uses data in cell B8 and B11 uses data in cell B12, insert the formulas in the order given. This is out of row order, but avoids showing an error in Excel.

Tips & Warnings

• Look at the templates provided when opening a new worksheet in Excel 2010 and choose "Microsoft Online." This will bring you to Microsoft's server where you should see and select a template called "Loan Amortization." In older versions of Excel, you may be able to open a new worksheet, then click "File"/"Project Gallery"/"Home Essentials"/"Finance Tools"/"Standard Loan" or look for "Installed Templates." Another way to obtain an amortization template is to download it at Microsoft's website.

• It can be interesting to input different interest rates and time periods to see how the payments and amount of interest paid are affected.

• In the loan amortization template found in Excel 2010, there is space to add in extra payments, which would reduce the total interest paid on the loan. Extra payments are applied directly to principal.

References

• TVMCalcs: Loan Amortization with Microsoft Excel
• Microsoft: "Plan Payments and Savings in Excel 2010"; Judi Hurlock
• Financial Dictionary: Amortization

Resources

• Arsidian LLC: Amortization Schedule Calculator
• Microsoft Office: Loan Amortization Schedule