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!