Is There a VB code to calculate Depreciation? 2

[testkitt2]

Would anyone know How to calculate Depreciation?

We have 72 Trucks all which depreciate differently.
what I'm trying to do is start with a $ book value
for each truck and then depreciate each truck by a value
each month.
Lets say that truck #307083 is valued on the books for $20,500.00 and it depreciates at $500.00 each month. so that on the first of each month that comes due.. $500.00 will come off the book value and so on and so forth.

I need the above to be in VB code..as the users who use the DB can not alter the DB values each month on their own. The code needs to depreciate each truck automatically each month.

Thanks for all your help.

Testkitt2

 

[Ravenous1]

Sorry, I didn't piece everything together to understand what you needed until I read your last couple of responses. As I now understand it, you want an automated means of calculating the declining book value (i.e. cost minus accumulated depreciation) for a given month. Therefore, Remou's earlier response gives you the final bit to what I suggested.

It definitely appears as if you want the straight line depreciation model - the most often used method that meets generally accepted acct principles. To completely automate the declining book value, we need one more entry in the tblAssets [DateAcquired] and one or more calculated values:

SELECT t.AssetID,
t.InitCost,
t.SalvageVal,
t.Lifetime,
t.DateAcquired,
Format(SLN([InitCost],[SalvageVal],
[Lifetime]),"Currency") AS MonthlyDeprecVal,
DateDiff("m",[DateAcquired],Now()) AS MonthsDeprec,
[MonthsDeprec]*[MonthlyDeprecVal] As AccumDeprec
[InitCost]-([MonthsDeprec]*[DeprecVal]) AS BookVal
FROM tblAssets AS t

Hope this makes sense...

 

[Ravenous1]

Raskew has a point about the DateDiff. However, you should also consider typical accounting practices for your put in service date.

Typically, if you put an asset in service the 1st half of the month, you can calculate its accumulated depreciation the same as if you put it in service on the 1st of that month. On the other hand, if you put an asset in service in the 2nd half of the month, you can treat it just the same as if you put it in service on the 1st of the following month.

Therefore, you could modify the [DateAcquired] as follows:

IIF(Day([DateAcquired]) > 15, IIF(Month([DateAcquired]) <> 12, DateSerial(Year([DateAcquired]), Month([DateAcquired]) + 1, 1),DateSerial(Year([DateAcquired]) + 1, Month([DateAcquired]) + 1, 1), DateSerial(Year([DateAcquired]), Month([DateAcquired]), 1)) AS BookDate

 

[testkitt2]

good eve to all

here is a sample....which does not resolve the problem

if I know the bookvalue...and the dep per month why do I need the purchase date ??
Ok I see if I know the purchase date then... it can calc amount of time left on the vehicle ...less the dep...to calc the current dep....the file below is just a sample...based on the suggestions above...but still I'm lost.

http://www.geocities.com/testkitt2/depreciation.zip

thanks for all your efforts..
jZ

Testkitt2

 

[testkitt2]

Ravenous1
Just to clarify..the value of the vehicle are calc month to month...it doesn't matter if the vehicle is aquired in the beginning, middle or end of the month...it depreciates the same...month by month...I like your code although ...could you upload an example..just to see it work...

IIF(Day([DateAcquired]) > 15, IIF(Month([DateAcquired]) <> 12, DateSerial(Year([DateAcquired]), Month([DateAcquired]) + 1, 1),DateSerial(Year([DateAcquired]) + 1, Month([DateAcquired]) + 1, 1), DateSerial(Year([DateAcquired]), Month([DateAcquired]), 1)) AS BookDate

Thanks
JZ

Testkitt2

 

[Ravenous1]

We may be talking about two different things. The book value in accounting actually refers to undepreciated cost of a fixed asset at a given point in time - not the "blue book" value. Moreover, depreciation is calculated from the actual cost of an asset - not its value - from the time you purchase it or "put it" into service.

When you enter a truck into your books, even though you may have spent money to do so, it's an asset - not an expense.

However, GAAP (Generally Accepted Accounting Principles) guidelines give you the mechanism to systematically claim the depreciable cost of a fixed asset (Initial Cost - Residual Value) as an expense over the useful life of the asset.

Therefore, when you put a truck into service, you must account for it by entering the purchase date or "put in service" date, entering what it cost you, estimating its useful life, estimating its salvage (residual) value at the end of that estimated useful life, and choosing an accepted method to depreciate (i.e. move some or all of the cost from your assets to expenses).

The built-in financial calculation functions that VB provides us require this info as arguments for functions like SYD and SLN.

If your need for and use of depreciation and book value is more generic than the narrower accounting terms, you may still be able to use the VB functions proffered to you. Otherwise, you can create your own customized functions to get at exactly what you want...

 

[ZmrAbdulla]

Ravenous1,
Nice write up.

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[testkitt2]

hello to all and thanks for everyones input..
although I'm sure your suggestions are on target...the one
I decided to use was the one below... maybe because his sample was easy to view and apply...
Ravenous1..not taking nothing away from you.... and your explanation of depreciation were right on ... I needed something simple and quick to use....
All samples and explanations are greatly appreciated... I will try to apply all codes(made available here)in my DB to see what takes place...... this thread for me is completed...thanks again to all who contributed....

final code applied...using a query....

BookValue: [PurchaseAmount]-(DateDiff("m",[PurchaseDate],Date())*[DepreciationAmount])

Thanks again
JZ



Testkitt2

 

[Ravenous1]

The important thing is that you got what you needed. Good luck!!!

 

[ZmrAbdulla]

Remou should get star for that simple solution...
OK I will do it for you.
Remou, a

from me.

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[testkitt2]

Hello and thanks to all who contributed to this thread..
I guess when making decisions on which code is best to use there is no real.... set standard....all of your codes could have been used ..but again ...thanks to all.
JZ

Testkitt2