✦Template Printable Ideas Insights

"Create Amortization Schedule Excel Monthly Payment"

By

Amortization schedules are essential tools for tracking the decreasing balance of a loan or other financial instrument over time. When dealing with loans, it's crucial to understand how your monthly payments are applied towards both your principal and interest. In this guide, we'll walk you through the process of creating an amortization schedule in Excel using a set monthly payment.

Before we dive in, ensure you have a basic understanding of Excel and some familiarity with financial concepts, particularly loans and amortization. This guide assumes you're using Microsoft Excel, but the principles should apply to other spreadsheet software like Google Sheets or LibreOffice.

Preparing Your Excel Worksheet

To get started, open Excel and create a new workbook. In the first row, title your columns as follows: 'Period', 'Payment', 'Interest', 'Principal', 'Balance', and 'Cumulative Principal'

Loan Amortization Schedule for Excel
Loan Amortization Schedule for Excel
Loan Amortization Schedule in Excel
Loan Amortization Schedule in Excel
loan amortization schedule excel
loan amortization schedule excel

Next, highlight the cells you've labeled and merge them to create headers for your amortization schedule. Now, let's set up the formulas to automatically calculate each column:

Calculating Payment

In the cell under 'Payment', enter the formula for your monthly payment. If your payment is constant, it's as simple as typing the amount. For example: `=500`

FREE 7+ Amortization Table Samples in Excel
FREE 7+ Amortization Table Samples in Excel
Amortization Schedule with Irregular Payments in Excel (3 Cases) - ExcelDemy
Amortization Schedule with Irregular Payments in Excel (3 Cases) - ExcelDemy
Get the Loan Amortization Schedule Template for Google Sheets
Get the Loan Amortization Schedule Template for Google Sheets

If your payment varies over time, you can use an Excel table or other structured reference to input the changing values.

Calculating Interest and Principal

The interest and principal components of your payment are calculated using the `PMT` function in Excel. In the cell under 'Interest' for the first period, enter the following formula:

`=PMT(B2[$A$1:A1], A1, -[@[Loan Amount]], 0, 0, 0)`

Calculateur d'amortissement de prêt : modèle Excel et calendrier
Calculateur d'amortissement de prêt : modèle Excel et calendrier
Loan Amortization Schedule Calculator | Plan Projections
Loan Amortization Schedule Calculator | Plan Projections
How to Make Loan Amortization Schedule in Excel - ORDNUR
How to Make Loan Amortization Schedule in Excel - ORDNUR
the loan annotation schedule is displayed in this screenshote, with an arrow pointing
the loan annotation schedule is displayed in this screenshote, with an arrow pointing
Calendrier de paiement/amortissement - Modèles réutilisables permettant aux particuliers de suivre et de gérer leurs finances personnelles - Prêt et paiements
Calendrier de paiement/amortissement - Modèles réutilisables permettant aux particuliers de suivre et de gérer leurs finances personnelles - Prêt et paiements
Calendrier d'amortissement du prêt : modèle de calculatrice Excel (téléchargement numérique)
Calendrier d'amortissement du prêt : modèle de calculatrice Excel (téléchargement numérique)

For the first period, replace `B2` with the cell containing your interest rate and `[Loan Amount]` with the cell containing your initial loan balance. For subsequent periods, the formula will adjust automatically as the loan balance and cumulative principal change.

In the cell under 'Principal', use the following formula: `=[@Payment] - [Interest]`. This calculates the principal component of each payment by subtracting the interest from the total payment.

Creating the Amortization Schedule

Now that your formulas are set up, you can create your amortization schedule. Click on the cell with the first payment amount and press `Ctrl + Shift + Down` (or `Cmd + Shift + Down` on Mac). This will apply the formulas to all subsequent rows, creating a schedule that automatically calculates each period's interest, principal, and balance.

Monthly Loan Amortization Calculator | Plan Projections
Monthly Loan Amortization Calculator | Plan Projections
Amortization Schedule Template Excel & Google Sheets
Amortization Schedule Template Excel & Google Sheets
Tableau d'amortissement | Calendrier de remboursement du prêt universel (modèle Excel)
Tableau d'amortissement | Calendrier de remboursement du prêt universel (modèle Excel)

To make your schedule visually appealing and easy to read, you can add some basic formatting. For example, you can change the number format to Currency and adjust the number of decimals. You can also highlight alternating rows and hide any columns that aren't necessary.

Adjusting the Schedule for Early Payments

If you plan to make early payments, you can adjust your schedule by manually inputting the new payment amounts and updating the loan balance accordingly. Alternatively, you can use `IF` functions to recognize when an earlier payment has been made and adjust the schedule automatically.

To do this, create a new column for 'Early Payment' and manually input any additional payments. Then, in the 'Payment' column, use the following formula: `=IF([@Early Payment]>0, [@Early Payment], [@Payment])`. This will use the early payment amount if it exists, or the scheduled payment if it doesn't.

Calculating Total Cumulative Principal

In the cell under 'Cumulative Principal', enter the following formula: `=SUM(Prin)` (where 'Prin' is the name you've given to the column containing the principal amounts). This calculates the total cumulative principal paid over the life of the loan.

Your amortization schedule is now complete! Not only does it provide a detailed breakdown of each payment, but it also offers valuable insights into how much interest you'll pay over time and when you'll reach your loan's maturity.

Remember, the accuracy of your amortization schedule depends on the accuracy of your input data. Always double-check your formulas and values to ensure they're correct.

As you become more familiar with amortization schedules and Excel, you can explore more advanced features like conditional formatting, data validation, and macros to further enhance your schedules and improve your financial management skills.