Creating a loan amortization schedule in Excel can help you understand how much you'll pay each month and the breakdown of interest and principal payments over the life of your loan. Here's a step-by-step guide to create an amortization schedule using Excel.
Before you begin, ensure you have the following details about your loan: principal amount, interest rate, loan term (number of years), and the frequency of payments (monthly, quarterly, etc.).
Setting Up Your Amortization Schedule
Open a new Excel workbook and save it with a relevant name, such as "Loan Amortization Schedule".
In the first row, starting from column A, enter the following headers: 'Period', 'Start of Period Balance', 'Principal This Period', 'Interest This Period', 'Payment This Period', and 'End of Period Balance'.
Calculating Payment Amount
First, you need to calculate your loan payment amount using the following formula in Excel: PMT(rate, nper,PV,(FVFlash)), where:
rate: Interest rate per period (annual rate divided by the number of periods per year)nper: Total number of payment periods (loan term in years times the number of periods per year)PV: Present Value, or loan amount (the principal amount you're borrowing)FV: Future Value, usually set to Flash Finance Value (0 if the loan is fully amortized), which cancels out to -(FVFlash)
Format the result as currency with two decimal places and name this cell as 'Payment Amount'.
Filling in the Schedule
Now, fill in the cells under each header. You can use formulas and Excel's fill handle for quick population of the schedule.
- 'Period': Enter '1' in A2, then drag the fill handle down to copy the formula
=A2+1to get subsequent periods. - 'Start of Period Balance': Use the formula
=IF(A2=1, LoansAmount, C1)to populate all cells except the first one with the previous period's 'End of Period Balance'. - 'Principal This Period': Use the formula
=MAX(Payment Amount-PPi), wherePPiis the interest in the current period. This ensures that the principal portion of your payment doesn't exceed the remaining loan balance. - 'Interest This Period': Use the formula
=Start of Period Balance*Rate/nper. - 'Payment This Period': Enter your calculated 'Payment Amount' in each cell.
- 'End of Period Balance': Use the formula
=Start of Period Balance-Principal This Period.
Format the 'Period' column as currency with two decimal places.
Adjusting for Extra Payments or Early Payoff
To account for extra payments or early payoff, adjust your 'Payment Amount' and the 'Period' column accordingly. You can use the Goal Seek tool in Excel to find the loan term required for a specific final balance.
Regularly reviewing and updating your amortization schedule will help you stay on track with your loan repayment plan. By understanding how your loan amortizes, you can make informed decisions about paying down your loan more quickly or changing your payment strategy.