Do you owe money, or does someone owe you money?

And do you want to track payments on the date paid and recalculate or confirm a debt balance?

That's exactly what the Ultimate Loan Payoff Calculator (ULPC) will do for you.

And unlike other online payoff calculators, **this calculator will track payments made on any date, and calculate an exact balance**. Need to know the payoff amount for a loan as the twentieth of the month? This calculator will handle the task easily.

What if the borrower **missed a payment**?

Or what if they made an **extra principal payment**?

Or what if the **interest rate changes**?

Once again, **no problem**. The below calculator will handle any of these scenarios with ease.

Further, not only will you know the exact balance, you'll be able to create and print a detail amortization schedule complete with payment dates, subtotals, and running totals.

#### Info...

**File save and open are new beta test features.**If you happen to get a different calculated result, do not assume that this calculator is making an error. Most likely, the problem is with the new file load feature. Please check that all settings got loaded as expected.

**Calculating a loan payoff amount as of a specific date is easy with this calculator. The calculator considers all on time late, missed and extra payments. It can also accommodate payment and interest rate changes.**

- The Ultimate Loan Payoff Calculator will do the job if you are searching for any of these calculators:
- loan repayment calculator
- mortgage payoff calculator
- student loan repayment calculator
- home loan repayment calculator
- car loan repayment calculator
- debt payoff calculator
- debt repayment calculator
- or any early loan payoff calculator

This example also 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.

## How to Calculate a Mortgage or Loan Payoff Amount

Tutorial 25

## Step-by-Step — Calculate a loan payoff amount or exact date balance

To calculate a mortgage or loan balance and record payments as they are made, follow these steps:

- 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

- Set "Rounding" to "Open balance — no adjustment" by clicking on {Settings} {Rounding Options}
- This setting is what allows you to enter single payments
- Any other setting forces the calculator to adjust the final payment so the balance is zero

- Set the "Days Per Year" option to "360 Days Per Year" by clicking on the {Setting} {360 / 365 Days}
- In the header section, make the following settings:
- For "Calculate Method" select "
**Normal**". - Set "Initial Compounding" to "
**Monthly**". - Enter
**5.25**for the "Initial Interest Rate".

- For "Calculate Method" select "

- In row one of the cash flow input area, create a "Loan" series
- Set the "Date" to
**February 16** - Set the "Amount" to
**5,250.00** - 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

- Set the "Date" to

- Frequently, the next step is to calculate the regular, periodic payment amount if you don't already know it. For this example, we'll assume that the payment has not yet been determined. If the payment has been agreed to, you can skip to
**step #8**.

- The borrower has agreed to pay the loan back in 24 equal payments due at monthly intervals. What is the payment amount?

- Enter known payment details in second row
- Set the series to "
**Payment**" - Leave the "Date" set to
**March 16** - In the "Amount" column type "
**U**" for "Unknown" - Set the number of periods to
**24** - Set Frequency to "
**Monthly**". (The "End Date" will be February 16.)

- Set the series to "

- Your screen will now look like this:

- Click the "Calculate" button
- The expected, periodic payment is $230.91

- Now we can start recording payments as they are received. Because we calculated the payment amount assuming 24 payments, we need to edit row #2:

- The 1st payment is received on time. Click on row #2
- Select "
**Payment**" for the series - Leave the date set to
**March 16** - In the "Amount" column enter
**$230.91** - Enter "
**1**" for "# Periods" (recording 1 payment)

- Select "

- Assume the next 3 payments are also received on the due date and for the amount due but you fell behind in recording them in the calculator. It is easy to catch up:

- Click on row 3
- Select "
**Payment**" for the series - Set the date to the
**April 16** - In the "Amount" column enter
**$230.91** - Enter a "
**3**" under "# Periods"

- Select "

- Your screen should now look like this:

- So far, all payments have been received for the amount due and on the due date. Let's check out the loan payoff amount after these 4 payments are made:

- Click on the "[Schedule]" button
- As of June 16, the payoff amount is $4,412.77

- The borrower is reliable and not only does he pay the 5th payment early, he also pays an extra $100.

- Normal payment plus an extra payment. To record this:
- Click on the fourth row. Set the series to "
**Payment**" - Set the date to "
**July 10**" - Set the "Amount" to
**$330.91**. (Includes the extra $100.00.) - Set the "# Periods" to
**1**

- Click on the fourth row. Set the series to "

- So much for the debtor being reliable.

- Missed payment followed by under payment:
- Click on the fifth row. Set series to "
**Payment**" - Set the date to "
**September 16**" - Set the "Amount" to "
**$180.91**" - Set the "# Periods" to
**1**

- Click on the fifth row. Set series to "

- After making 4 regular payments, as well as one early payment with an extra $100.00, missing a payment and making a payment that is short by $50.00, your cash flow data screen will look like this:

- Note: It is not necessary to enter a '0.00' for the missed payment. But it can be done as a matter of record keeping. Doing so, explicitly acknowledges that a payment was missed and it also forces the balance to be calculated on the amortization schedule as of the date of the missed payment.

- Note: Interest is being calculated through August 16 and it is being added to the balance.

- Your borrower is in need of additional cash. You agree to lend it to them and add it to the loan balance.

- Add an additional loan
- Click on the empty row after the last payment. This will be row six
- Select "Loan" for the series
- You will make the funds available on October 1st. Enter
**October 1**in the Date column - In the "Amount" column enter the new loan amount
**$1,000.00** - You are making one loan. Enter a "
**1**" for "# Periods"

- Since there is a new loan amount, you want to calculate a new payment amount. Also, the borrower has agreed to pay the loan off in 18 more payments.

- Adjust payment due to new borrow
- Click on the empty row after loan just entered
- Select "
**Payment**" for the Series - The payments will continue to be due on the 16th of each month. Set the "Date" set to "
**October 16**" - In the "Amount" column type "
**U**" for "Unknown". - Set the "# Periods" to
**18** - Set "Frequency" to "
**Monthly**"

- Before clicking "Calculate", your screen will look like this if you've been following along:

- Click "
**[Calculate]**"- The new payment will be
**$286.78**

- The new payment will be

- Next, borrower pays the full payment amount, however he does pay two days late:
- Edit the payment in row 7.

- Leave "Series" set to "
**Payment** - Change the "Date" to "
**October 18**" - Since the full payment is paid, leave the amount as it is,
**$286.78** - Only one payment is being made. Change the "18" to "
**1**" in the "# Periods" column

- Continue to enter payments (and loan advances) as they are received until the loan is paid off. Remember, you may enter "0.00" payment amounts on any date to calculate the payoff as of the date entered.

TValue is a trademmark of TimeValue Software.

## Bill Greer says:

I hit a snag. When I follow the tutorial and click on “Calculate,” this message comes up:

“APR” cannot be calculated when “Rounding” (under “Settings”) is set to “Open Balance”. “APR” calculation can be turned off under “Loan Options”.

When I go to Loan Options, I see no reference to APR and so no way to turn it off.

What am I missing? Thanks for an excellent website!

## Karl says:

Hi Bill, first I’m sorry I missed your post. I try to answer all questions in less than 24 hours and I see that I missed yours from Sunday (my time).

Thank you for bringing this issue to my attention. I need to update the instructions or change the calculator defaults.

To fix this…

Go to "Settings". Click on "Analytics" and uncheck the "Include internal rate of return (IRR) on schedule"

You may also need to turn off "Include Regulation “Z” APR Disclosure calculation in schedule?" Click on "Loan Options" in the first row to access.

(Thanks for the compliment.)

## Judy Krawiec says:

I hit a snag When I followed the tutorial on an amortization schedule to calculate adding additional money to Principal each month to gain Equity quicker, payoff the mortgage sooner & get rid of MI as fast as you can.

I followed all of the tutorial and the message I got was that a number larger than “1” had to be in the field that I am directed to put UNKNOWN in the field (# of periods).

I honestly would love to understand what each field is “Happy” with. This is an amortization that I try and instill in all my clients. Builds Financial Stability and Credit.

Thanks so much!

## Karl says:

Several things, first, I need to change some of the default settings so that users do not run into warning messages. You didn’t mention any issue around these items, but I recommend that you:

Go to “Settings”. Click on “Analytics” and uncheck the “Include internal rate of return (IRR) on schedule”

You may also need to turn off “Include Regulation “Z” APR Disclosure calculation in schedule?” Click on “Loan Options” in the first row to access.

For your specific item with the number of periods, you can enter UNKNOWN if the values for the other columns are entered. EXCEPT, if you are also trying to set a special series (Cash Flow Options) in that row, then the number has to be greater than 1 to open the Cash Flow Options window. Then, once you’ve made your selection, and closed the window, then you can go back and set the number of payments to UNKNOWN. (Yes, I could design this better.)

If this does not get you going, then please tell me what tutorial you are following and what specific step you get stuck on.

The calculator absolutely works. The problem is, I need to get the kinks out of the documentation, because there are some quirks.

## Ange says:

Hi, I am trying to setup the calculator for a loan payoff that has fixed monthly principal payments and 1 yearly payment of the interest due. I tried setting up the principal first series with an additional payment at the end of the year where I changed the cash flow option to interest only, but that seems to add more interest than what is actually due. Any tips?

## Karl says:

The thing with calculators is "it seems" isn’t valid. It either is, or isn’t. 🙂

Are you saying the other payments are as you want them, and it’s just the interest only payment that’s a problem?

If you want to send the exact details, I’m happy to take a look. However, please note, if you have an interest rate that is not 0%, and the other payment are paying only the principal, when it comes time to pay the interest payment, the interest amount will equal the accrued interest since the beginning of the year.

## Irene von Engelhardt says:

Hi, Im having a problem My borrower has to pay an annual payment over 10 year

period, but he pays different amounts during the year, which calculator is best to use. ?

## Karl says:

This calculator will track the loan balance for you. Have you tried it? Do you have any questions? I’ll be happy to try and answer them.

## Tom says:

I need a form which shows the running balance owed! I had the starting amount Jan 1 2019, he has made periodic irregular payments and I now want to know what he owes me December 31?

Thanks

Tom

## Karl says:

Hi Tom, there’s not really a question here. Are you telling me how you used the calculator? If you have a question about how to do this, did you read the step-by-step guide on the calculator’s page? If you did, and that’s not clear, no problem. Just ask your question about what’s not clear.

## Tom Warth says:

My apologies if not clear!!!

What I need is a form which has a column which shows the outstanding balance after each payment is made. Am I missing something(not being an accountant or statistician)

Tom

## Karl says:

Hey Tom, no problem! That’s exactly what this calculator will do. When you first enter the page, the calculator will have some row prepopulated as an example. Without changing anything, click on the "Schedule" button. That will show you the end result. The loan balance after each payment is shown on the right.

## Shauna says:

Hi- Can the ultimate loan payoff calculator be used on a loan with a balloon payment after 10 years? The note has a set interest rate and a set monthly principal payment, but does not identify what the balloon payment amount is. Debtor is 8 years into this 10 year debt and I need to get amortization schedule with final calculations for him. Please advise as to the best tool for doing so.

Thanks

## Karl says:

Hello.

>>Can the ultimate loan payoff calculator be used on a loan with a balloon payment after 10 years?

It sure can.

Since there is already an existing loan, probably the best thing to do is to enter the payments which have been made. Then in the next row, enter the payments that are scheduled, but not yet made. So perhaps something like

Nov. 1, 2019, $1,500.00 23

And then add one more final row with an unknown payment amount.

Nov 1, 2021 Unknown 1

Let me know if this doesn’t get you to where you want to be.

Also, here are two balloon payment tutorials.

calculate the balloon amount

calculate the payment required that results in the desired balloon amount

## Trent says:

Hi, I’m the lender to a family member for a home loan. They began making regular monthly payments to me beginning February 8th, 2018. (Payments are due on the 8th of each month).

payments have been and remain current and beginning sometime in 2020, they wish to begin making extra random principle payments (in the months they can afford to do so) and in varying amounts.

I guess I’ll then need to be able to take those ‘extra’ principle amounts and somehow re-calculate the balance of the loan after each and every ‘extra’ principle payment? Not sure what to do. They’re goal is to simply reduce the overall length of the note over time. Regular monthly payment amount will remain the same.

Borrowers said that the months they do end up doing this, they’ll send the full payment along with the extra principle amount on the 8th of the month. (payment due date). Some months may contain extra payments and some months may not.

Amount borrowed is 305,000.00.

Interest is 7.0%

Length of note is 300 months (25 years)

Monthly Payment amount is: $2155.68

1st payment was February 8th, 2018

For their sake and mine as it would relate to the ongoing accounting for such note, how may I properly calculate/account for this. Is this the calculator I need for such calculation(s)?

Thanks in advance for any help and/or recommendations.

## Karl says:

Yes, this payoff calculator will do what you want i.e. track and adjust the balance as needed depending on the actual payment amount received and when it was recewived.

I suggest stepping through the tutorial to get an overview.

Of course, with this online calculator, you will not be able to save your entries. So you might want to print out the results. Then you can reenter the values when more payments are made.

If you want to save your entries to avoid reentering the payments, please see the software application C-Value! (link at the top of any page). C-Value! is a Windows program and costs $49.95.

## Sandra Tuszynska says:

Hi Karl, thank you so much for such a fantastic tool, there is indeed nothin like it anywhere else online that I could find.

I am about to payoff my vendor finance loan and have used your calculator to see how much I need to pay out as I have not been receiving statements. I’d like to check with you if the initial compounding option calculates daily compounded interest coming off the loan?

This is the only tricky bit as all else is simple as I’ve been paying recurring amounts weekly, but I don’t know what that translates to, given the interest is calculated daily paying both principal and interest.

Any feedback would be greatly appreciated, and once more, thank you so much for the great gift.

Sandra

## Karl says:

You are quite welcome. Glad you are finding it useful.

To your question, I’m not 100% clear on what you mean by "coming off the loan," but, if the terms of the loan call for daily compounded interest charges, then the second option in "Initial Compounding" is "Daily." compounding.

In case the word "Initial" is confusing things, that just means it’s the first compounding frequency from when the loan originates. Compounding frequency can later be changed, if necessary (under rate change). I think that would be very very rare.

## Paul says:

Hello Karl,

First, excellent calculator!

I am trying to calculate a “discounted” loan pay-off amount as of July 6, 2020. The loan originated in December 1, 2015 and the loan amount was $100,000. It is amortized over 20 years.

As per the note, if I pay it off within 5 years of the loan origination (before December 1, 2020) then I would receive a 2% discount off the principal amount. Payment is due and made on the first of each month.

I added a row for July 6, 2020 with $0.00 so that it shows up on the schedule with a balance of $85,402.31 ($62.23 for 6 days of accumulated interest)

I am able to figure out regular payoff amount as of July 6, 2020, however, how do I ask the calculator to show me the “discounted” payoff amount that reduces the principal balance by 2% and adds 6 days of interest? (e.g., I would like it to discount 2% of $85,340.08 balance of July 1st and add 6 days of interest to give me the payoff).

Thank you in advance for your help.

## Paul says:

Sorry, I forgot to mention that interest rate is 5.25%.

Thanks,

Paul

## Karl says:

Hi Paul, thank you. Glad to hear you think the calculator is "excellent."

I’m not sure I’m clear on what is meant by a "discount off the principal amount." But here are my thoughts.

Set up the calculation has you have before and make your last row a row dated July 1, 2020. Set the amount to "Unknown." and take the original loan amount in row one and change it to $98,000 ($100,000 less 2%). Solve for the unknown. That gives you the balance for the loan with the payments as paid and a 2% discount on the principal.

Finally, enter a row for July 6 with an unknown amount and then calculate. This final calculation is going to add the 6 days of accrued interest.

Let me know how this works out for you.

## Brenda says:

Can this worksheet be saved, so we can continue entering the payments as they are made? As there is time still left on the loan.

## Karl says:

Yes. Please click on "File", then "Save as…".

## Brenda says:

Thank you!! It worked … take care

## Brenda says:

Oh no ….

It only shows the first page when it is saved. and I can’t modify anything. Looking to be able to continue adding to it monthly and not have to enter all payments and information again. Thanks

## Karl says:

That doesn’t sound good. Are you saying that you had payments on the second page, and after you saved the file and reloaded it, the 2nd-page payments weren’t loaded?

Or ( 2 ), are you saying that after you loaded the file, there was no 2nd page available so that you could enter more payments? If #2, can you please try again? This time click on the Insert button next to the file button. That should add an empty frow for you as you need them.

If you still have a problem, can you send me your file to the email address on the contact page of this site? The file save feature is a new feature being tested, and there could be a problem I need to fix.

## Brenda says:

Thank you Karl,

Once I realized and learned about the File Save and the File Open, it works perfectly! What a time saver!!

Thank you for all your hard work and your persistence to help me understand.

You’re the BEST!

Take care

Brenda

## Karl says:

Thank you for letting me know. I’m glad the calculator is working for you.

## Kelsey says:

Love your calculators. I have a commercial promissory note for $123,785 at 6.5% covering 15 years originating on 11/01/2019. Payments of $3,246.15 are due quarterly, first payment being due 2/1/2020. Interest is compounded exact/simple and accrues using an actual/365 days counting method. I have decided to go ahead and pay monthly. The first couple payments totaled more than the first quarterly payment then I started paying a standard $1,500 a month. I’ve put in all my payments and payment dates and I was comparing them to my bank statement but for some reason the interest/principal amounts are not jiving. I’m not sure if I have selected a wrong option somewhere or if it could be that my bank is funny in that if you make an early and/or over payment then instead of just applying to principal, it delays your next payment due date. Does all this make sense? Is this what is causing the issue or have I not selected some of the correct options? For example I entered my first payment of $2,164.10 made on 12/10/19. Calculator says interest 661.32 and principal 1502.78. Bank applied as interest 859.71 and principal 1304.39. Hopefully I have included all relevant information. Thank you in advance.

## Kelsey says:

Correction, first entry was correct. Subsequent entries are off.

Bank:

Date Balance Payment Principal Interest

11/1/2019 $123,785.00

12/10/2019 $122,480.61 $2,164.10 $1,304.39 $859.71

1/6/2020 $119,823.08 $3,246.15 $2,657.53 $588.62

3/16/2020 $119,812.68 $1,500.00 $10.40 $1,489.60

5/4/2020 $119,355.31 $1,500.00 $457.37 $1,042.63

6/8/2020 $118,597.21 $1,500.00 $758.10 $741.90

7/1/2020 $117,581.64 $1,500.00 $1,015.57 $484.43

7/31/2020 $116,708.10 $1,500.00 $873.54 $626.46

8/31/2020 $115,850.63 $1,500.00 $857.47 $642.53

10/1/2020 $114,988.45 $1,500.00 $862.18 $637.82

10/30/2020 $114,080.67 $1,500.00 $907.78 $592.22

11/30/2020 $113,208.73 $1,500.00 $871.94 $628.06

1/13/2021 $112,594.03 $1,500.00 $614.70 $885.30

2/1/2021 $111,475.00 $1,500.00 $1,119.03 $380.97

3/3/2021 $110,570.55 $1,500.00 $904.45 $595.55

Calculator:

Date Payment Interest Principal Interest

11/01/2019 0.00 0.00 0.00 123,785.00

1:1 12/10/2019 2,164.10 859.71 1,304.39 122,480.61

2:1 01/06/2020 3,246.15 588.91 2,657.24 119,823.37

3:1 03/16/2020 1,500.00 1,493.69 6.31 119,817.06

4:1 05/04/2020 1,500.00 1,045.53 454.47 119,362.59

5:1 06/08/2020 1,500.00 743.97 756.03 118,606.56

6:1 07/01/2020 1,500.00 485.80 1,014.20 117,592.36

7:1 07/31/2020 1,500.00 628.23 871.77 116,720.59

8:1 08/31/2020 1,500.00 644.36 855.64 115,864.95

9:1 10/01/2020 1,500.00 639.64 860.36 115,004.59

10:1 10/30/2020 1,500.00 593.93 906.07 114,098.52

11:1 11/30/2020 1,500.00 629.89 870.11 113,228.41

12:2 01/13/2021 1,500.00 887.21 612.79 112,615.62

13:2 02/01/2021 1,500.00 381.04 1,118.96 111,496.66

14:2 03/03/2021 1,500.00 595.67 904.33 110,592.33

## Karl says:

Glad you like the calculators.

The interest calculation goes out, as you pointed out, with the second payment. Once there’s a discrepancy, the 2 schedules will never match. This is because, interest is calculated on the balance, and the balance do not agree after the first payment.

Either, the bank has made an error in their calculation, or they are not calculating the interest as you describe.

Here’s the math behind the interest accrued as of the 2nd payment:

Balance – 122,480.61

Rate – 6.5% or 0.065/365 = 0.0001780 daily rate

Dec 10 – Jan. 6 is 27 days.

Thus 27 * 0.0001780 * 122,480.61 = 588.914 = $588.91

Which is the number the calculator calculates for interest with the 01/06/2020 payment.

I did look at the bank’s calculation, but could not figure out how they are coming up with 588.62. Are you sure they are using a rate based on days and not a monthly rate, i.e. 6.5%/12? I did not attempt that calculation because I did not know how they would account for the odd days.

The bank should be able to document their math. I would love to hear what they say.

## Kelsey says:

I have discovered the issue. The problem occurs during a pay period that spans a year change that includes a regular year and a leap year. Part of the days should be calculated at 365 and part at 366. For example the payment made on 1/6/20 should be calculated as follows:

12/10-12/31

0.065/365*22 days*122480.61=479.86

PLUS

1/1-1/5

0.065/366*5 days*122480.61=108.76

Add those two together and you get $588.62. This would not be relevant if all payments made in the new year were always made on 1/1 but mine never are. If you make this correction, all the subsequent calculations are correct (except a few rounded pennies here and there) till you get the end of the leap year and have to make the correction again and then when more leap years occur. Not sure if this is normal practice and if your calculator would need the correction.

## Karl says:

Thanks, Kelsey. I’ll take a look. I had thought of this possibility when replied, but I had done the math and came up with $588.56 somehow. I must have had a number transposed somewhere. I wish I had saved my work. 🙁

This will be a significant update. I’ll take a look over the next 30 days.

Concerning whether it’s normal practice, I had not run into it until recently, but some lenders do follow a 366 day leap year convention when the period spans a non-leap year/leap year.

## Karl says:

Kelsey, I posted today an update to the calculator the supports 366-day years. I’ll send you a file that you drag and drop to load your example. The final balance differs from you bank example by one cent. If I trace the one cent back to where there is the first difference, I see it here:

Your example has $741.90 for the interest on June 8, 2020. However, this is the arithmetic:

prior period’s balance times daily rate 6.5/100/366 times number of days

= 119,355.31 * 0.0001775956 * 35 = 741.8943 or $741.89

Most likely, the bank’s calculation does not maintain the same degree of precision in the rate as this calculator does.