How to Build an Amortization Schedule to Account for a Prepayment Curtailment
A curtailment is a payment to reduce the repayment length of a loan. If you are building an amortization schedule to account for a curtailment of a long-term loan, such as a mortgage, you may want to do so in a spreadsheet document because the schedule is quite lengthy. If you don't have a spreadsheet program, you can still complete the schedule by hand, but it will take much more time.
Other People Are Reading
Instructions
-
-
1
Write down the constants: the total loan amount, interest rate, total number of payments and total monthly payment amount. You also need to write down the prepayment amount.
-
2
Organize the schedule. You need eight columns. In column one, enter each payment's due date. Column two is the payment number, three is the interest paid, four is the principal paid, column five is the amount of the prepayment, six is the total amount of principal paid for each payment period, seven is the remaining principal balance and eight is a running tally of cumulative principal paid.
-
-
3
Calculate the interest payment. Divide the interest rate by the number of periods and multiply that number by the principal loan amount. For example, if the interest rate for which you make monthly payments is 7 percent and the principal amount is $10,000, then the monthly interest payment on your first payment is 0.7/12 x $10,000 = $583.33. Put this number in column three.
-
4
Calculate the principal payment. To do this, subtract the interest payment from the monthly payment amount. Put this number in column four.
-
5
Write down the prepayment in column five. You need to write this down or enter this number in the spreadsheet to keep track of the additional principal you are paying each month.
-
6
Add the total principal paid. This number goes in column six and is the total amount of principal you paid with your normal monthly payment plus the extra amount you paid toward your principal.
-
7
Calculate your remaining principal balance. Subtract the principal payments you made for the month, from the beginning principal amount. This number goes in column seven.
-
8
Calculate the next period's interest payment. Divide the interest rate by number of periods and multiply that number by the remaining principal loan amount in Step 6.
-
9
Repeat Steps 3 through 8. Continuing repeating these steps until your remaining principal amount is zero.
-
1
