Search code examples
functiongoogle-sheetscalculation

I need something similar to a recursive function to dynamically update "Amount Due" fields for a debt payment plan in Google Sheets

Edit 1: Note, the "Payment Received" check boxes are irrelevant. They are just for the accountant to check upon confirming receipt of the payment. That's why I didn't mention that column.

I'm sorry if this is as clear as mud. I am having trouble understanding how to explain my issue clearly, which is part of the reason I am having trouble figuring it out. If I was using JavaScript I would just do this with a recursive function. But I don't think Google Sheets has that capability.

Here is a link to an example of the spreadsheet Debt Payment Plan

I am creating a debt payment plan for my church group. I've got a columns for "Due Date" (A2:A13), "Amount Due" (B2:B13), and "Amount Paid" (C2:C13).

I also have "Total Debt" (G1), "Months to pay debt" (G2), "First Due Date" (G3), "Total Amount Paid" (G5), and "Remaining Balance" (G6).

The goal is for the amount due each month to be the total debt divided by the months to pay debt. That part is easy. What I am having trouble figuring out is how to make sure that the final amount due takes overpayments into account.

For example. Say I owe $600 and want to pay it off in 12 months. I will have 12 rows, 1 for each due date. The monthly payment will be $50. Now, let's say I am able to pay $60 one month. I don't want every monthly payment to go down because of this, but I need some kind of function or calculation that will make sure the 12th row reflects the difference. Since I have paid $10 extra, the last payment should be $10 less.

I could probably figure that out if I only needed the last "Amount Due" to work this way, but I need them all to work this way. Let's say I am able to pay $125 extra one month. Rows 11 AND 12 would need to show $0, but row 10 would need to show $25.

Finally, I don't want the "Amount Due" to show negative numbers. So, if the monthly payment has been made, whether it was $50 or more, I want the "Amount Due" for that row to show $0. If less than $50 was paid, I want it to show the difference of "amount due" minus "amount paid".

I have tried putting this in row 12 (=IF($G$1-$G$5>=($G$1/$G$2), $G$1/$G$2, $G$1-$G$5)). It is displaying the standard monthly payment if ("total debt" - "total amount paid") is >= the standard monthly payment, and it displays "remaining balance" otherwise. It does not display $0 if an over payment was more than the standard monthly payment.

My first row seems to be functioning how I want it to. It shows the standard monthly payment until a number is put into "Amount Paid", and then it reflects the difference down to $0.

Solution

  • Here's one approach; do test out with varied scenarios to make sure its givin' out the intended output...

    UPDATED FORMULA:

    =choosecols(ifna(hstack(let(Ξ,G2, Δ,tocol(,1), Λ,iferror(chooserows(C2:C,sequence(index(match(,0/(C2:C<>0)))))),if(sum(Λ)>G1,Δ,vstack(
  if(Λ="",Δ,map(Λ,lambda(Σ,max(0,Ξ-Σ)))),iferror(sequence(quotient(G1-max(sum(Λ),if(Λ="",,rows(Λ))*Ξ),Ξ),1,Ξ,0),Δ),let(x,mod(G1-max(sum(Λ),if(Λ="",,rows(Λ))*Ξ),Ξ),if(x<0.1,Δ,x))))),wrapcols(,G1/G2,)),0),1)

    enter image description here