Discussions

 View Only
Expand all | Collapse all

Depreciating Value by Month

  • 1.  Depreciating Value by Month

    Posted 06-28-2018 20:43

    I need help with a formula that depreciates the value of an item each month until it's value hits $0.00

         I have a single table that contains these fields:

         - Depreciation Start Date (Date Field) i.e. 6/28/2018

         - Depreciation Term (Numeric) i.e. 36 months

         - Item Cost (Currency) i.e. $100

         - Monthly Deprecation Value (Currency): Item Cost / Depreciation Term i.e. $2.77

         - Remaining Deprecation Value (Formula ? Currency): This is the field that should display             the remaining value, and it should stop at $0. 

    If a user selects the Depreciation Start Date to be June 28th, 2018, then on June 1st, 2018 the Remaining deprecation value will be adjusted.  And on July 1st, August 1st, Sept 1st etc. it will be adjusted again until it?s value hits zero. (So, the Month contained in the Depreciation Start date is all that really matters...hopefully i'm making sense here)

    Please ask any questions - any help is greatly appreciated!


  • 2.  RE: Depreciating Value by Month

    Posted 06-28-2018 23:34
    Was there a typo here?

    If a user selects the Depreciation Start Date to be June 28th, 2018, then on June 1st, 2018the Remaining deprecation value will be adjusted. 


  • 3.  RE: Depreciating Value by Month

    Posted 06-29-2018 14:55
    It's actually correct. Even if a user selects a day that occurs mid month, it should default to the first day of the month selected.


  • 4.  RE: Depreciating Value by Month

    Posted 07-11-2018 22:37
    It's actually correct. Even if a user selects a day that occurs mid month, it should default to the first day of the following month selected.


  • 5.  RE: Depreciating Value by Month

    Posted 07-12-2018 00:16
    Are you saying that the calcs are correct except has 1 too many depreciation months?


  • 6.  RE: Depreciating Value by Month

    Posted 07-12-2018 17:21
    Yes - sorry just noticed this response!


  • 7.  RE: Depreciating Value by Month

    Posted 07-12-2018 23:39
    Try making a change from this

    var date EffectiveStart = FirstDayOfMonth([Start Depreciation]);

    to this

    var date EffectiveStart = LastDayOfMonth([Start Depreciation]) + Days(1);


  • 8.  RE: Depreciating Value by Month

    Posted 06-29-2018 00:51
    Try this.  The purpose of defining today as a fixed date is to be able to try different values for total to test the formula.

    var date Today = Date(2019,1,15);

    var date EffectiveStart = FirstDayOfMonth([Start Depreciation]);
    var date EffectiveToday = FirstDayOfMonth($Today);

    var date ES = $EffectiveStart;
    var date ET = $EffectiveToday;

    var number MonthsStepOne = (Year($ET) - Year($ES)) * 12;

    var number MonthAdjustment = If(Month($ET) >= Month($ES), Month($ET) - Month($ES) +1,  // both ends count as a month, so add 1
         (Month($ET) - Month($ES)) + 1);
       
    var number Months = $MonthsStepOne + $MonthAdjustment;

    Max(0, [Original Amount] - [Original Amount]/[Depreciation Term (months)] * $Months)


  • 9.  RE: Depreciating Value by Month

    Posted 07-11-2018 18:52
    This got me close but it didn't output the number i was hoping.  I have an item with Original Ammoun = $100 and Deprecation Term (months) = 36.  The Start Deprecation = July 1.  But the formula is outputting a value of $80.55. The actual value should be $97.23 ($100 - $2.77). 

    Maybe i'm misunderstanding what you meant by "The purpose of defining today as a fixed date is to be able to try different values for total to test the formula.


  • 10.  RE: Depreciating Value by Month

    Posted 07-11-2018 18:55
    It seems that the var date Today needs to equal the date (first day of the month) that this formula should identify.


  • 11.  RE: Depreciating Value by Month

    Posted 07-11-2018 19:03
    Once you feel that the logic is working for a day of your choice, you would set that line to read

    var date Today = Today();

    I did it that way to have a way to test my logic as while I am all powerful, I cannot yet  actually time travel to different worlds where today is a different today.


  • 12.  RE: Depreciating Value by Month

    Posted 07-11-2018 19:14
    >I cannot yet  actually time travel to different worlds

    Use script and live in different worlds as you wish.


  • 13.  RE: Depreciating Value by Month

    Posted 07-11-2018 20:26
    You have to first start by wearing a trench-coat, all time travelers do these days.


  • 14.  RE: Depreciating Value by Month

    Posted 07-11-2018 20:58
    I did a image style transfer using my avatar (writer monad) and the Dane Shadow Black Long Coat theme and here is what I got:



    Deepart
    https://deepart.io/


  • 15.  RE: Depreciating Value by Month

    Posted 07-12-2018 16:26
    I was able to get this to do exactly what i wanted. Thanks for all the help!

    var date Today=Today();

    var date EffectiveStart = FirstDayOfMonth([Deprecation Start Date]);
    var date EffectiveToday = LastDayOfMonth($Today);

    var date ES = $EffectiveStart;
    var date ET = $EffectiveToday;

    var number MonthsStepOne = (Year($ET) - Year($ES)) * 12;

    var number MonthAdjustment = If(Month($ET) >= Month($ES), Month($ET) - Month($ES),  // both ends count as a month, so add 1
         (Month($ET) - Month($ES)));
       
    var number Months = $MonthsStepOne + $MonthAdjustment;

    Min(Max(0, [Original Amount] - [Original Amount]/[Deprecation Term (months)] * $Months),[Cost])


  • 16.  RE: Depreciating Value by Month

    Posted 07-12-2018 16:29
    Great, thx for letting me know.  Please be sure to ask all your questions while I'm trapped in a horrible waiting room waiting for an airplane.  That is why I had the time.


  • 17.  RE: Depreciating Value by Month

    Posted 07-17-2018 14:47
    Slightly off topic... Do quickbase automations ignore anything triggered via a webhook or records being modified by another application, much like the way form rules won't trigger off of a webhook (form rules only work when a user opens the record and interacts with it)?


  • 18.  RE: Depreciating Value by Month

    Posted 07-17-2018 14:53
    QuickBase Actions and Automation trigger off any change to the database by itself (ie the automation can get itself into a loop), or any other Action, Automation, human input, grid edit, manual import, API - anything database change.  The "trigger" is at the database level and not the interface level.


  • 19.  RE: Depreciating Value by Month

    Posted 07-19-2018 17:55
    Is there a maximum amount of Automations and do Automations count against the application's webhook threshold?


  • 20.  RE: Depreciating Value by Month

    Posted 07-19-2018 18:29
    I suggest that you post this as a new question.


  • 21.  RE: Depreciating Value by Month

    Posted 07-19-2018 18:30
    kk


  • 22.  RE: Depreciating Value by Month

    Posted 07-17-2018 14:59
    Thank you! That's exactly what i hoped to hear.