Formula for ceiling and utilizing free cash money

[Amit Punia]

Need a formula which only take that much amount from free cash which is remaining for loan. So suppose I took a loan of 5636(Including interest) and I'm paying that amount based on the money I've on monthly basis. So in 1st month I'm having free cash of 471 then I paid that amount and remaining loan amount is 5165(5636-471). Now next month I'm having 869 free cash. I will pay 869 out of 5165. The problem is the output amount should not be more than the free cash and once the loan amount is fully paid, it should utilise free cash. Amounts are hard coded in range C3:C10. Need a formula in that range which fulfills the condition.

File is attached.

Thanks in advance

 

[p45cal]

Does the attached do it for you?

 

[Amit Punia]

What if E column is not there.

 

[NARAYANK991]

Hi ,

Try this in D3 and copy down :

=MIN($H$4 - SUM(D$2:D2), C3)

Narayan

 

[Amit Punia]

Thanks @NARAYANK991 , The formula is working absolutely fine.