Forum Discussion

VictoriaLaw's avatar
VictoriaLaw
Qrew Trainee
6 years ago

Formula for Amortization

Hello! I do hope I'm posting this in the correct spot after the Community UI change. I've been wracking my brain trying to come up with a formula that accomplishes what I need for my app.

We buy software licenses, and we record spend for each year depending on how many months are in the contract period.

Example of what I'm trying to accomplish (using numbers I'm making up):
We buy a QuickBase enterprise license for $12,000 that's good for 1 year (12 months) on September 1st. The cost works out to $1,000/month. This means $4,000 needs to show in a field for 2019, and the remaining $8,000 will show in a field for 2020. (and so on if needed)

I have these fields already set up that I'm thinking are pieces needed in this equation.
Order Total
Order Date
Contract Period in Months

I started out thinking I need a formula that will divide the Order Total by Contract Period in Months to work out the monthly cost, and compare it against the order date to know how many months are left in 2019 to display what I need but I have no idea how to go about writing it. I'm hoping there is a formula guru here that can take a look and steer me in the right direction.

------------------------------
Victoria Law
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    So, before i reply are you sure that you want hard coded fields for the years.  You will make yours elf crazy .... would a better way to go be to have a fields for the 

    [Last Year amortization]
    [Current Year Amortization]
    [Next Year Amortization]

    That way your reports would all be stable and you would not need to keep adding new fields every year.

    ------------------------------
    Mark Shnier (YQC)
    markshnier2@gmail.com
    ------------------------------
    • VictoriaLaw's avatar
      VictoriaLaw
      Qrew Trainee
      6 years ago
      Most definitely... thanks for calling that out. I just created them as formula - numeric, displaying as currency. I'll tweak as needed which they will since I'm still a novice

      ------------------------------
      Victoria Law
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago
        ... and then just to keep it interesting, the order date can be any day of the month, I suppose, do do we need to deal with partial months??  Or can we keep it in monthly buckets like a contract with an order date of Jan 15th would get say 1/12th into January if it was a 1 yr contract.   Partial months make it more complicated.

        ------------------------------
        Mark Shnier (YQC)
        markshnier2@gmail.com
        ------------------------------
  • LukeMcInnes's avatar
    LukeMcInnes
    Qrew Cadet
    6 years ago
    I would just have one formula field something like this... just add as many years as your longest contract, this one just to the following year, so if you have contracts over 12 months just add a bit on the end to account for that.

    //work out monthly cost
var number MonthlyCost = [Order Total] / 12;

//define contract length
var number contractLength = [Contract Period in Months];
//remaining months in year including current month
var number monthsLeftInCurrentYear = 13 - Month([Order Date]);

//calculate current month, taking into account if the contract ends before the end of the year
Year([Order Date]) & " " & 
ToText(If ($contractLength - $monthsLeftInCurrentYear <= 0, $contractLength * $MonthlyCost, $monthsLeftInCurrentYear * $MonthlyCost)) &

//calculate the next year, taking into account if the contract ends before the end of the year
If ($contractLength - $monthsLeftInCurrentYear > 0, "\n" & ToNumber(Year([Order Date]) + 1) & " " & If($contractLength - $monthsLeftInCurrentYear <= 12,ToText(($contractLength - $monthsLeftInCurrentYear) * $MonthlyCost),ToText(12 * $MonthlyCost)))

​


    ------------------------------
    Luke McInnes
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    I'm not understanding Luke's response as it seems to give s text result which is not what you are looking for. 

    I will respond when I get a chance.

    ------------------------------
    Mark Shnier (YQC)
    markshnier2@gmail.com
    ------------------------------
    • LukeMcInnes's avatar
      LukeMcInnes
      Qrew Cadet
      6 years ago
      It isn't really clear what he needs it for, I'm not sure if he needs a text result or not, but the formula will show the amortization for each entry regardless of how old it is.  If you create fields like you have suggested, you would not be able to use or view the details after a year (unless you create infinite fields).  This way you have the amortization listed by year in one field regardless of how old it is.  You would be able to report on this data by formula if required.  You could use it in conjunction with your method if you are wanting more detailed reports of the previous/current/next years too.

      Edit:
      You could also just change the field type and return the figure that is calculated and leave off the year to use this as a number for reports in a 'current year' or 'next year' field

      ------------------------------
      Luke McInnes
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago
        I believe Victoria was looking for what I suggested in my initial response, which was a numeric result and say four fields for last year, current year, next year, and subsequent year.

        The fields would give a floating result depending on what the current year is

        ------------------------------
        Mark Shnier (YQC)
        markshnier2@gmail.com
        ------------------------------