# Depreciating Value by Month

• 0
• Question
• Updated 11 months ago

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)

• 854 Points
• Groovy, Baby!

Posted 12 months ago

• 0
• 70,434 Points
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.
• 854 Points
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.
• 854 Points
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.
• 70,434 Points
Are you saying that the calcs are correct except has 1 too many depreciation months?
• 854 Points
Yes - sorry just noticed this response!
• 70,434 Points
Try making a change from this

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

to this

var date EffectiveStart = LastDayOfMonth([Start Depreciation]) + Days(1);
• 70,434 Points
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)
• 854 Points
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."
• 854 Points
It seems that the var date Today needs to equal the date (first day of the month) that this formula should identify.
• 70,434 Points
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.

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,204 Points
>I cannot yet  actually time travel to different worlds

Use script and live in different worlds as you wish.
• 854 Points
You have to first start by wearing a trench-coat, all time travelers do these days.

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,184 Points
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/
• 854 Points
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])
• 70,434 Points
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.
• 854 Points
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)?
• 70,434 Points
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.
• 854 Points
Is there a maximum amount of Automations and do Automations count against the application's webhook threshold?
• 70,434 Points
I suggest that you post this as a new question.
• 854 Points
kk
• 854 Points
Thank you! That's exactly what i hoped to hear.