Function to balance CC credit utilization

Question

I have $x$ to pay $(^p)$ on multiple cards. Each card has a different credit limit $(^{cl})$ and a different balance $(^b)$. So the credit utilization $(^{cu})$ for each card is different.

I have created a spreadsheet that I can manually manipulate the ^p breakdown to balance the $^{cu}$.

It looks like this: image of spreadsheet

So how could i write a single formula to put in H3 & H4 to balance out I3 & I4?

Thank you.

Answer 1

I realized the answer. Maybe it can be improved, because the percentages vary slightly.

for H3: =round((C3)-((sum($C$3:$C$4)-$H$1)/sum($D$3:$D$4))*D3,2)

I took the sum of the balances, subtracted the amount available to pay, then divided the sum of the credit limits; this gives me the credit utilization percentage to calculate for each card.

From there it's just multiplying that Credit utilization by the card credit limit to get the projected balance for that card. then Subtracting the current balance by the projected balance to get the payment for that card.

I then rounded the whole function to the penny.

I hope this helps someone else, or if it could be simplified or improved, I am all ears.

Thanks,