Step 1: Create a Loan Amortization Schedule In this first step of your project, you’ll need to create a loan amortization schedule. The following table illustrates the payments and interest amounts for a fixed-rate, 30-year mortgage loan. The total amount of the mortgage is $300,000, and the interest rate is 6 percent. This mortgage requires monthly payments of $1,798.65, with a final payment of $1,800.23. The table was created in Excel. The following is an explanation of the columns in the table: The first column in the table, with the heading “Payment Number,” shows the 360 payments required to pay off the mortgage loan (30 years, with 12 monthly payments per year). ¦ The second column, with the heading “Payment Amount,” shows the monthly payment amount. ¦ The third and fourth columns show the portion of the monthly payment paid for interest, and the portion paid towards the principal. ¦ The fifth column, headed “Balance,” shows the starting balance of $300,000, and the remaining balance each month after the principal is subtracted. ¦ The sixth column, headed “Current,” reflects the current portion of the principal (12 months). ¦ The amounts in the “Non-Current” column are calculated by subtracting the current portion of the principal from the total balance. ¦ The “Annual Interest Expense” column provides a run- ning total of the interest expense on the mortgage for the entire 12-month period. ¦ The “Totals” under the “6% Interest Expense” and “Principal” columns show the final totals for the 30-year life of the mortgage.
You'll get a 119.0KB .XLS file.