One way:
I'm not sure where you get 33% and 67%, but if you want to pay off in
the ratio of the loans...
A1: 12000000
A2: 21000000
B1: CA
C1: =A1*MIN(1,B1/(A1+A2))
C2: =MIN(A2,B1-C1)
Where C1 is applied to the A1 loan and C2 is applied to the A2 loan.
In article ,
Steven wrote:
I can't get the proper if/then statement to work. I know I need If/Then and I
may need an and or two and maybe a MIN or MAX but I'm not sure. The problem
is as follows: I take out 2 loans that I have to pay back over time. Loan A =
$12 million and loan B = $21 million. Each year my business will have cash
available to pay the loans. So I need 2 formulas to tell how much to pay back
of loan A and B. So first the formula has to see if I have cash available
(CA) to pay back the loans. Then it needs to pay off some % of A and some of
B until they are both paid back. But it obviously shouldn't pay back more
than I owe. For example, if CA was $40 million, it should return $12MM for A
and $21MM for B. But if CA was only $10MM then it should pay 33% of the $10MM
for A and 67% of the $10MM for the B.
|