Creating an amortization schedule in Excel is a crucial step in managing your financial obligations, whether it's for a mortgage, car loan, or business loan. This step-by-step guide will walk you through the process, ensuring that you understand each step and have a well-structured amortization schedule at the end.
To begin, open a new or existing Excel workbook and ensure you have Excel 2010 or later for the best functionality. We will create the amortization schedule for a 30-year mortgage with a $200,000 loan, 6% interest rate, and $1,000 annual payment.
Setting Up the Amortization Schedule
This section will focus on setting up the basic structure of the amortization schedule, including the labels and formulas for the columns.
In cell A1, enter "Loan Amortization Schedule" (without the quotes) and format it as a header. In row 2, enter the following labels, starting from cell A2:
- Period (in years and months)
- Beginning Balance
- Payment
- Interest
- Principal
- Ending Balance
Creating the Year-Month Column
In cell B3, enter "30", and in cell B4, enter "29". Then, select these two cells and drag the fill handle down to cell B305 to create the period column (B3:B305).
To display both the year and month, select cell B3 and click 'Format' > 'Number' > 'Custom'. Enter "0\" \"yyyy\" yyyy; ""[@[B3]]" and click 'OK'.
Calculating the Loan Payment
To calculate the annual loan payment, use the loan payment formula in cell C3: `=PMT(6%/12,12*30,-200000,0,1)`. This formula takes into account the interest rate, loan term, loan amount, and whether your payment is at the beginning or end of the period.
The result should be -$12,361.28, with the negative sign indicating it's an outgoing payment.
Calculating the Amortization Schedule
This section will focus on calculating the interest, principal, and ending balance for each period using formulas.
Calculating Interest
In cell D3, enter the formula for the interest: `=C3*(6%/12)` to calculate the monthly interest. Then, drag the fill handle down to cell D305.
To format the results as currency, select cells D3:D305, click 'Home' > 'Number', select the currency format, and choose your preferred currency symbol.
Calculating Principal and Ending Balance
In cell E3, enter the formula for the principal: `=C3-D3` to calculate the principal paid each period. Drag the fill handle down to cell E305.
To calculate the ending balance, in cell F3, enter the formula: `=F2+E3` and drag it down to cell F305. In cell F2, enter the loan amount: `$200,000`.
Congratulations! You have now created a comprehensive 30-year mortgage amortization schedule in Excel. Regularly reviewing this schedule can help you understand how much interest and principal you're paying, and when the loan will be paid off.
To take your skills further, consider exploring more features in Excel like conditional formatting, data validation, and pivot tables to enhance your amortization schedule or create entirely new financial tools.