Hello all, and as usual, thank you for the help in advance!
I want to pay off a list of expenses over a certain number of months using the same amount of money each month. Once one expense is paid off, I want to allocate the money that was going to that expense to the next expense, and so on. Eventually, the total sum of what was being paid to the lessor expenses will be allocated to the most expensive one.
Hopefully this chart helps to illustrate what I am asking. (Row 6 is a space between months, and the next month begins in row 7)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD="align: center"]Expense 1[/TD]
[TD="align: center"]Expense 2[/TD]
[TD="align: center"]Expense 3[/TD]
[TD="align: center"]Expense 4[/TD]
[TD="align: center"]TOTAL[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Beginning balance[/TD]
[TD]$25[/TD]
[TD]$175[/TD]
[TD]$200[/TD]
[TD]$250[/TD]
[TD]$650[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Minimum payment[/TD]
[TD]$25[/TD]
[TD]$45[/TD]
[TD]$30[/TD]
[TD]$15[/TD]
[TD]$115[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Payment I should make[/TD]
[TD]$25[/TD]
[TD]$45[/TD]
[TD]$30[/TD]
[TD]$15[/TD]
[TD]$115[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]New Balance[/TD]
[TD]$0[/TD]
[TD]$130[/TD]
[TD]$170[/TD]
[TD]$235[/TD]
[TD]$535[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Beginning balance[/TD]
[TD]$0[/TD]
[TD]$130[/TD]
[TD]$170[/TD]
[TD]$235[/TD]
[TD]$535[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Minimum payment[/TD]
[TD]$0
[/TD]
[TD]$45[/TD]
[TD]$30[/TD]
[TD]$15[/TD]
[TD]$90[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Payment I should make[/TD]
[TD]$0[/TD]
[TD]$70[/TD]
[TD]$30[/TD]
[TD]$15[/TD]
[TD]$115[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]New Balance[/TD]
[TD]$0[/TD]
[TD]$60[/TD]
[TD]$140[/TD]
[TD]$220[/TD]
[TD]$420[/TD]
[/TR]
</tbody>[/TABLE]
What I need is a formula that will automatically adjust how much of a payment I should make for expense 2 (B9), now that expense 1 is paid off. I still want the total of the payments to be the same $115, but the $25 I paid for expense 1 is now available to be added to expense 2 for a total payment of $70 paid the next month, leaving a balance of $60.
The total minimum payments for expense 1 and 2 is $70, but now that the balance for expense 2 is only $60, I want to pay it off, leaving a balance of $10 that can be added to the minimum payment of expense 3, making that payment $40 instead of $30.
I want to pay off a list of expenses over a certain number of months using the same amount of money each month. Once one expense is paid off, I want to allocate the money that was going to that expense to the next expense, and so on. Eventually, the total sum of what was being paid to the lessor expenses will be allocated to the most expensive one.
Hopefully this chart helps to illustrate what I am asking. (Row 6 is a space between months, and the next month begins in row 7)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD="align: center"]Expense 1[/TD]
[TD="align: center"]Expense 2[/TD]
[TD="align: center"]Expense 3[/TD]
[TD="align: center"]Expense 4[/TD]
[TD="align: center"]TOTAL[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Beginning balance[/TD]
[TD]$25[/TD]
[TD]$175[/TD]
[TD]$200[/TD]
[TD]$250[/TD]
[TD]$650[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Minimum payment[/TD]
[TD]$25[/TD]
[TD]$45[/TD]
[TD]$30[/TD]
[TD]$15[/TD]
[TD]$115[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Payment I should make[/TD]
[TD]$25[/TD]
[TD]$45[/TD]
[TD]$30[/TD]
[TD]$15[/TD]
[TD]$115[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]New Balance[/TD]
[TD]$0[/TD]
[TD]$130[/TD]
[TD]$170[/TD]
[TD]$235[/TD]
[TD]$535[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Beginning balance[/TD]
[TD]$0[/TD]
[TD]$130[/TD]
[TD]$170[/TD]
[TD]$235[/TD]
[TD]$535[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Minimum payment[/TD]
[TD]$0
[/TD]
[TD]$45[/TD]
[TD]$30[/TD]
[TD]$15[/TD]
[TD]$90[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Payment I should make[/TD]
[TD]$0[/TD]
[TD]$70[/TD]
[TD]$30[/TD]
[TD]$15[/TD]
[TD]$115[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]New Balance[/TD]
[TD]$0[/TD]
[TD]$60[/TD]
[TD]$140[/TD]
[TD]$220[/TD]
[TD]$420[/TD]
[/TR]
</tbody>[/TABLE]
What I need is a formula that will automatically adjust how much of a payment I should make for expense 2 (B9), now that expense 1 is paid off. I still want the total of the payments to be the same $115, but the $25 I paid for expense 1 is now available to be added to expense 2 for a total payment of $70 paid the next month, leaving a balance of $60.
The total minimum payments for expense 1 and 2 is $70, but now that the balance for expense 2 is only $60, I want to pay it off, leaving a balance of $10 that can be added to the minimum payment of expense 3, making that payment $40 instead of $30.