Mortgage Payment Schedule not properly calculating

  • 0
  • 1
  • Problem
  • Updated 2 months ago
  • In Progress
I am running:

Quicken Premier 2016 Release R 12 (25.1.12.2)
Windows 10 Version 1607 OS Build 14393.1198 64-Bit OS / 64-Bit Processor

I created a new mortgage loan and it is manual.

The term is for 15 years with an Open Date of 12/01/2015.  I would expect the maturity date to be 12/01/2030, but the Loan Schedule stops at 05/01/2027.  It only calculated a total of 137 payments vs. an expected total of 180 payments.

The future principal payment and future interest payment amounts are skewed as the 05/01/2027 maturity date illustrates a zero loan balance and the principal & interest amounts should be calculated with a 12/01/2030 maturity date.

The actual monthly payment amount is correctly reflected in both the Bill Pay section and correctly posts the actual amounts to the loan schedule.

I ran the File>>>File Options>>>Validate and Repair and the log file indicated that there was nothing wrong with the mortgage loan / account or respective transactions.

Why did Quicken only schedule 137 payments and what's the best manner to correct the data?
Photo of James Stewart

James Stewart

  • 166 Points 100 badge 2x thumb
  • Frustrated

Posted 2 months ago

  • 0
  • 1
Photo of NotACPA

NotACPA, SuperUser

  • 326,814 Points 100k badge 2x thumb
Can you take a snapshot of the Loan Details page (the top portion) and post it ... so that we can see how you set up the loan?

Because, the Loan Wizard has ALWAYS worked perfectly for me.
Photo of James Stewart

James Stewart

  • 166 Points 100 badge 2x thumb
See the attached Loan Details page.
Photo of Tom Young

Tom Young, SuperUser

  • 7,096 Points 5k badge 2x thumb
I just tried entering your loan information and everything check out except that I and got a different monthly payment amount ($2,517.69) and a full 180 months of payments.



Photo of James Stewart

James Stewart

  • 166 Points 100 badge 2x thumb
The delta ($759.39) is coming from extra amounts that are addressing escrow dollars for Taxes and Insurance.

I navigated to the mortgage loan, selected Payment Details>>>Edit Payment Details button.  The payment set-up has the $759.39 being applied as "Extra principal determines the total".  See the attachment.

If you look at the split detail in the Bill Pay transaction, the $759.39 is being automatically transferred to the escrow account.  See the attachment.

Should the $759.39 be listed / configured in the "Other" section of the Payment Details? 
(Edited)
Photo of NotACPA

NotACPA, SuperUser

  • 326,814 Points 100k badge 2x thumb
James, given the Loan Details that both of you have posted ... are you making extra principal payments?  OR, did you manually input the Monthly Payment amount?

Because I created an Amortization schedule, in Excel ... and the only way that I could get your "Current Balance" figure was by using Tom Young's payment amount.


Date                 Prin      Int          Balance
12/2/2015                                  $371,000.00
1/1/2016    $1,667.48  $850.21  $369,332.52
2/1/2016    $1,671.30  $846.39   $367,661.22
3/1/2016    $1,675.13  $842.56   $365,986.09
4/1/2016    $1,678.97  $838.72   $364,307.12
5/1/2016    $1,682.82  $834.87   $362,624.30
6/1/2016    $1,686.68  $831.01   $360,937.62
7/1/2016    $1,690.54  $827.15   $359,247.08
8/1/2016    $1,694.42  $823.27   $357,552.66
9/1/2016    $1,698.30  $819.39   $355,854.36
10/1/2016  $1,702.19  $815.50   $354,152.17
11/1/2016  $1,706.09  $811.60   $352,446.08
12/1/2016  $1,710.00  $807.69   $350,736.08
1/1/2017   $1,713.92   $803.77   $349,022.16
2/1/2017   $1,717.85   $799.84   $347,304.31
3/1/2017   $1,721.78   $795.91   $345,582.53
4/1/2017   $1,725.73   $791.96   $343,856.80
5/1/2017   $1,729.68   $788.01  $342,127.12
Photo of NotACPA

NotACPA, SuperUser

  • 326,814 Points 100k badge 2x thumb
The 3rd line of that payment split MUST be a transfer to the mortgage loan account ... even if that line is $0, it must be present.

Then, your escrow amount would be on the 4th line.

It's a quirk in Q.
Photo of smayer97

smayer97, SuperUser

  • 177,314 Points 100k badge 2x thumb
A quirk, meaning poor programming?
Photo of Tom Young

Tom Young, SuperUser

  • 7,096 Points 5k badge 2x thumb
I've always thought so when I first encountered this, probably back with the upgrade to Q2013.  Some artifact of "automatic" loans.
Photo of James Stewart

James Stewart

  • 166 Points 100 badge 2x thumb
I'm not sure that I am following....

The the third line of the split transaction is a transfer amount ($759.39) from my checking account to the [AFR - Escrow] Account.  The transfer amount is properly getting credited from my checking account and debiting the Escrow Account.

Are you saying that I need to set the dollar amount of line three to $0 and add a line (line 4) to post the [AFR - Escrow] amount?

With regards to the Current Balance figure, the balance figure is correct in my system because the split transaction is posting the correct monthly amortized amount.  Quicken is correctly calculating the amortization schedule for payment purposes.  For example, it is calculating based upon 180 periods.

The issue is with the future / remaining amortization amount.  It is only calculating based upon 137 periods. Payment Details>>>View Payment Schedule.  Notice the significant jump in the actual principal paid vs. the next future principal payment.  I believe the issue is with rendering the full payment schedule in the User Interface and not in the actual calculation.

See the attachment.

(Edited)
Photo of NotACPA

NotACPA, SuperUser

  • 326,714 Points 100k badge 2x thumb
Here's the REQUIRED order of items in your payment split.
  1. A Transfer to the loan account (to reduce principal)
  2. A payment to the Interest category
  3. Another Transfer to the Loan account (even if this line is for $0)
  4. A Transfer to any Escrow account
  5. Any other payments/transfers/etc.
You issue is ENTIRELY caused by not following this.
Photo of James Stewart

James Stewart

  • 166 Points 100 badge 2x thumb
I figured it out.

After creating the mortgage account via the wizard, I must have navigated to Bills>>>Manage Reminders and manually edited the split transaction window.  I manually added the third line, the [AFR - Escrow] Account, to the split transaction.

When I removed the line three and edited the payment details by adding the escrow transfer via the "Other" option.  The amortization properly displayed.  I guess the payment details reads the dollar amount(s) from the split transaction window and it was assuming any difference as an extra principal payment, which affected the future amortization schedule.

See the new maturity date of 12/2030.
(Edited)
Photo of NotACPA

NotACPA, SuperUser

  • 325,510 Points 100k badge 2x thumb
Well, CONGRATULATIONS!  You figured out what I told you 3 hours ago.