Savings & Debt (Prototype Sheet) Debt Tracking Issues

Spreadsheets Google Sheets
,
1

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!

2

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?

3

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.

4

A post was split to a new topic: Savings Budget Shows as “Incompatible”

5

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
6

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
7

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

8

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. :sweat_smile:

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.

9

I made the following changes:

  • 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
10

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)

11

Good catch. That’s fixed now in v64.

1 Like
12

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

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
13

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
14

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
15

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 :wink:

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 :wink:

16

Thanks, @heather! I have a work meeting at that time, but I’ll be internally cheering you guys on. :slight_smile:

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
17

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:

image
image832Ă—812 46.6 KB

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 :point_up:).

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.

18

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
20

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

2 Likes
21

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.