nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Mortgage Problem: Amortization Inaccurate

Chris Harris

Greetings,

I am at a loss; please help.

Our Mortgage began in 2011 and I started using Quicken around 2014. This means that the first 26 transactions were entered as balance adjustments and not as individual transactions which makes things a bit messy.

The main problem: My payoff date is showing as April 2039 and the statements shows November 2041 (a 31 month difference).

In trying to track down every possible reason, I found that when I go to Property & Debt > Mortgage Principal > Payment Details > View Payment Details, it shows me this...

Image: https://us.v-cdn.net/6031128/uploads/editor/s6/z4a936acycwr.png

Note the line between 69 & 70 which shows a payment of $278.27.

I can't find what this is. When I search for "All Transactions" for 278, nothing shows up.

What is this transaction? Is it normal?

Find more posts tagged with

Accepted answers

All comments

Tom Young

I don't see a payment of $278 or $278.27 in your picture. What I'm seeing is an interest rate change that presumably took place 8/1/2019. Is this a variable rate loan?

Chris Harris

The $278.27 appears too the right of "Payment:" when you scroll over This is a fixed 4.5%, not a variable rate.

Chris Harris

Thank you Tom. The $278.27 appears too the right of "Payment:" when you scroll over This is a fixed 4.5%, not a variable rate.

Boatnmaniac

(Revised to add additional comment regarding mortgage interest in step 5 below.)

Chris Harris - It is not a payment. It indicates that something was done or changed that cause Quicken to recalculate the remaining payoff schedule. The dollar amount there is the new recalculated monthly payment amount going forward. Some of the things that can cause this include making a payment amount that was different from what was scheduled, missing a payment or making an additional payment, changing the next payment due date, changing the interest rate, changing the number of payments, etc. Sometimes this will result in a payment amount change (i.e., an interest rate change) and other times it will keep the payment amount the same but will push out (or pull in) the payoff schedule, the latter of which appears to be what happened in your case.

I suspect your issue is caused by the balance adjustments that were made. Those adjustments go against the principle but do not account for the monthly interest that was due for those first 26 months. Quicken will see that interest as not being paid so it will accrue and compound it as debt still to be paid and will then push out the end date of the mortgage to account for it.

You should be able to fix your issue with the following process (I tested it in Quicken and it worked for me).

Back up your Quicken file for quick retrieval should something go wrong.
Pull together your mortgage statements for the months of the balance adjustments. They should show how much of your payments were applied to principal and how much were applied to interest.
For each of those balance adjustment transactions, click on the category and make it a split category.
For the Balance Adjustment line, confirm with your mortgage statement that it is for the correct principal paid amount. Adjust the $ amount to match your statement if needed.
On the 2nd line in the split, add a transaction for the interest paid (in my Quicken the default for this is "Loan: Mortgage Interest") and enter the interest amount shown on the statement. (It doesn't matter which category you use for this interest line. Quicken will apply what is in this 2nd line against the mortgage interest. It is the split category that is important. If you enter a separate single category transaction Quicken will apply it against principal, no matter how that is categorized.)
Click on "Adjust Total". The "Transaction Total" should now match the total of what you actually paid.
Click "OK" to close the split category popup and then click "Save".
Pull up the "Payment Schedule" and you will now see that the interest has been added to the Interest Paid column. You will also see that the last payment date has been pulled in some (or the amount owed for the last payment has been reduced).
After all the transactions have been updated, your last payment date of the mortgage should now be correct.

Tom Young

"The $278.27 appears too the right of "Payment:" when you scroll over"

Unfortunately a picture of that window doesn't scroll.

"This is a fixed 4.5%, not a variable rate."

The notation that you're seeing there is what you see when you do have a variable rate loan and change the rate. That's a user-initiated entry though the total payment didn't change between payment 69 and 70 so it doesn't seem to have affected anything, really. I don't think that's a factor in your payoff problem.

Is the current principal balance correct? If it is then I'd suggest continuing to scroll down the payment schedule to see if anything else jumps out at you. If the current principle balance isn't correct then you have some sort of historical problem.

Sherlock

To correct the issue, I suggest you recreate the manual loan by essentially following the guidance provided at: https://community.quicken.com/discussion/7164936/faq-how-do-i-convert-a-loan-account-that-automatically-downloads-transactions-to-a-manual-entry-sc

Chris Harris

@Boatnmaniac thank you for the really detailed reply. There's a lot there and I'm working through them one by one. You said this...

"I suspect your issue is caused by the balance adjustments that were made. Those adjustments go against the principle but do not account for the monthly interest that was due for those first 26 months. Quicken will see that interest as not being paid so it will accrue and compound it as debt still to be paid and will then push out the end date of the mortgage to account for it."

In my case, the statement's payoff date is November 2041 while Quicken's payoff date is April 2039 (earlier). If Quicken were viewing interest as being owed, wouldn't it push the maturity date out into the future?

I agree that I'll probably have to just enter those pre-Quicken transactions but I haven't built up the nerve yet. I suspect it's going to mess with opening balances and already-reconciled transactions. Do you have any suggestions on how to handle those.

Chris Harris

@Tom Young thanks for the help.

You said "that's a user-initiated entry". Where would I have entered it? I've confirmed that every register transaction is accurate. Details below.
Where is the setting in Quicken that makes a loan Variable vs. Fixed?
The current principal balance in Quicken and on the statement DOES match.

Details:
I did find one possibly related problem and fixed it. Awhile back, I thought I had the brilliant idea of adding a category to line 5 that was solely a note reminding me about mortgages not syncing to mobile. Later on, after reading numerous forum posts, I realized just how terrible this idea was for mortgages specifically and undid it; but I missed deleting line 5 for the 8/1/2019 transaction in particular - it still had a note. So, I created a new transaction and deleted the old one entirely. Unfortunately, this didn't get rid of that line between 69 & 70 in the first screenshot (original post).

Image: https://us.v-cdn.net/6031128/uploads/editor/ny/801si3bk6ytl.png

Tom Young

@Chris Harris

Is the current balance in this Account correct? Has the balance ever been correct?

When you set up a loan in Quicken that originated prior to "today" using Quicken's loan "wizard", Quicken makes an entry in the Account for the original loan balance notated as the "Opening Balance" and then makes another entry, the "Balance Adjustment", which brings the loan "up to date" based on its calculations.

In your original post where you said "This means that the first 26 transactions were entered as balance adjustments and not as individual transactions..." I assumed that what happened is that you went back into your checking Account, found those first 26 loan payments and modified those transactions by making them "split" entries - some interest and some principal. That should not be the source of your problem if you made sure that the total of the 26 principal payments agreed to the Quicken wizard's Balance Adjustment, and then deleted that Balance Adjustment. Do you do that correctly? Hence my questions about if the loan balance was ever correct in Quicken.

If your checking Account is in balance and the current loan amount is incorrect, then there's another approach to get things squared away, one that doesn't involve a lot of detail scut work.

Zero out the current loan Account by making a Decrease entry in the Account with the "Category" being the same Account, e.g., [Mortgage Loan].
Create a new loan Account but this time tell Quicken that this is a new loan that originated on the date of your most recent loan payment with a balance that's the same as the actual loan balance right after that payment. You'll use the remaining term of your actual loan as the term of this "new" loan, enter the correct interest rate, etc.

That should get the loan correctly stated on your balance sheet with a future amortization schedule that reflects your actual loan.

Tom Young

@Chris Harris

Here's the Quicken Help instructions:

Property & Debt > Managing loans

Change the interest rate for a loan

Open the loan account that you want to change the interest rate for.
If necessary, click the Loan Details button.
Next to the Interest Rate at the top of the window, click edit.
- To change the interest rate for an adjustable-rate loan
  If the loan you set up in Quicken has a variable interest rate, at some point the rate will change. When you change the rate in Quicken, the scheduled payment amount also changes.
  
  To ensure that the payment schedule is updated accurately, adjust the interest rate at any time between your last payment at the old rate and the time your next payment at the new rate is due.
  1. Click Adjustable-rate Loans.
  2. Click Add New Rate.
  3. In the Loan Rate Changes dialog, click New.
  4. In the Effective Date field, enter the effective date for the rate change.
  5. In the Interest Rate field, enter the new interest rate. If you adjust only the interest rate, Quicken uses the new rate to recalculate your payment amount without changing the length of the loan.
  6. In the Payment field, enter a new amount (principal + interest only) for your regular loan payment. (Optional)
    - What happens when I adjust this amount?
      If you change the regular payment amount, Quicken adjusts the length of the loan.
  7. Click OK.
- To change the initial, opening date interest rate for a loan
  You might need to do this if, for example, you entered an incorrect or imprecise interest rate when you first set up the loan.
  1. In the Interest Rate field, enter the new rate.
  2. Click OK.
    - What happens now?
      Quicken calculates a new payment amount using this new interest rate. The length of the loan doesn't change. If you change the regular payment, however, Quicken changes the length of the loan to correspond to the new payment.

Notes

Adjustable-rate loans have a variable interest rate. If you have this type of loan, use the current interest rate when you set up your loan. If you want to estimate your future payments, you can enter expected rate changes to create what-if scenarios.

Boatnmaniac

Chris Harris - Oh, my. I misread your original post and reversed the issue. Sorry about that. You are correct about how if this is an issue with unpaid compounding interest payments not being accounted for properly this process should pull in the pay-off date, not push it out.

Doing what I suggested shouldn't mess with your opening balance but you are right in that it could impact your already reconciled payment transactions because that might make the principal/interest allocations in them incorrect. So you'll probably need to review & adjust each of those, as well.

Assuming that the mortgage was set up correctly (start date, original mortgage amount, number of months, interest rate) then I still think it does boil down to something in the balance adjustments and/or reconciled principal/interest payment allocations causing the issue you are seeing. So, fixing whatever is wrong there should resolve the payoff date issue.

Something else I just thought of: Do your monthly mortgage payments include taxes and insurance (which can periodically change)? If so, are those being categorized properly and not included in your principal and interest?

A couple of ideas that might make it a bit easier for you no matter which troubleshooting path you decide to take:

Instead of working this issue from within your main Quicken file, save a copy of it under a different name. You can then continue to run OSU in your main file to keep your other accounts current. In parallel, in the new name file you can do the same (run OSU to keep your other accounts current) but also work with the mortgage to try to fix it. It's a little more effort maintaining two files concurrenlty but when the mortgage is fixed you can make the new file your main file without having to be concerned about bringing all of your other accounts in it up to date. And in the meantime your main file will be kept current, too, so you have a safe and current fallback option. Perhaps this will help to make you feel less pressured to complete the review/corrections to your mortgage in one fell swoop...you can take as much time as you want/need.
Instead of pulling all of your statements together: Does your mortgage company allow you to download your all of your payment and balance history into Excel of PDF? That should make the comparison with what's shown in your mortgage register easier.

Chris Harris

@Boatnmaniac

Thanks for the really detailed assistance. I forgot that I could work off a backup file so I tried that. Using a backup, I tried entering six (6) of the missing twenty six (26) transactions and adjusted the opening balances accordingly (Principal & Escrow).

I was really surprised to find that it didn't change the payoff date. Nothing appeared to have changed.

To answer a few of your direct questions:

I am tracking Escrow (Insurance & Taxes).
Unfortunately, our mortgage company has changed five times over the years so I don't have the option to download an Excel from them. The most recent company's Excel only goes back to when they took over. Accordingly, I've recreated my own Excel and triple checked it. At this point, I'm comfortable knowing that all the transactions in Quicken and Excel match.

I'm at a loss...

Chris Harris

@Tom Young

Thanks for the instructions. I found this through the "Edit" button located under "Interest Rate".

Image: https://us.v-cdn.net/6031128/uploads/editor/vf/vwogvhcpoom3.png

This is the source of my odd entry (below), although I don't know how it got there and I can't find any way to delete or edit the highlighted line (above). Nothing occurs when you right click it.

Image: https://us.v-cdn.net/6031128/uploads/editor/fb/ungnbjdetw4f.png

To me, the way the Loan Interest Rate is set up, it looks like there was simply no interest rate associated with the account before 8/1/2019. So, I opened my testing data file and added this (below). It didn't have any apparent change; didn't adjust the errant payoff date.

Image: https://us.v-cdn.net/6031128/uploads/editor/dk/2ugci5uy4zmr.png

Since this IS a fixed loan, any idea how to make it NOT an Adjustable-rate Loan? How to delete the adjustable-rate entries above?

Tom Young

"Since this IS a fixed loan, any idea how to make it NOT an Adjustable-rate Loan? How to delete the adjustable-rate entries above?"

I don't think there's any need to delete those entries since that really didn't change the loan's interest rate. They stand out in the Payment Details list but shouldn't affect the amortization.