Formula question

Question

Hi all,
I am working on an app that calculates loans including deferred loans. Right now, if someone has not paid for a long time and the payment is less than the interest owed, the principle comes back as a negative number rather than a zero. (Current formula is payment amount-interest=principle). I need to make the formula show that there should never be a negative number for principle - just a zero if the amount paid is less than total interest owed. How can I limit the results to not get a negative number?------------------------------Jesse Edsell-Vetter------------------------------

chayceduncan · Answer

Lots of options. I would recommend (1) an if statement or (2) a 'max' function
IF Statement
if( ([Amount] - [Interest]) &lt; 0, 0, [Amount]-[Interest])
if the value of amount - interest is less than 0, return 0 else return amount - interest&nbsp;
Max function
Max([Amount]-[Interest],0)
This will return 0 in the event that amount - interest is a (neg) value
------------------------------Chayce Duncan------------------------------

jesseedsell-vet · Answer

Thanks!------------------------------Jesse Edsell-Vetter------------------------------

jesseedsell-vet · Answer

Formula:
[Amount]-[Interest]Max([Amount]-[Interest],0)
But it says that there are extra characters beyond the end of the formula. What am I missing?
------------------------------Jesse Edsell-Vetter------------------------------

chayceduncan · Answer

You should only need the second part - it looks like you left the original formula in there and QB can't decipher the two calculations without some adjustment. You should just need:&nbsp;

Max([Amount]-[Interest],0)

Delete the first row with amount - interest------------------------------Chayce Duncan------------------------------

jesseedsell-vet · Answer

Another formula question for another report in the same app:Need to sum all payments from a repayments table (_DBID_PAYMENTS) which has payment date and payment amount fields based on the following:Dates that are used to sum these payments to be are on or before the effective date on a separate table called cash/noncash draws (_DBID_CASH_AND_NON_CASH_DRAWS)ThanksJesse&nbsp;

Forum Discussion

Formula question