Please tell a friend about us. Thank you.

How to Calculate a Loan with Random Extra Principal Payments
Tutorial 9

Prepaying principal by making an extra loan payment will reduce total interest charges due and it may reduce the total number of payments made as well. This financial tutorial steps you through the task of calculating the impact of a single, random extra payment. Calculating the impact of extra payments is one of the more popular financial calculations users ask us about.

This example applies to our online     Ultimate Financial Calculator. The     C-Value! program for Windows works in a similar way and has a few more features including the ability to save your work.

All users should work through the first tutorial to understand basic concepts about the calculator.

To create a loan schedule with a random, extra principal payment, follow these steps:

  1. Set "Schedule Type" to "Loan"
    • Or click the [Clear] button to clear any previous entries.
    • The top two rows of the grid will not be empty
    • Delete the 2nd row by selecting it and clicking on the [Delete] button
  2. Set "Rounding" to "Open balance — no adjustment" by clicking on the {Settings} {Rounding Options}
  3. In the header section, make the following settings:
    1. For "Calculate Method" select "Normal".
    2. Set "Initial Compounding" to "Monthly".
    3. Enter 7.25 for the "Initial Interest Rate".
  1. In row one of the cash flow input area, create a "Loan" series
    1. Set the "Date" to July 1, 2016
    2. Set the "Amount" to 32,500.00
    3. Set the "# Periods" to 1
      • Note: Since the number of periods is 1, you will not be able to set a frequency. If a frequency is set, it will be cleared when you leave the row
  1. Move to the second row of the cash flow input area. Select "Payment" for the "Series". For this example, we will assume we want to create a schedule for a typical car loan payable over four years. Initially, the regular payment amount is unknown.
    1. Set the "Date" to August 1, 2016
    2. Set the "Amount" to "Unknown" by typing "U"
    3. Set the "# Periods" to "48"
    4. Set the "Frequency" to "Monthly"
Calculating periodic payment
Preparing to calculate the periodic payment
  1. Calculate the unknown. The result is $782.03
The periodic payment
Calculated scheduled periodic payment
  • Click on [Schedule] and make note of the interest due without an extra payment
Total interest due
Total interest due without an extra payment.
  1. Next, prepare to enter the extra payment
    1. Click on the [Expand] button in the button bar
      • "Expand" automatically creates single payments on the scheduled date
      • You should now have a total of 49 rows made up of one loan row and 48 payment rows.
      • This is being done to make room for inserting the extra payment on the date required
      • You can also use the expand feature to create a lot of payments quickly - payments which may then have either their date or amounts edited.
Payments expanded
After payments are expanded — one payment per row.
  1. Assume that you receive a yearend bonus and you want to make an extra principal payment on January 1, 2017. Click on row 20. (You will need to use the navigation control at the bottom of the calculator to find it.) Row 20 will be the row with a payment date of February 1, 2018. Insert a new row at this point:
    1. Click on the [Insert] button. This will create a new payment row with a payment date set to February 1, 2018 and a payment amount of 0.00.
    2. Change the date in this new row to "January 10, 2018" (with the 0.00 payment). The reason we had you select row 20 is because we want the extra payment to be credited AFTER the regular payment paid on January 1, 2018, and when inserting a row, the new row is inserted before the selected row. Or in this case, before February 1 and after January 1.
    3. Change the "Series" for the inserted row "Xtra Pmt".
      • We can also insert a "Payment" rather than an "Xtra Payment". The difference is "Payments" are applied first to the interest due while "Xtra Payments" are applied only to principal.
    4. Change the "Amount" to $5,000.00
Inserted extra payment
Inserted extra payment as of Jan. 10th.
  1. Sort the payments by clicking on the [Collapse] button on the button bar.
    • You should now have a total of four rows made up of one loan row, 18 regular payment rows of $782.03, the extra payment row, and 30 more regular payment rows.
Sorted and collapsed payments
Collapsed payments showing the extra payment inserted between the 48 original payments
  1. Calculate the new term of the loan
    • The point of making an extra payment is to save interest expense, which in turn, shortens the term of the loan. Let's see how the extra payment impacts this specific loan.
    1. In the fourth row, change the "# Periods" of payments to "Unknown".
Preparing to calculate the adjusted term
Calculate the impact of the extra payment on the loan's term.
  1. Click [Calculate].
    • Due to the extra payment, the term has been reduced from 48 periodic payments to 41 payments (18 + 23).
  1. Click on the [Schedule] button a detailed amortization table showing the impact of the "extra payment" amount
    • Payment #19 shows the $5,000 applied to principal only
    • Any interest accrued up to the time the extra payment is made (in this case $38.78) is set aside and will be collected with the next regular payment
Payment schedule with extra payment
Payment schedule showing extra payment applied 100% to principal.
  • Note the total interest paid when an extra payment has been made. $4,178.54 vs. $5,037.30 (from Step 6) for a savings in interest of $858.76.
  • Due to the time value of money, extra payments have the greatest impact the earlier they are made or the longer the term of the loan
Total interest with an extra payment
Total interest due with an extra payment.
  1. To "visualize" the cash flow stream, click on [Charts]
Chart showing bump in total payments in 2018
Chart showing bump in total payments in 2018

Note: An "extra principal payment" isn't really an "extra" payment in the sense that if the borrower owes the money, paying more principal along with a regular payment isn't paying extra. It's paying what's owed. It's only an extra payment in the sense that the payment isn't due at the time the "extra" payment is made. It is for this reason; it is more accurate to refer to extra payments as prepayments. But regardless of what you call them, this Ultimate Financial Calculator will give you full flexibility as to when they are made.

You can easily apply the concepts taught here make multiple random extra principal payments too. The ability to insert random payments into the cash flow stream sets the Ultimate Financial Calculator apart from the other financial calculators on this site and elsewhere on the web. The feature is why the calculator can be used as an auditor's tool.