There are two common ways to handle interest. With a simple-interest loan, interest is computed only on the unpaid principal. Each period, interest = outstanding principal × (annual rate ÷ periods per year), so the interest part of the payment is based on today’s balance, not the original loan amount.
With compound interest, any unpaid interest is added to the balance (capitalized), and later interest is then computed on that higher amount.
This Excel template builds a simple-interest, fixed-rate amortization schedule with equal payments. Enter the loan amount, annual rate, term, and start date. The sheet calculates each payment, splits interest and principal, tracks the remaining balance, and lists due dates from start to payoff.
How We Calculate Interest
Our template supports 2 common (non-compounding) ways of calculating interest. This way, users are able to accommodate varying types of loan terms & arrangements:
- Add-on (or Flat-rate) Method: The interest rate stays fixed for the entire loan term, and is always charged on the original loaned amount
- Simple-Interest, Amortizing (or Reducing) Method: Interest is charged on the remaining balance after each repayment, so it decreases over time
For reducing loans, we help you track how much each payment goes toward paying interest (progressively 🔽) and how much to cover the principal loan (progressively 🔼). In flat-rate loans, these two values remain constant throughout the loan term. Keep reading to learn why this information is good for you & your finances!
Feel free to use our free Loan Amortization template to manage loans that allow you to save on interest payments by paying extra, on top of your regular installment.
The Simple Interest Loan Calculator
We designed this template to be as simple as possible. You can use it to model two non-compounding structures: flat (add-on) and reducing-balance amortized loans.
Probably a good time to ask, what is amortization?
Loan amortization is a debt repayment method where you pay back through fixed-rate payments spread out over the loan term. Amortization tables show outstanding balance after each payment, giving you an idea of how much you still have to pay.
Okay, back to our Simple Interest loan-calculating sheet…
As you can see in the Main spreadsheet, each payment consists of a principal and an interest amount.
Now, suppose you select the flat-rate interest method. In that case, these values will remain constant, in effect depriving you of the option to pay extra and lower the interest you cumulatively pay. But for reducing loans, these values change over time because interest is charged on the remaining balance of what you have to pay – NOT the original borrowed amount.
As you can see, for reducing loans, the interest portion (see Column E) goes down over time while the principal portion (Column F) builds up, until complete loan payoff at the end of the loan term.
Impact of extra payments on loan interest and prepayment terms
Paying extra (on top of your regular payment) is a great way to lower the total amount of interest you pay over the loan term. That said, extra payments on flat-rate loans may or may not save you much, depending on the lender’s refund method for unearned interest and any prepayment terms in your contract. Many contracts allow early payoff and refund the unearned portion of interest by an actuarial method or by the Rule of 78. Some may charge fees or give a smaller refund. Check your loan agreement or lender policies for more information.
So, Which One’s Better?
Reducing interest rates is more popular among borrowers. With reduced rates, borrowers get to pay interest only on the remaining loan balance. This way, they can benefit from reduced interest payments and early payouts, unlike the more rigid flat interest rate method.
Core Template Features
With the right template, you can easily monitor & track multiple loans, ensuring sound finances across your personal or business life.
- A quick snapshot of your loan commitments
- A list-view schedule & calendar of periodic payments over the entire loan term
- A tabulated summary of your loan repayment structure
- A chart that shows the remaining balance, interest paid, and principal paid over time
- An instant loan amortization calculator based on minimal user input
- A reference sheet to test out different loan amounts & interest rates
Data You Need To Put In Manually
With only a few data prompts, create a live loan amortization schedule tailored to your loan arrangements.
- Loan Amount: The lump-sum amount of money loaned or borrowed. Loans vary widely, from major mortgages to modest auto loans.
- Loan Term: The term or life of the loan: This is the total time you (legally) have to pay back the loan, with interest.
- Payment Frequency: The frequency of loan repayments. Options available: Biweekly (26 payments per year), semi-monthly (24), monthly (12), quarterly (4), and yearly (1).
- Interest Rate: The fixed-interest rate you are charged (e.g., 5% on a business loan of $250,000)
Rounding Option
For easier calculation and consistency, we round off monthly payments to two decimal points.
You can also switch off the rounding function. Remember, if you round in your calculations, you’ll end up with a slight difference in either the total underreporting interest paid and the overreporting principal paid or vice versa.
Grand Total
This is the sum of all your periodic payments by the end of the loan term. In other words, it represents the originally borrowed amount plus all the interest you paid on it. We highlight this value for you – so you know exactly what kind of financial commitment you are walking into from day one, and whether it’s worth it for you in the long run!
Taxes, insurance, fees, and escrow are not part of loan interest and are not included unless you enter them.
Summary
Right above the main sheet, we present a tabulated summary of the loan repayment process based on the data supplied by the user, including the total number of payments you will make over the loan life. For example, if you select Monthly as your Payment Frequency, and the loan term is 12 years, this number will be 144; if you choose ‘weekly’, this number changes to 624.
Benchmark Spreadsheet
A Benchmark Sheet is provided as part of our premade package. You can use it to research or plan your loans by testing different loan amounts & interest rates. This allows you to compare multiple loan scenarios side by side and choose the repayment plan that suits your budget or financial goals.
This Pre-automated Template Is Ideal For …
- Private borrowers
- Private lenders
- Banks & credit unions
- Online lenders & fintech companies
- Government programs
You Might Also Be Interested In…
- Compound Interest Calculator
- Balloon Loan Calculator
- Interest-Only Loan Calculator
- Daily Compounding Loan Calculator
- Loan Amortization
Wrap Up
Whether you are comparing loan schemes or simply looking for a way to track or manage your loan repayments, it is important to understand the overall loan structure & repayment schedule, as specified in your loan agreement or policy.
Use this simple yet effective tool to deepen your understanding of your loan obligations as you keep track of when your next payment is due!
