Depreciating Value by Month

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered

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!
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
  • Groovy, Baby!

Posted 5 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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. 
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
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.
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Are you saying that the calcs are correct except has 1 too many depreciation months?
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
Yes - sorry just noticed this response!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Try making a change from this

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

to this

var date EffectiveStart = LastDayOfMonth([Start Depreciation]) + Days(1);
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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)
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
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."
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
It seems that the var date Today needs to equal the date (first day of the month) that this formula should identify.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,352 Points 20k badge 2x thumb
>I cannot yet  actually time travel to different worlds

Use script and live in different worlds as you wish.
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
You have to first start by wearing a trench-coat, all time travelers do these days.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,242 Points 20k badge 2x thumb
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/
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
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])
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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.
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
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)?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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.
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
Is there a maximum amount of Automations and do Automations count against the application's webhook threshold?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
I suggest that you post this as a new question.
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
Thank you! That's exactly what i hoped to hear.