Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Is There a VB code to calculate Depreciation?Helpful Member!(2) 

testkitt2 (TechnicalUser) (OP)
18 Dec 05 22:48
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

xplo5iv (TechnicalUser)
19 Dec 05 7:29
Unless I'm missing something (always a possibility!) You're going about this the wrong way. Surely your Db should contain the initial value, start date and depreciation per month, then calculate the value whenever it's needed, ie on a form or report.
Calculating it will be a matter of using DateDiff.

HTH

Jonathan
testkitt2 (TechnicalUser) (OP)
23 Dec 05 0:20
Xplo5iv
Thanks for your response..

I don't have anything setup yet in my DB..I'm trying to get a feel as to how to go about it.The user will have no access to this..only to click and see the current value of the the vehicle... but each month said vehicle will depreciate by what ever the depr value is for that unit.
I want to set it up as a smal pop up form when ..one double clicks on in the text box that shows that current vehicle number..it will bring up a form for that vehicle with its current value.. could it be set up with something like:
below just a sample...not actual code...I know..

Quote:


Year([Date]) year([Now])...book value - depreciation = currentvalue
then when the new month rolls around it will auto take depr value and minus it from curr value to give you the actual value of the vehicle.

quite lost... does anyone know where to go with this/

Thanks to all....in advance.
JZ

Testkitt2

Crowley16 (TechnicalUser)
23 Dec 05 4:12
like xplo5iv said, store the initial values and dates in a table, and a set of depreciation values, and then calculate the current value by subtracting the current date with the start date, and multiplying by the depreciation value...

--------------------
Procrastinate Now!

Helpful Member!  ZmrAbdulla (TechnicalUser)
23 Dec 05 7:17
Have a look at SLN Function, SYD Function, DDB Function in the vba help

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

testkitt2 (TechnicalUser) (OP)
23 Dec 05 22:29
Thanks ZmrAbdulla
When you ref to " SLN Function, SYD Function, DDB Function"
where do I go...this site? or where?
Thanks
JZ

Testkitt2

ZmrAbdulla (TechnicalUser)
23 Dec 05 23:07
These are all functions that calculate depreciation.
Just go to the code window and type SLN and press F1 key. you will be taken to the help page that describes how to use SLN. So other functions.

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

testkitt2 (TechnicalUser) (OP)
24 Dec 05 0:37
ZmrAbdulla
I'll give it a try..Thanks much
Jz

Testkitt2

testkitt2 (TechnicalUser) (OP)
24 Dec 05 22:57
Good eve to all

F1 and SLN was not what I was looking for...
anyone else have any suggestions..

Thank you
JZ

Testkitt2

Remou (TechnicalUser)
25 Dec 05 4:40
Do you mean something like:

CODE

Private Sub TruckNo_DblClick(Cancel As Integer)
Dim intMonths As Integer
Dim curDepreciatedValue As Currency

intMonths = DateDiff("m", Me.PurchaseDate, Date)
curDepreciatedValue = Me.BookValue - (intMonths * Me.Depreciation)
MsgBox "This truck is currently valued at " & Format(curDepreciatedValue, "Currency")
End Sub
testkitt2 (TechnicalUser) (OP)
25 Dec 05 21:51
Thank you Remou
I'm not that good ..if you can clarify a bit.
I would have to make a new table inserting Dates and Bookvalue...yes?

Me.PurchaseDate ...ref to a date in the new table
Me.BookValue ....ref to a $$ value in the new table as well
or could build the table then use the code as an expression in a query?

Thanks for your time
JZ

Testkitt2

Remou (TechnicalUser)
26 Dec 05 6:43
The code is attached to a form, which is bound to a table. As everyone above said, you need a table. The table I built for the test contained the minimum information, that is, TruckNo, BookValue (which I assumed to be purchase price?), purchase date, and devalution, which is an amount. In your in your original post, you said devaluation was by an amount, rather than a percentage. Persumably your truck table would contain a great deal more information than this: capacity, engine number etc etc. The form I built for the test was equally simple. You mentioned double-clicking the truck number and some code running. This code runs from a double-click of a field called TruckNo. It is an illustration of what both xplo5iv and Crowley16 said. I know almost nothing regarding the accounting rules for calculating depreciation (except that they can be quite complicated), so I hope that you are happy with means of calcualtion.
testkitt2 (TechnicalUser) (OP)
26 Dec 05 23:04
Hey thanks there Remou,
I know the answer is probably right in front of in my face.
but I'm lost...
I made a table called "tblTrkDepreciation"
and columns and fields called as follows:

TruckNo
PurchaseDate
bookvalue
monthlydep

When I said that you can double click in the textbox where the truck no is.. is so that a small form ..(not yet developed) will pop up...giving the user the current value of the truck. If I know the bookvalue of the truck..is it necessary to input the date the truck was bought?
If so..no problem.. I'm just trying to set a bookvalue for each truck then when the new month rolls around it will auto calc the trucks value by "said amt". Maybe the purchase has to come into play here.
I don't know..
can you help?
happy holidays....
Thank you
JZ

Testkitt2

ZmrAbdulla (TechnicalUser)
27 Dec 05 0:48
Remou,
BookValue = The value at which an asset is carried on a balance sheet; equals cost minus accumulated depreciation
as per dictionary

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

ZmrAbdulla (TechnicalUser)
27 Dec 05 1:13
Testkitt2,
here is a sample depreciation (15kb zip file contains Access 2000 format mdb)
using Remou's suggestion.

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

Helpful Member!  Ravenous1 (IS/IT--Management)
27 Dec 05 18:15
There are two VB financial functions that I believe you could use here: SYD (for Sum of Years'Digits depreciation method) and SLN (for Straight Line depreciation - probably fits better for your example).

SYD takes 4 arguments:
      cost    (i.e. initial cost of object)
      salvage (i.e. value of asset at end of useful life)
      life    (i.e. useful life of asset - use months
               for your example)
      period  (period over which depreciation is calculated
               - must be same time unit as life argument or
               in months for your example)

So, if your cost is $20,500.00, your salvage value is $500.00, and your useful life is 60 months (5 years), you'd use the function like this:

      SYD(20500, 500, 60, x) *where x is any month at
          which you'd like to state the depreciation for
          that period (month).

Otherwise:

Dim dInitValue, dSalvage, dDepForMonth As Decimal
Dim iLifeInMonths, iPeriodMonth As Integer

dInitValue = 20500.00
dSalvage = 500.00
iLifeInMonths = 60
iPeriodMonth = InputBox("For which month would you like " _
                          & "to calculate depreciation?")
dDepForMonth = SYD(dInitValue, dSalvage, iLifeInMonths, _
                   iPeriodMonth)

SLN takes the same first three arguments as the SYD function and yields a single periodic depreciation value for the useful life of the asset:

      SLN(20500, 500, 60)

Otherwise:

Dim dInitValue, dSalvage, dMonthlyDeprec As Decimal
Dim iLifeInMonths As Integer

dInitValue = 20500.00
dSalvage = 500.00
iLifeInMonths = 60

dMonthlyDeprec = SLN(dInitValue, dSalvage, iLifeInMonths)

MsgBox "Monthly depreciation is: " _
       & Format(dMonthlyDeprec, "Currency")


In Access, you could create a table (tblAssets) with fields for the varying initial cost values, salvage values, and useful life values, and create a query that calculates the monthly depreciation value for you as follows:

SELECT t.AssetID, t.InitCost, t.SalvageVal, t.Lifetime,
   SLN([InitCost], [SalvageVal], [Lifetime]) As DeprecVal
FROM tblAssets As t

In the query design view, choose the tblAssets to base the query on.  Then, add each of the first 4 fields to the design view and create the named "DeprecVal" field as by typing, "DeprecVal: SLN([InitCost], [SalvageVal], [Lifetime])", in the "field" box of the query design view
testkitt2 (TechnicalUser) (OP)
27 Dec 05 22:06
Hello and thanks to all
ZmrAbdulla thanks for your sample...
I changed a few things look at the SQL code below

CODE

SELECT tblTrucks.TruckNumber, tblTrucks.PurchaseAmount, tblTrucks.DepreciationAmount, tblTrucks.ModelYear, tblTrucks.Color, tblTrucks.Notes, [PurchaseAmount]-[DepreciationAmount] AS BookValue, Year([Date]) AS CurYear, Month([Date]) AS CurMonth
FROM tblTrucks
GROUP BY tblTrucks.TruckNumber, tblTrucks.PurchaseAmount, tblTrucks.DepreciationAmount, tblTrucks.ModelYear, tblTrucks.Color, tblTrucks.Notes, [PurchaseAmount]-[DepreciationAmount]
HAVING (((Year([Date]))=Year([Now])) AND ((Month([Date]))=Month([Now])));

The only problem with this code is that it asks for the user to input dates....not what I want...it should look at the current month and depreciate the monthly dep value from the book value..which brings me to another question ..lets
say the book value is 20,000.00 for one truck and it depreciates at 500.00 per month ...this query would not work cause the result of the depreciation per month would not be saved unless I went into the tables each month and adjusted the book value. But for the current month it worked.
I have not tried Ravenous1 suggestion ...but I will...
thanks again..
JZ

Testkitt2

testkitt2 (TechnicalUser) (OP)
27 Dec 05 22:28

Hello Ravenous1

I created a table as you suggested...and your SQL code works
but I left the lifetime value at 1 and it gave me the depreciation for 1 month...thats good..but how will it help me 2 , 3 or 4 months down the road.. I need it to calc and take into account each month seperately or better yet..to calc by the system date...so that each 1st of the month it will look at the book value of each truck (which will be different for each truck) and then calc the dep for each month according to each truck. So if Truck 307084 is valued at 20,000 and dep is 500.00 (per Month)in feb of 2006 the value of that truck will be 19,000.
I dont want to edit or adjust tables.

Thanks again
JZ

CODE

SELECT t.tblassestsid, t.initcost, t.salvageval, t.lifetime, SLN([InitCost],[SalvageVal],[Lifetime]) AS DeprecVal
FROM tblAssets AS t;

Testkitt2

ZmrAbdulla (TechnicalUser)
27 Dec 05 23:13
When you are writing Date into the query design remebmer to add () with that. Otherwise Access will  change it into a field name.

Try this SQL

CODE

SELECT         tbltrucks.trucknumber
,              tbltrucks.purchaseamount
,              tbltrucks.depreciationamount
,              tbltrucks.modelyear
,              tbltrucks.color
,              tbltrucks.notes
,              [purchaseamount]-[depreciationamount] as bookvalue
,              year(date()) as curyear
,              month(date()) as curmonth
FROM           tbltrucks group by tbltrucks.trucknumber
,              tbltrucks.purchaseamount
,              tbltrucks.depreciationamount
,              tbltrucks.modelyear
,              tbltrucks.color
,              tbltrucks.notes
,              [purchaseamount]-[depreciationamount] having (((year(date()))=year(now()))
 AND          ((month(date()))=month(now())));
I didn't change any part except the square brackets to curve. And I am not sure if it works as you required. As I can't see to purchase date to compare the age of the vehicle.

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

raskew (Programmer)
27 Dec 05 23:34
Hi -

tblTruck
   - TruckID (number)
   - PurchasePrice (currency)
   - PurchaseDate (date/time)
   - MonthlyDep (currency)

In this configuration, the following query-SQL will return the  current value of each vehicle.  Note that datediff("m", date1, date2) is insufficient because it only counts the difference between the months, without regard to the days (e.g. ? datediff("m", #11/30/05#, #12/01/05#) returns 1 when in fact only one day has expired.

CODE

SELECT tblTrucks.TruckID
  , tblTrucks.PurchasePrice
  , tblTrucks.PurchaseDate
  , tblTrucks.MonthlyDep
  , [PurchasePrice]-([MonthlyDep]*(DateDiff("m",[PurchaseDate],Date())+(Day([PurchaseDate])>=Day(Date())))) AS CurrentValue
FROM
   tblTrucks;
Give that a shot, exchanging table and field names as necessary.  In my tests (using A97) it worked.

Best wishes - Bob
Ravenous1 (IS/IT--Management)
28 Dec 05 3:01
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 (IS/IT--Management)
28 Dec 05 9:38
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 (TechnicalUser) (OP)
28 Dec 05 20:54
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 (TechnicalUser) (OP)
28 Dec 05 23:20


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...
 

CODE

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 (IS/IT--Management)
29 Dec 05 3:06
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 (TechnicalUser)
29 Dec 05 3:20
Ravenous1,
Nice write up.thumbsup

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

testkitt2 (TechnicalUser) (OP)
29 Dec 05 21:55
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....

CODE

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

Thanks again
JZ

Testkitt2

Ravenous1 (IS/IT--Management)
30 Dec 05 1:15
The important thing is that you got what you needed.  Good luck!!!
ZmrAbdulla (TechnicalUser)
30 Dec 05 5:11
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 (TechnicalUser) (OP)
30 Dec 05 21:50
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close