Remaining Interest #REF Error

Hello,
I’m trying to create a debt progress google sheet for my student loans. I’ve input all of the information, but the Remaining Interest column has an #REF! error and I cannot for the life of me figure out how to fix it. I believe this error is rendering the entire sheet useless - it says I can pay off my student loan debt this month (I definitely can’t). This is the formula in the cell =TILLER_DEBTSNOWBALL($B$8:$G,$D$4,U1,false)

I also can’t change the Start Month. It’s stuck at Jan. 2019. I’d really like to look ahead once student loan payments are required again in January 2021.

Please help!
Megan

:wave:, @mlbenson10 !

Are you You might try a newer version of the debt planning tool, the Debt Planner that we recently released.

Thanks Heather. This appears to be working (yay!) but the pay off date for some of the loans are in the past. For example, one loan says the pay off date is April 1984, which is clearly wrong. Any insight?

Thank you again!

@randy any ideas on why she might be getting a pay off date that’s in the past?

@mlbenson10, I’m assuming you have the interest rate, and minimum monthly payment entered for the loans and that you’re seeing a starting balances that is equal to or higher than your current balance? Do you mind sharing a screenshot of the dashboard? Feel free to mask any sensitive info in the screenshot.

I don’t have it all filled out, but I’d think for the lines I do that it’d be a date in the future.
Also, some of the Est. Total Interest is negative. Maybe this has something to do with it?

Playing around with the other payoff methods still gives me dates in the past as well.

The monthly interest for the 4th loan also seems ridiculously high. I didn’t input this number…

I had the same issue with negative interest and past paid off month.
For me, the issue was that I only had one loan in my budget. The rest was set to “Debt Payoff.” Once I created a budget for every other debt, the numbers went back to normal.
It appears that you either can have nothing budgeted or all budgeted, so while it’s optional, you do need either 0 or 100% and nothing in between.

I also had to change the total recommended payment formula to just sum the remaining payments, otherwise it was giving a wrong number.

Thanks for flagging this issue and sharing a screenshot, @mlbenson10.

I see three issues with the screenshot you have shared:

  1. I can see that the formulas are generating negative payoff dates when the provided budget isn’t sufficient to pay down the loans. I can see in the screenshot that a) your Total Dec Budget isn’t populating (since you probably haven’t budgeted for these loans in your Categories sheet)… You can get away with not creating loan budgets in the Categories sheet if you provide sufficient Additional $ to cover minimum payments and interest. In your screenshot, these two fields sum to Total $ and with no payment amount; this means your loans will keep growing.
  2. I recommend inputing Min Monthly Payments for all loans. If this field is not populated, the loans will grow. In the Avalanche and Snowball models, the intent is to pay off just enough to maintain them until they receive the focus of your payments.
  3. The interest rate for the fourth loan looks like it has been changed from a percentage to 6.4 (which would be 6400%— hopefully you don’t have such a loan :wink:).

Let me know if these adjustments fix the issues you’re having.
(I will think on a fix to the UX displaying past dates when the fields aren’t all populated… :thinking:)

Randy

This isn’t the intended behavior, @yossiea. Our intent was to leverage budgets in the Categories sheet and to allow you to supplement or run scenarios suing the Additional $ field (e.g. what if I paid more or less?).

Wherever the budget comes from— Categories sheet budgets or Additional $— it is important that Total $ is sufficient to cover the min monthly payment total.

I just published an update (Debt Planner version 1.01) that does a better job of displaying data when the payoff plan is underfunded.

Thank you! These seemed to work!

1 Like

Thanks, the new version looks good.
If I may make a suggestion for a future release: It would be nice to see an amortization schedule and then I’d have the opportunity to select additional payments for specific months, rather than additional payments for every month. I’ve used something similar in Google Sheets and was very useful but also psychologically appealing to help reduce debt as you saw your debt dwindle and suddenly the next month saw the next debt increase in payments as you now have more money to spread around.

This is a very cool idea, @yossiea. Unfortunately, the paydown-modeling formulas are not sophisticated enough at this point to account for month-to-month payment changes… but, if someone built a debt payoff solution that could pull this off, it would be a great addition to our Show & Tell category.

I was using this Google Sheets (template here: https://docs.google.com/spreadsheets/d/1iAfHnn2Ok-Nb9wV46SfaPDGNbTKfbZDibWDq-cmqWK4/template/preview) and I tried importing the payment schedule into the Tiller Template which I was able to do, but the snowfall/avalanche wasn’t working because I didn’t know how to do the formulas. But I was able to see at least a sort of accurate view of the monthly breakdown.
Hopefully this should give someone with better skills an idea on how to implement.

I have seen that Vertex sheet, @yossiea. The payment schedule is a nice feature. We built ours to handle more loans without creating a row for each month, an approach doesn’t play as well with a payoff schedule.

The Vertex approach is a good one as well and more amenable to changing payments by month.