Brian Entz
Level 1 (5 points)
Mac OS X

Q: problem with payment tally and payoff.

Screen Shot 2016-09-07 at 9.36.31 AM.png

So, in this table column B represents a tally of payments that are made toward a specific due amount of $53819.82 as shown in C2. The $21,527.93 in B4 is the result of monthly payment ($897) in C1 being multiplied by the number of months of payments as shown in A4 (24 months). Never mind that the results are not precise because these figures are being rounded up. The problem is once the tally reaches the full payout amount of $53819.82 the tally should drop to zero because the balance is paid off. So, cell B8 should be zero. How can I do this?

Posted on Sep 12, 2016 10:40 AM

Close
Brian Entz

Q: problem with payment tally and payoff.

  • All replies
  • Helpful answers

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 12, 2016 12:53 PM in response to Brian Entz
    Level 6 (19,037 points)
    iWork
    Sep 12, 2016 12:53 PM in response to Brian Entz

    This may work for you:

    Screen Shot 2016-09-12 at 2.52.02 PM.png

     

    C4=MAX($C$2−B4, 0)

     

    this is shorthand for... select cell C4 then type (or copy and paste from here) the formula:

    =MAX($C$2−B4, 0)

     

    select cell C4, copy

    select cells C4 thru the end of the column, paste

  • by Brian Entz,

    Brian Entz Brian Entz Sep 12, 2016 1:41 PM in response to Wayne Contello
    Level 1 (5 points)
    Mac OS X
    Sep 12, 2016 1:41 PM in response to Wayne Contello

    Actually, C4-C12 is a separate column for a separate calculation. What I want is what to write in B4-B12.  Right now B4 has the formula

     

    =C1*A4

     

    I need to ADD something to that so that if the calculation goes over the content of cell C2 it should go to zero. How can I do that?

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 12, 2016 2:51 PM in response to Brian Entz
    Level 6 (19,037 points)
    iWork
    Sep 12, 2016 2:51 PM in response to Brian Entz

    Brian,

     

    I do not know what you want.  please describe what you want (leaving out how) while including ALL the relevant  information.

     

    the function I provided provides the maximum of the calculation or zero.  whatever that calculation is is irrelevant. 

     

    max(<CALC>, 0) will return max whenever <CALC> is less than zero.

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 12, 2016 2:58 PM in response to Brian Entz
    Level 6 (19,037 points)
    iWork
    Sep 12, 2016 2:58 PM in response to Brian Entz

    =max($C$1*A4-B4, 0)

  • by Brian Entz,

    Brian Entz Brian Entz Sep 12, 2016 6:51 PM in response to Wayne Contello
    Level 1 (5 points)
    Mac OS X
    Sep 12, 2016 6:51 PM in response to Wayne Contello

    This is the fuller spreadsheet.

     

    Screen Shot 2016-09-12 at 6.43.23 PM.png

     

    The Full payoff chart registers a tally of monthly payments at the end of the year. M11 is the monthly payment. L15 is the full amount of payments at the end of the first year. It is $720.18 * 12. The full payment is only $51,852.98. That last payment will be made at the 72nd month. So L20 was M11 * K20. With the full balance paid the cells below it should register zero because no more monthly payments will be made at that time. So, what I want to see is what the formula would be in a cell that currently reads M11 * K21 but needs to be modified so that any amount over M13 is zero. In the M15-M25 column I used your max(<CALC>, 0) formula to get the zeroes in the Pay off column. The M column is fine now, but the L column should not register continued payments being made after the $51,852.98 is reached.

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 13, 2016 5:54 AM in response to Brian Entz
    Level 6 (19,037 points)
    iWork
    Sep 13, 2016 5:54 AM in response to Brian Entz

    Brian,

     

    OK.  Thanks.

     

    1) Just a comment on designing tables in Numbers... the "Numbers" way would be to place each of the "sections" you have shown in separate tables on a blue rectangle (if you recall, then table I posted earlier was a small table just the size of the portion of the view you posted).  There are several reasons for this which will most likely be clear only later

     

    2) I recommend the following for the TOTAL COST

    L15=MIN(K15×$M$11, $M$13)

     

    fill L15 down as needed

     

    this caps the amount in TOTAL COST to the FULL PAYOFF AMOUNT