# Savings & Debt (Prototype Sheet) Debt Tracking Issues

First off, thank you, @randy for another fantastic sheet! This will be a great tool for at-a-glance savings and debt tracking.

I have noticed a couple of issues with the debt tracking portion of the sheet. I discovered that if you have 0% interest debt accounts, the `REQ'D BUDGET` formula in `Q6` does not properly calculate the monthly payment for those accounts. I was able to resolve this by adding a check for 0 in the Interest column `M7:M` inside the `ROUNDUP()` function and adjusting the formula for cases of `M7:M=0` to only divide the balance by the number of months remaining minus 1.

`={ "REQ'D BUDGET";arrayformula( if(isblank(L7:L)+ISBLANK(M7:M)+ISBLANK(N7:N)+(O7:O<=0), iferror(1/0), iferror( ROUNDUP( if(M7:M=0, O7:O/ (DATEDIF(\$AW\$2,N7:N,"M")-1), O7:O* (M7:M/12*(1+M7:M/12)^DATEDIF(\$AW\$2,N7:N,"M"))/ ((1+M7:M/12)^DATEDIF(\$AW\$2,N7:N,"M")-1) ) ) ,0 ) ) ) }`

After correcting this error, I noticed another error in the `TOTAL COST R6` formula. Unlike the `REQ'D BUDGET` calculation, `TOTAL COST` is using the number of months remaining rather than the number minus 1, which is adding an extra monthly payment to the cost. I was able to correct this by encapulating the `DATEDIFF()` function and subtracting 1 from the result.

`={"TOTAL COST";arrayformula(if(isblank(Q7:Q),iferror(1/0),Q7:Q*(DATEDIF(\$AW\$2,N7:N,"M")-1)))}`

Also regarding the `TOTAL COST` column, itâ€™s not really an error, but I think that it would be more appropriately titled `REMAINING COST`, as it seems this value will decrease with each successive period as the payoff date is reached. `TOTAL COST` isnâ€™t a very useful metric since this is driven by the current-period balance and has no concept of the original balance, nor is it useful with revolving debt accounts. I think itâ€™s still useful to have both `BALANCE` and `REMAINING COST`, as they should be close to the same value, within a deviation of one monthly payment, but will understandably not always be equal since the remaining balance may not necessitate a full final monthly payment.

Iâ€™d love it if this could handle mortgages properly, but that will require some extra effort in order to account for the PITI breakdown. Iâ€™m working on a helper sheet that may be able to be used to feed the extra logic required. Stay tuned!

Thanks for your detailed debug and solution as always, @cculber2.

I remembered that there is a Google Sheets function `PMT()` that can help in this situation. I believe this formula in `Q6` simplifies the calculations and removes the need for the zero-interest special case:

`={"REQ'D BUDGET";arrayformula(if(isblank(L7:L)+ISBLANK(M7:M)+ISBLANK(N7:N)+(O7:O<=0),iferror(1/0),iferror(-PMT(M7:M/12,DATEDIF(\$AW\$2,N7:N,"M"),O7:O))))}`

What Iâ€™m still wrestling with is the logic around the number of periodsâ€¦

• If the current period is `9/1` and the user sets the â€śdeadlineâ€ť to `10/1` is that two periods or one? (`DATEDIF()` measures it as one.)
• If the current period is `9/1` and the user sets the â€śdeadlineâ€ť to `10/31`â€¦?
• What if the current period is `9/1` and the user sets the payoff date to `9/30`? (`DATEDIF()` measures this as zero period which blanks out the results formulas.)

I guess Iâ€™m inclined to defer to whatever DATEDIF() reports with the exception that if the balance is non-zero, there should be a minimum of one period used in the calculation.

Does this conflict with your `DATEDIF()-1` logic, @cculber2?

Nice, @randy, that `PMT()` function certainly simplifies things.

There is definitely some weirdness around the number of periods. If `BALANCE` is prior to a payment being made in the current period, the formula you posted with `DATEDIF()` is correct. If `BALANCE` includes the current periodâ€™s payment, `REQ'D BUDGET` will be off by one monthâ€™s payment, which is corrected by using `DATEDIF()-1`. `PMT()` has no idea if weâ€™ve made a payment in the current period, so I feel like we need to include an `IF()` statement to check `Transactions` for a payment in the current period and adjusting `DATEDIF()` accordingly. Or am I overcomplicating things?

Something else I think that needs to be considered for debt tracking is the minimum required payment. I know this is included on the `Debt Progress` sheet template and it should probably be included here. `REQ'D BUDGET` should be the the greater of the minimum payment or `PMT()`, unless the remaining number of payment periods is 1 in which case `PMT()` should be used. This would still need to account for whether or not a payment has been made in the current period.

Related to above, I also had a thought regarding mortgage debt tracking. The minimum payment column could also be considered as the principle and interest payment, and another column could be added for escrow payments (taxes and insurance). The escrow term would zero out for non-mortgage debt, so it shoulnâ€™t break other tracking. At this point weâ€™re moving beyond a simple debt tracker and more so recreating the `Debt Payoff` sheet template, but I think itâ€™s a good move.

A post was split to a new topic: Savings Budget Shows as â€śIncompatibleâ€ť

Thanks for the feedback, @cculber2.
Iâ€™m working on a new build.

Iâ€™ve added a column `AK` that sums receipts in `Transactions` within the current period `AX2` that a) match the debt/account category name and b) match the transaction `Account` to the debt account. For now, we will use this column simply as a boolean: if it is greater than zero, there has been a payment in the current period.

From here, we need to determine the logic for how many periods to use in our calculationâ€¦ I think there are a few inputs to this logic:

• `DATEDIF(\$AX\$2,N7:N,"M")`: the number of months/periods between the current period and the deadline
• `IF(VLOOKUP(L7,{\$AI\$7:\$AI,\$AK\$7:\$AK},2,false)>0,1,0)`: if the user has made a payment in the current period

I think the logic should be:
`=MAX(1,DATEDIF(\$AX\$2,N7,"M")+1-IFERROR(IF(VLOOKUP(L7,{\$AI\$7:\$AI,\$AK\$7:\$AK},2,false)>0,1,0),0))`

The `MAX(1,...)` is intended to ensure there is at least one period since the balance is non-zero. For example, if your current month is `9/1` and your payoff date is `9/20`, `DATEDIF()` will return zero but there must be one period at least to pay the balance.

I think this new formula is closer, but Iâ€™m still pretty confused on desired behavior around the deadline. If someone sets a deadline of `10/1`, do you think their intent is to payoff the debt in October, if September is the current month, they have two periods? Or do you think their intent is to pay it off by October, so there would only be one period?

To make debug a little easier, I created a standalone `Period` column. Once weâ€™re confident the logic makes sense, we can roll this into the formulas and hide it.

1 Like

Are you thinking of `Min Payment` as a calculation to cover the interest or as defined by the lender on the monthly statement?

I hadnâ€™t considered working mortgage tooling into this sheet. I think that is an interesting concept but Iâ€™d like to sit on it until the base functionality is a little more robust. Further, Iâ€™ve got a refresh in the works on the Debt Progress template. Right now, my idea is that the Savings & Debt template is for really basic debt visualization/dashboarding and the Debt Progress update will be for creating and adhering to a comprehensive paydown plan.

1 Like

Just published an update. I think it is closer. Not sure it is 100% there yet.

Thanks, @randy. Things are definitely moving in the right direction and I agree itâ€™s not 100% yet. Hereâ€™s my latest pass based on your most recent update.

`AL7:AL Period Actual`
There was a missing `\$` in `\$AY2` cell references. It should be `\$AY\$2`. This was causing cascading errors in the 2nd and further `AJ7:AJ` debt accounts.

Fillable formula:

`=if(ISBLANK(\$AJ7),iferror(1/0),SUMIFS(INDIRECT(\$AX\$20&"!"&\$AY\$23&"2:"&\$AY\$23),INDIRECT(\$AX\$20&"!"&\$AY\$22&"2:"&\$AY\$22),\$AJ7,INDIRECT(\$AX\$20&"!"&\$AY\$24&"2:"&\$AY\$24),\$AJ7,INDIRECT(\$AX\$20&"!"&\$AY\$21&"2:"&\$AY\$21),">="&\$AY\$2,INDIRECT(\$AX\$20&"!"&\$AY\$21&"2:"&\$AY\$21),"<"&(EOMONTH(\$AY\$2,0)+1)))`

`P6 Periods`
I like the addition of a `Periods` column to simplify other formulas. `DATEDIF()` is still consistently off by one period whether or not the current period has a payment. Removing the `+1` term from the `DATEDIF()` result corrects the error. After making the change, I verified that the number of periods remaining changes depending on whether or not the current period has a payment recorded.

Formula:

`={"PERIODS";arrayformula(if(ISBLANK(N7:N)+(O7:O<=0),iferror(1/0),iferror(IF(DATEDIF(\$AY\$2,N7:N,"M")-IFERROR(IF(VLOOKUP(L7:L,{\$AJ\$7:\$AJ,\$AL\$7:\$AL},2,false)>0,1,0),0)<1,1,DATEDIF(\$AY\$2,N7:N,"M")-IFERROR(IF(VLOOKUP(L7:L,{\$AJ\$7:\$AJ,\$AL\$7:\$AL},2,false)>0,1,0),0)))))}`

Add new column `G Periods` for Savings
I added this in support of properly calculating `REQ'D BUDGET` similar to debt changes. We need to take into account whether or not there are any savings already budgeted for this period. This column is formatted as a 0-decimal precision number. Unlike `P6 Periods`, this `DATEDIF()` calculation does require the `+1` term to calculate correctly. I canâ€™t quite explain why they behave differently, but that is how it is working out.
All above column references beyond G shift by one. All below column references account for the shift.

Formula:

`={"PERIODS";arrayformula(if(ISBLANK(B7:B)+(F7:F<=0),iferror(1/0),iferror(IF(DATEDIF(\$AZ\$2,E7:E,"M")+1-IFERROR(IF(VLOOKUP(B7:B,{\$AB\$7:\$AB,\$AD\$7:\$AD},2,false)>0,1,0),0)<1,1,DATEDIF(\$AZ\$2,E7:E,"M")+1-IFERROR(IF(VLOOKUP(B7:B,{\$AB\$7:\$AB,\$AD\$7:\$AD},2,false)>0,1,0),0)))))}`

Extended fillable formulas in columns `AE7:AE Total Budget`, `AM7:AM Period Actual`, `AT7:AT Period Balance` to account for a large number of categories.
I always seem to forget to do this and see some really weird behavior until I make the change.

Increase display decimals for `D7:D SAVINGS GOAL`, `F7:F AVAILABLE`, `H7:H REMAINING \$`, `J7:J BUDGETED`, and `K7:K REQ'D BUDGET`.

`K6 REQ'D BUDGET`
I am now conditionally adjusting the number of periods used in the calculation based on whether or not money has already been budgeted toward the savings goal this period. Again, we need to take into account whether or not there are any savings already budgeted for this period. Prior to this change the `REQ'D BUDGET` would continuously flip values depending on the current periodâ€™s budget, making it impossible for them to ever be equal.

Formula:

`={"REQ'D BUDGET";arrayformula(if(isblank(B7:B)+ISBLANK(E7:E)+(F7:F>=D7:D),iferror(1/0),Iferror(ROUNDUP(H7:H/G7:G,2))))}`

`MIN PAYMENT`
I was looking at this is as defined by the lender on the monthly statement. Naturally, if there is a minimum payment specified by the lender, `REQ'D BUDGET` should never be less than the `MIN PAYMENT` unless the `BALANCE` is less than the `MIN PAYMENT`.

Period Logic Considerations
My thought is that the solution is a little simpler than stated. I think that the number of periods should reflect the number of remaining payment periods available (or the number of remaining unbudgeted savings periods available) between the current period and the deadline, accounting for whether or not payment has been made (or savings has been budgeted) for the current period, e.x. I have X more opportunities to make a payment or set aside savings. When I set a deadline, I set it as stated on the lender agreement (or the target date of a savings goal). I agree that `MAX(1,â€¦)` is a good addition to ensure calculations are still made if the current period is the last required. I am making all of these comments and calculations based on a savings goal and a debt payoff with known remaining periods and amounts, and engineering accordingly.

• I bumped the sheet up to 100 rows of content and filled down all non `ARRAYFORMULA()` formulas
• I added a savings period column
• Implemented all of your proposed formula changes
• I republished the solution (should be live very shortly)

Regarding the `MIN PAYMENT`, I agree with the `REQ'D BUDGET` needing to be higher, but we donâ€™t have a field to enter the `MIN PAYMENT` and, at this time, Iâ€™m hesitant to add more special-purpose columns.

Really appreciate the detailed debug & solutions, @cculber2, as always.
Randy

P.S. Iâ€™m hoping this resolves the data-accuracy issue you were having, @aronos.

1 Like

Thanks, @randy. The updates are working almost perfectly, except for a mistake I made in the `Savings Periods` formula in `H6`. It should be checking `SAVINGS GOAL` in `C7:C` for a value, not `AVAILABLE` in `E7:E`. Not very helpful if youâ€™re trying to start a new savings goal with \$0 in budgeted savings. Mea culpa!

The formula should start like so:

`={"PERIODS";arrayformula(if(ISBLANK(A7:A)+(C7:C<=0)`

Good catch. Thatâ€™s fixed now in v64.

1 Like

I used to think I was pretty good with spreadsheets until being a part of this forum.

I just loaded the latest and all of the issues I had look resolved. Really liking this sheet! It is filling a hole in my workflow I created by deciding another sheet I was keeping up was too much effort.

1 Like

You and me both! I still donâ€™t have a solid understanding of many of the specific spreadsheet functions if I were to develop something on my own, but I have a strong background in programming and can pick up what someone else has built (thanks, @randy!) and make modifications in that context. Iâ€™m really enjoyed digging into the hidden logic and learning how to do all of this manipulation.

1 Like

You are learning very fast, @cculber2. Your fixes and debug are excellent.

I think the one thing that makes debugging our spreadsheets much harder than it should be are the `INDIRECT()` range references which we use to find and link user data wherever a user places columns within the Tiller Money core sheets. The `INDIRECT()` formula is powerful, but it makes formulas longer and the data references obtuse.

1 Like

You mean if they insert a new column into Transactions for example or rearrange the columns there, the `INDIRECT` keeps the formula from breaking because we can still find the appropriate column referenced? Just curious in case some less than expert comes across this and is curious

P.S. thanks @cculber2 and @aronos for all the testing and bug finds here. Hopefully that will make our demo this afternoon more reliable and hope to see you on there if you can make it at 1PM weâ€™ll give you a shout out

Thanks, @heather! I have a work meeting at that time, but Iâ€™ll be internally cheering you guys on.

Youâ€™ve got it right. Not only does it help prevent users from breaking formulas when they add new columns, but it also prevents solution templates that add new columns from breaking those same formulas.

1 Like

We want to make sure everyone feels comfortable making their spreadsheets & templates their own. Because users can add columns, drag columns, delete columns, etc, Tiller Money templates need to start by finding the data locations in each userâ€™s core sheets (e.g. `Transactions`, `Categories`, `Balance History`, etc) and then referencing the data where it is.

Most of our spreadsheets have a â€śhidden areaâ€ť over to the right (of the visible area) where we perform lookups and identify key data columns in other core sheets.

This is what it looks like in the new Savings Budget:

From there, we often use the `INDIRECT()` formula which allows us to create cell references by appending text (including the column reference letters that we found ).

These references look like this:
`INDIRECT("'"&\$BB\$27&"'!\$"&\$BC\$31&"\$2:\$"&\$BC\$31)`
â€¦ where `\$BB\$27` is a sheet name (e.g. â€śTransactionsâ€ť) and `\$BC\$31` is a column letter (e.g. `A`).

Hope this helps.

Since yesterday, my Savings & Debt prototype sheet has been showing â€ś1 account is underfunded for your savings goalsâ€ť even though the only two accounts being tracked there both show as fully allocated (balance and allocated equivalent, unallocated 0).

1 Like

I had this happen today. I traced it to a liability account with a negative balance, a credit card that has a net credit.

1 Like

It would be nice if there was an easy way to select next monthâ€™s budget, once it exists. I had to muck with the formula in AX2 to get the required budget numbers when setting up my November budget. Itâ€™s workable for some but not user friendly.