Lump sum payments

1. Lump sum payments

In this lesson, we'll be looking at a special case on some loans where normal payment rules don't apply. These are open loans, where additional payments can be made throughout the loan.

2. Closed loans v. Open loans

Throughout the course so far, we have been working with closed loans. These loans have a fixed schedule where payments need to be made on a schedule that cannot be modified by increasing or decreasing installments. On actual closed loans, there is a fee associated with paying down a loan early designed to compensate lenders for their lost interest payments. On an open loan, the payment schedule is still required. Open loans are not revolving credit like credit cards where there is no fixed term. The payments dictate the minimum payment required, but payments over the minimum can be made without penalty and will be applied against the principal. If the full loan is paid off early, there is no penalty, and the loan is discharged.

3. Adding lump sum column

When you add the lump sum column to the schedule, there is no requirement to enter any values in as lump sums. These new entries need to be made in the period when they occur, and need to be positive values. It will be assumed that all payments will happen at the end of the period. These payments will not impact your period, date, opening balance, or loan to value calculations. Everything else, however, will change.

4. Final payment adjustment

The first change is to the PMT() function. When a lump sum payment is made, it will not reduce the monthly payments. The only change will be that the number of periods will be reduced. However, in the final period, the payment could exceed the balance remaining on the loan. In this case, an adjustment is required to reflect the principal and interest in the final period.

5. Final period payment calculation

For the payment formula, calculate if the normal payment, calculated from the PMT() formula would be larger than the opening balance plus interest. If it is, then it is not the final period, and the PMT() formula can be used. Otherwise, use the calculated opening balance plus interest. In an example, the PMT() formula has calculated $1000. The opening balance in the period is $500, and interest is 6% APR calculated monthly. Since the $1000 payment exceeds the $500 plus interest, the payment exceeds the interest plus opening balance of $502.50 and the payment formula returns $502.50.

6. Don't use financial functions!

Since the schedule changes when a lump sum is paid, the financial functions for calculation of interest and principal should not be used. These include the periodic payments, IPMT() and PPMT() as well as cumulative payments, CUMIPMT() and CUMPRINC().

7. Manual calculations

Since financial formulas cannot be used, except the PMT() function which must be done first in order to properly calculate the principal payments, the calculations of interest and principal payments must be done manually. For interest, take the opening balance and multiply by the APR divided by the number of annual periods. For principal payments, subtract the interest from the total monthly payment calculated by the PMT() function. Finally, for the cumulative payments, simply add the current month principal or interest to the prior cumulative principal or interest.

8. Let's get lumpy!

Time to make some lump sum and manual payments, and I'll see you in the next lesson!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.