Skip to main content
Question

Depreciating Value by Month

  • June 29, 2018
  • 0 replies
  • 100 views

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!
This topic has been closed for replies.

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. 

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)

  • Author
  • Registered
  • July 11, 2018
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.

  • Author
  • Registered
  • July 11, 2018
It seems that the var date Today needs to equal the date (first day of the month) that this formula should identify.

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.

  • Author
  • Registered
  • July 12, 2018
You have to first start by wearing a trench-coat, all time travelers do these days.

  • Author
  • Registered
  • July 12, 2018
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])

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.

  • Author
  • Registered
  • July 17, 2018
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)?

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.

  • Author
  • Registered
  • July 17, 2018
Thank you! That's exactly what i hoped to hear.