Debt Planner: Payoff Month Not Calculating Correctly

My payoff months arent calculating correctly:

If I have a loan for $2681 with 0% interest and I pay $150 min payment, I will finish paying in roughly 18 months. Aug 2022.

The paid-off month in the sheet is Aug 2026? I can’t see how the sheet is calculating the payoff month?

Thank you.
Megan

Echoing this. I have a credit card that has $198 left, I pay $82/mo.
Avalance: It thinks it’ll be paid off in June 2023.
Snowball: It thinks it’ll be paid off by September 2021.
Ranked (when set as #1): It thinks it’ll be paid off by June 2022.

I’m not able to reproduce this issue, @mmgalliv. I loaded a fresh copy of the Debt Planner template and entered the parameters you gave. You can see that results below yield August 2022 as expected (regardless of the payoff method).

The things that could complicate this are:

  1. A damaged version of the Debt Planner template - have you tried restoring it?
  2. Additional loans that are prioritized ahead of paying down the $2,681 loan.
  3. A loan payment total in cell L5 that is less than your intended $150/month payment.
  4. A loan balance pulled from Balances History that is different from the expected $2,681 value.

Can you check the above?
Randy

As with my note to @mmgalliv, I tried the scenarios you listed and I’m not able to reproduce the issue.
Can you review the bullets I shared with @mmgalliv :point_up:?

Snowball

Avalanche

Ranked

I’ll give them a shot, thanks!

Tried the restore, checked the columns, etc, still having issues.

I can cause it to “break” pretty easy. See steps below:

If I just add one loan, with one balance, and one minimum payment, it works great.

Loan 1: 0% interest, min payment $82, balance of $198, succesfully shows pay off date for May 2021.

Breaks upon adding a second debt:

Loan 2: 0% interest, min payment $116, balance of $694, still shows May 2021 (this is wrong).

Loan 3: 0% interest, min payment of $185, balance of $4898, still shows May 2021 (impossible)

Question:

  1. Additional loans that are prioritized ahead of paying down the $2,681 loan.
    A min monthly payment should have the same due date. Even if you are prioritizing paying more on some you still have to pay the min monthly on the card so it shouldn’t be affected by the other accounts?

The account by itself has the right date but when I add in the other accounts the date changes, Which doesn’t make sense to me at no point am I not going to pay the min. monthly payment.

Also, now the other account cannot be found?

Megan

I think the problem, @branadonshutter, is that you are using the Avalanche method which prioritizes loans based on (highest) Interest Rate, but you have not entered any interest rates into B8:B.

Can you try entering the number zero for the interest rate for each loan and see if the behavior is as expected?

It’s really hard for me to imagine how the payment model is running in your sheet, @mmgalliv, without seeing the inputs. Can you share a screenshot of your sheet? Feel free to simplify the example (with fewer accounts that still result in the failed behavior) or to redact account descriptions like @branadonshutter.

Also, I don’t know what you mean by “now the other account cannot be found”. The accounts in the dropdowns in column A populate from your Balance History sheet where the Class is marked as Liabilitythis requirement is documented here. (You can override the Class using the Accounts sheet.) If you aren’t seeing an account in the dropdown then:

  • It doesn’t exist in Balance History.
  • It is not properly setup with an Account Id in Balance History.
  • It is not marked as a Liability in Balance History.

Thanks,
Randy

If you search for “Ethan Allen Platinum” in your Balance History sheet, is the class set to Liability?

Can you tell me more about the example in “As for the sheet:”? What is the problem I should be seeing? There is a typo in the message (“payoff should email the same”) that is making your message hard to understand.

To be honest, @branadonshutter, there are some aspects of your loan portfolio here that are at odds with the way that the Debt Planner model was built.

The model was built assuming that minimum payments alone would hold a balance stable but would not pay off a loan completely. This allows the model to be built with an expectation that only one loan at a time— the prioritized loan receiving the lump payment— would be paid off.

Your portfolio has so many 0%-interest loans and some with relatively high minimum payments. This means that long before they are prioritized, some of these 0%-interest loans could be fully paid off. I did not anticipate this when I built the formulas.

I don’t know for certain, but my guess is that this issue is resulting in some of the calculation problems you are seeing. You could try to rework the hidden formulas to make the model perform better in this scenario (if you do, please share your changes in this thread!). Alternatively, as a workaround, you may be able to set your Min Monthly Payment to $0 for the 0%-interest loans.

Best,
Randy

I’ll take a looksie. Excel is definetely not my forte. I’m glad that the stuff I was seeing wasn’t me going crazy (plenty of that in my day to day).

I use another tool to track all of this for me (Debt Reduction Calculator | Debt Snowball Calculator), and it works well. I was just hoping to keep everything in Tiller.

I am both still lost and crazy :slight_smile:

Sorry do you see in the drop down of accounts listed Ethan Allen is not one of them but it use to work before I restore and it is marked as a liability and in the balance history.

For the payoff:

If I use avalanche method or snowball it should n’t effect my pay off date if I don’t add any additional money to that acccount.

My monthly payment will still be 150 a month with zero interest and will always end on aug. 2022 unless I decide to add more money to that account.

However, when we add accounts to the spreadsheet it changes the payoff dates without even adding any extra money to the accounts? Does that make sense.

I think we addressed the Ethan Allen account issue with the drop down in a DM thread, @mmgalliv. :crossed_fingers:

As for the payoff issue, unfortunately, I think the answer is the same as my response to @branadonshutter above. Essentially, the Debt Planner doesn’t currently handle well 0%-interest loans where large minimum payments cause them to be paid off before they are prioritized. Your sheet has a few loans like this.

The easiest workaround is to set your Min Monthly Payment to $0 for the 0%-interest loans. (I realize this may make budgeting challenging if you have an agreed-to payment plan for some loans.) If you do this, the template should perform predictably.

Randy

1 Like

Thank you so much! Repairing the balance history workeded and I will try the 0 min payment.

Than you again for everything!

Well that’s great news. I’m happy to hear that.

Sorry about the limitations in the template with the 0%-interest loans. It’s probably not a super complicated change but it’s a fussy set of calculations and I won’t have time to dig into that anytime soon.

Would be awesome if someone in the community had a fix or improvement to the formulas. I’d be happy to update the template master.

I will try to play around with, but I’m not that great. Also, all of the 0% interests accounts have to be change to 0 min for it to work.

Will adding columns between F & G mess up the formulas?