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

 

[xplo5iv]

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]

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

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]

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!

 

[ZmrAbdulla]

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]

Thanks ZmrAbdulla
When you ref to " SLN Function, SYD Function, DDB Function"
where do I go...this site? or where?
Thanks
JZ

Testkitt2

 

[ZmrAbdulla]

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]

ZmrAbdulla
I'll give it a try..Thanks much
Jz

Testkitt2

 

[testkitt2]

Good eve to all

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

Thank you
JZ

Testkitt2

 

[Remou]

Do you mean something like:

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]

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]

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]

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]

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]

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

 

[Ravenous1]

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]

Hello and thanks to all
ZmrAbdulla thanks for your sample...
I changed a few things look at the SQL code below

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]

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

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

Testkitt2

 

[ZmrAbdulla]

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

SELECT         tbltrucks.trucknumber
,              tbltrucks.purchaseamount
,              tbltrucks.depreciationamount
,              tbltrucks.modelyear
,              tbltrucks.color
,              tbltrucks.notes
,              [purchaseamount]-[depreciationamount] as bookvalue
,              year([b]date()[/b]) as curyear
,              month([b]date()[/b]) as curmonth 
FROM           tbltrucks group by tbltrucks.trucknumber
,              tbltrucks.purchaseamount
,              tbltrucks.depreciationamount
,              tbltrucks.modelyear
,              tbltrucks.color
,              tbltrucks.notes
,              [purchaseamount]-[depreciationamount] having (((year([b]date()[/b]))=year([b]now()[/b]))
 AND          ((month([b]date()[/b]))=month([b]now()[/b])));

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]

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.

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