Open Excel and create columns for Payment Number, Payment Date, Beginning Balance, Payment, Interest, Principal, and Ending Balance
Enter the loan amount in the Beginning Balance of the first row
Enter the annual interest rate in a separate cell
Enter the loan term in months or years in a separate cell
Enter the payment frequency in a separate cell
Calculate the periodic interest rate by dividing the annual rate by the number of payment periods per year
Calculate the fixed payment using the PMT function
Use a formula to calculate interest for each period as Beginning Balance multiplied by periodic interest rate
Use a formula to calculate principal as Payment minus Interest
Use a formula to calculate Ending Balance as Beginning Balance minus Principal
Set the next row’s Beginning Balance equal to the previous row’s Ending Balance
Fill the formulas down for all payment periods
Format currency cells as currency
Format date cells as dates
Verify the final Ending Balance is zero or close to zero due to rounding
Adjust the last payment if needed to remove any small remaining balance