Debt Planner Not Pulling Budget for Current Month

Google Sheets
1

I’m having an issue with the Debt Planner template from Tiller Labs.

In the hidden columns, I noticed there’s an error - Scenario in function NPER is not possible. While I’m reasonably savvy with spreadsheets, I’m not able to track down the issue. Formula in L5 has error (Input must be greater than or equal to =$C3).

I’ve attempted Restore twice but the same issue(s) occur.

Appreciate any insight.

2

@randy Any ideas on what might be the issue here.

3

I can attempt another restore, but I’m not convinced that will work. No other responses on this question.

4

The restore is always a good first step, but it sounds like this more likely has to do with your debt parameters.

You are probably underfunding the debt pay down in the model so the interest runs away and the loans get bigger rather than smaller. Does the monthly payment amount in L5 look sufficient? What happens if you increase the amount by entering a large value in L4?

Randy

5

@randy Hi A couple others of us have posted issues in the Debt Payoff column. I did try restoring but that didn’t help.

6

Randy - thank you for the comments and suggestions.

Adding a large value in L4 does remove the error I stated above. I actually had not added anything into this cell yet. In doing so, it lists a ‘Projected Debt Freedom Date’ which had previously been blank. What I’m noticing is there is not value pulling into L3 (Tot Feb Budget) and my Feb Budget values (column/cell K8, K9) remain as a filled RED cell. I am using the ‘Debt Payoff’ and ‘Debt Payoff Received’ categories necessary for the Debt Progress worksheet and it seems to be working without issue. I only state that because the values seem to be pulling in from associated connected sheets.

I also noticed that selecting the formula in K8, there are no values in S8,etc. for Budget, Current. I don’t specify individually the account itself in my Budget. Meaning, I don’t have a line item category for ‘Regions Bank Loan’. I’m using ‘Debt Payoff’ in the Budget to aggregate the payments made to the debt.

I’m throwing a lot at you - so apologies upfront if at all confusing in the way I’m describing what is occurring.

Jason

7

Following the formulas, you can see that K7 is the sum of K8:K. Further, you can see that K8:K pull from a lookup based on the account name mapping to current-month budget values in S8:S in the hidden part of the template.

If you look in S7, you can see the ARRAYFORMULA() that pulls in the monthly budget values for each account. It looks like this:

={"Budget, Current Period";arrayformula(if(isblank(O8:O),iferror(1/0),iferror(vlookup(O8:O,{INDIRECT("'Categories'!$"&U3&"$2:$"&U3),INDIRECT("'Categories'!$"&U4&"$2:$"&U4)},2,false))))}

Essentially, it is doing a look up based on account name O8:O in the Debt Planner sheet to a matching account name in the Categories sheet, then mapping from the Category-name match ('Categories'!$"&U3&"$2:$"&U3) to the Categories-sheet budget for the current month 'Categories'!$"&U4&"$2:$"&U4.

You can see that to create the monthly budgets for the Debt Planner, you need to create new categories for each account with a category name that exactly matches the (debt) account name. You’ll then want to create budgets for these new categories. If you take these steps, the budget values should populate into K8:K and also into L3.

These steps are documented here.

Let me know if this helps.
Randy

1 Like