Microsoft Excel's versatility lies in its ability to simplify complex financial tasks, such as creating a mortgage amortization schedule. An amortization schedule provides a detailed breakdown of a loan, outlining monthly payments, principal balance, interest paid, and more. In this guide, you'll learn how to create a mortgage amortization schedule in Excel step-by-step.
Before diving in, ensure you have Excel installed. If not, consider using Microsoft's free Excel Viewer or their 1-month free trial for full access. Now, let's begin.
Setting Up the Amortization Schedule
The first step involves setting up the basic structure of your amortization schedule. This includes entering initial loan data and creating rows for each period.
Here's how to do this:
Enter Initial Loan Data
In cells A1:F1, enter the following labels: 'Loan Principal', 'Annual Interest Rate', 'Loan Term (Years)', 'Number of Payments per Year', 'Payment per Period', and 'Cumulative Payments'.
Then, fill in the appropriate values in cells A2:F2, e.g., '$200,000' for 'Loan Principal' if that's your initial loan amount.
Create Rows for Each Period
In cell A3, enter 'Period', then drag this cell down to the appropriate row based on your loan term. For example, if you entered '30' in cell D2, drag cell A3 down to A31 for a 30-year mortgage.
Next, calculate the interest and principal for each period under 'Interest' and 'Principal', and update the 'Cumulative Payments' column accordingly.
Formulas for Amortization Schedule
Now, let's understand the formulas required to calculate interest, principal, and cumulative payments in your amortization schedule.
Formula for Interest
In cell B3, enter the formula: `=A2*F2*C2`. This calculates the interest paid in the first period. Copy this formula and paste it into cells B4:B31.
Here, 'A2' represents the 'Loan Principal', 'F2' the 'Annual Interest Rate', and 'C2' the 'Loan Term (Years)'. The result is divided by the 'Number of Payments per Year' in cell E2.
Formula for Principal
In cell C3, enter the formula: `=(A2-A3)+B3`. This calculates the principal paid in the first period. Copy this formula and paste it into cells C4:C31.
Here, 'A2' and 'A3' represent the loan principal at the start of the period and the cumulative payments up to the current period. 'B3' represents the interest paid in the current period.
The 'Cumulative Payments' column is calculated by adding the 'Interest' and 'Principal' for each period, as demonstrated in the formulas above.
Reviewing and Customizing Your Amortization Schedule
With your amortization schedule complete, take a moment to review the figures. You can also customize this schedule to view extra-info, such as interest paid to date and principal paid to date, by adding new columns.
Interest Paid to Date
In cell G2, enter this formula: `=SUM(B2:B31)`. Then, in cell G3, enter the formula: `=G2` to hold this value as 'Interest Paid to Date'.
Principal Paid to Date
In cell H2, enter this formula: `=SUM(C2:C31)`. Then, in cell H3, enter the formula: `=H2` to hold this value as 'Principal Paid to Date'.
Your mortgage amortization schedule is now complete. Excel provides an efficient and flexible tool for managing complex financial tasks. Poker, anyone?