Simple Interest Loan Amortization Schedule in Excel

Understanding Simple Interest Loans
Simple interest loans are straightforward: interest is calculated only on the principal amount of the loan. This means that every payment made will reduce the principal balance, and interest is applied only to this remaining balance. The formula for calculating interest on a simple interest loan is:

$\text{Interest}=\text{Principal}\times \text{Rate}\times \text{Time}$Interest=Principal×Rate×Time

where:

• Principal is the original loan amount.
• Rate is the annual interest rate (as a decimal).
• Time is the time in years.

Setting Up Your Excel Spreadsheet
To create an amortization schedule, you'll need to set up an Excel spreadsheet with specific columns. Here’s a step-by-step guide:

A. Open Excel
Launch Excel and open a new blank workbook.

B. Create Column Headers
Label the columns with the following headers:

• A1: Payment Number
• B1: Payment Date
• C1: Payment Amount
• D1: Principal Payment
• E1: Interest Payment
• F1: Total Interest Paid
• G1: Remaining Balance

C. Input Basic Loan Information
In cells A2 through A4, input the loan details:

• A2: Principal amount (e.g., $10,000)
• A3: Annual interest rate (e.g., 5% or 0.05)
• A4: Number of years (e.g., 3 years)

Formulas for Amortization Schedule

A. Payment Number
In A6, input 1, and in A7, input the formula:
$=A6+1$=A6+1
Drag this formula down to generate payment numbers for the entire loan term.

B. Payment Date
In B6, input the start date of the loan (e.g., 01/01/2024). In B7, use the formula:
$=EDATE(B6,1)$=EDATE(B6,1)
This will increment the payment date by one month. Drag this formula down for the length of the loan.

C. Payment Amount
Use the PMT function to calculate the fixed monthly payment. In C6, input:
=PMT($A$3/12, $A$4*12, -$A$2)
This formula calculates the fixed payment amount based on the interest rate, loan term, and principal.

D. Principal Payment
In D6, calculate the principal part of the payment. Use:
=C6 - (B6 * $A$3 / 12)
where B6 is the remaining balance.

E. Interest Payment
Calculate the interest part in E6:
=B6 * $A$3 / 12

F. Total Interest Paid
In F6, use:
=SUM($E$6:E6)

G. Remaining Balance
In G6, calculate the remaining balance:
$=B6-D6$=B6−D6

4. Fill Down the Formulas
Select the cells with formulas and drag them down to fill the entire schedule. Ensure that the remaining balance updates correctly and the total interest is cumulative.

Review and Adjust
A. Verify Calculations
Check that the final balance is close to zero and that the total interest paid aligns with your expectations.

B. Customize as Needed
You may need to adjust the format or add additional columns for your specific needs, such as extra payments or changes in interest rates.

Example Spreadsheet

Here’s a simple example of how your Excel amortization schedule might look:

Conclusion
By following these steps, you can easily create a simple interest loan amortization schedule in Excel. This tool will help you manage your loan repayments effectively, allowing you to see how your payments are applied over time and how much interest you will pay throughout the loan term. Whether you are managing a personal loan or a business loan, this schedule is a valuable financial management tool.