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)
```

- Nested functions are not allowed but why nested function prototypes are allowed? [C++]
- How to define enum values that are functions?
- Blackjack Capstone Project from 100 Days of Code with Dr. Angela Yu
- JavaScript generating always the same numbers on the second for loop
- Javascript method to ensure that a date is valid
- How can I invoke a scriptblock in the caller's context?
- Define metadescription
- Is it possible to make R do this complex sum function involving a parent-child hierarchy?
- How can I execute PHP code from the command line?
- Why there is a significant performance difference when using function in WHERE clause
- I don't understand this javascript function call and where it would be used
- Returning a Dictionary from a Bash Function
- In what circumstances is it allowed to have a function body be an expression NOT surrounded by curly brackets?
- "Adding a value to a 'datetime' column caused an overflow."
- Changing options() in a function environment without changing options() in global environment in R?
- Confused about javascript function expression
- Forward function declarations in a Bash or a Shell script?
- Understanding functions in javascript
- Passing a variadic function to a variadic function via a pointer
- Beginner Python script for calculating GC content in DNA sequence
- Type of Function in C or C++
- How to process tabular data from Oracle PL/SQL function in JAVA?
- PHP append one array to another (not array_push or +)
- SQL string throws "External Name not Defined" error when running in VBA module - Name is defined
- run javascript function on griview pageindexchanging event
- Identify the list that is the odd one out, and find its index from given lists
- Different results for function() and fn(). PHP
- Functional programming with dplyr: column_to_rownames() inside function
- Difference on passing a free function as template argument by value or by reference
- How can I filter and count objects in DJANGO template?