Splitting Amount into equal payments across several cells

Rosa Crisostomo

Hello,

I am needing some help trying to figure out a formula that will take an amount due and split it across a determined number of months. I would also like it to return a "due date" based on a "start date".

Total Amount: $77,760.00

# of Months: 6

Start Date: 2/1/2021

I would like to have the amount due ($77,760) split between 6 payments. Each payment will populate in its corresponding month (payment cells) and return the due date. As you can see I did not get very far and all of the formulas I came across factored in interest, which we do not use. Thank you all in advance!!

Paul Newcome

Are you able to copy/paste your exact formula directly from your sheet? It shouldn't be doing that as the first portion of the IF is comparing the number in the [# of months/ ...] column to how many columns you have moved over. If the number of months is greater than or equal to the number of columns we have moved to the right, then it should populate. If the number is less than how many columns we have moved over, then the output should be blank.

Paul Newcome

Try inserting this into [Payment 1] and then dragfilling across the rest of the payment columns (make sure the column names are correct)...

=IF($[# of months/ ...]@row >= COUNTIFS($Balance@row:Balance@row, OR(@cell = "", @cell <> "")), $[Phase Allotted]@row / $[# of months/ ...]@row)

This can also be set as a column formula in each of the payment columns.

Rosa Crisostomo

Hi Paul,

Thank you so much for your help! The formula worked, however, should it populate an amount past the indicated # of months? For instance, the payment for $77,760 should be invoiced over 6 months, but when I drag filled the formula across the rest of the payment columns, it populates an amount past payment 6. Can we get these columns to zero out once the # of months/payments indicated are met?

Paul Newcome

Are you able to copy/paste your exact formula directly from your sheet? It shouldn't be doing that as the first portion of the IF is comparing the number in the [# of months/ ...] column to how many columns you have moved over. If the number of months is greater than or equal to the number of columns we have moved to the right, then it should populate. If the number is less than how many columns we have moved over, then the output should be blank.

Rosa Crisostomo

Hi Paul,

I re-checked my formula, looks like I was missing the $ in front of [($Balance@row:Balance@row]. It works like a charm, now! Thank you so much!!

Best,

Rosa

Paul Newcome

That would do it. Happy to help. 👍️

Rosa Crisostomo

Hi Paul,

I have one more question regarding the same sheet. I would like for [P1 Paid] to be checked off automatically if the corresponding invoice # in [P1 Invoice] is referenced in [Base Contract Amount] AND [Paid] is checked. Here is the formula I have but it's obviously not working for me:

=IF(AND([Paid]:[Paid] = 1, [Base Contract Amount]:[Base Contract Amount] = [P1 Invoice]@row), 1, 0)

THANK YOU AGAIN!!!

Paul Newcome

Try this...

=IF(COUNTIFS([Base Contract Amount]:[Base Contract Amount], [P1 Invoice]@row, Paid:Paid, 1) = 1, 1)

Rosa Crisostomo

It worked! Thanks again, Paul! 😊

Paul Newcome

Happy to help. 👍️