Update Annual payment for each month base on conditions

[Bharathy]

I have Budget table which has cost field, variable cost, and month field (Jan to Dec). Cost field has some default amount. Variable cost field is editable which user should give some value(amount).
Case 1:

Cost | Variable | Jan | Feb | Mar | Apr | ......... | Dec | 
$60                     $5     $5    $5     $5               $5     

Case 2:

Cost | Variable | Jan | Feb | Mar | Apr | ......... | Dec | 
$60      $120      $10    $10  $10   $10             $10     


if the client's budget amount is more than the standard cost. in that case, the user will provide the original amount in the Variable field to split the amount into 12 months. but if the client wants to close the monthly payment early by paying more each month. 
For example:
variable cost is $600. Each month need to pay $50. If the client pays $100 in January, then the rest of the month is due at $45. If the client pays $200 in Feb month, the rest of the month owing will be $25... so.. on

How to achieve this in an Airtable base?

[TheTimeSavingCo]

Hmm what if we used a formula field for that?

(Total - Paid)/12

[Bharathy]

Yes, You are right. But the Formula field is non-editable, right? If the client paid some amount in January, I need to enter the amount manually in the January field and the rest of the Columns will be (Feb - Dec) split up of the balance amount. And if the Client paid some amount in the February field the rest of the Columns will be (Mar - Dec) split up of the balance amount. The client paid some amount in the March field which closes the due amount so no need to pay for the rest of the month. (Apr-Dec) due is $0.

Hope you got my requirement. 

[TheTimeSavingCo]

Ah, I see.  Hmm, you're going to need to have two fields for each month then I think, a currency field to indicate how much they paid, and a formula field to indicate how much they should be paying

The formula fields would then deduct the amounts that were already paid and also figure out how many months were remaining

[Bharathy · Accepted solution]

I'm trying to achieve this using automation. Could you please answer to this post if you have any idea?
https://community.airtable.com/t5/automations/find-a-field-recently-updated-in-automation/m-p/186579...