Formula question

  • Thread starter Thread starter D Hafer - TFE
  • Start date Start date
D

D Hafer - TFE

I'm creating a debt payoff sheet, paying off multiple monthly payments. I
want to roll one payment into the next when amount reaches zero. For
example. I have one payment of $300.00 per month a minimum monthly payment
of $250 per month for 3 months. I also have a $600 payment per month for 6
months. When that three month payment is over, I want to roll the 300.00
onto the $600 payment to get to $900.00

Right now, i'm manually doing the additions for when a debt amount due is
greater than zero. I'd like formula that is an if then, for example if the
amount due is greater than 0, then subtrack the payment amount, unless the
payment amount is greater than amount due, then only apply the total amount
due. Any suggestions?
 
Let's say you have balance #1 in A2 and balance #2 is in A3. Default
payment amount for #1 is in B2 and payment for #2 is in B3. Formula
for current payment to make for #1 and #2 (in C2 and C3 respectively):

C2 =MIN(B2+B3*(A3=0),A2)
C3 =MIN(B3+B2*(A2=0),A3)

I actually had a much more complex model set up for a friend, that
tracked which amount has the highest interest accruing figured into
the calculation, and kept record of payment made/to be made on each
scheduled payment date. It used a Solver optimization for least
possible interest, which was handy in determining the monthly payment
amount to use for each account.
 
Back
Top