Forum Discussion

JesseEdsell-Vet's avatar
JesseEdsell-Vet
Qrew Member
12 months ago

Formula 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
------------------------------
  • Lots of options. I would recommend (1) an if statement or (2) a 'max' function

    IF Statement

    if( ([Amount] - [Interest]) < 0, 0, [Amount]-[Interest])

    if the value of amount - interest is less than 0, return 0 else return amount - interest 

    Max function

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

    This will return 0 in the event that amount - interest is a (neg) value



    ------------------------------
    Chayce Duncan
    ------------------------------

    • JesseEdsell-Vet's avatar
      JesseEdsell-Vet
      Qrew Member

      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's avatar
        ChayceDuncan
        Qrew Captain

        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: 

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

        Delete the first row with amount - interest



        ------------------------------
        Chayce Duncan
        ------------------------------
    • JesseEdsell-Vet's avatar
      JesseEdsell-Vet
      Qrew Member

      Thanks!



      ------------------------------
      Jesse Edsell-Vetter
      ------------------------------