Depreciation Schedule

[Annette]

I have an asset table that lists the date acquired and life span in
years along with date disposed,etc. I calculate the depreciation
schedule when each item is viewed on the screen. I have a table that
holds this information. So when I am viewing another asset, the
information from this table is deleted and new data is written to the
table. The data that is written is the asset id, month end date and
depreciation amount. This then gets listed in a subform and presented
as monthly depreciation and a year summary deprecation in a different
subform.

So an asset purchased on 03/01/2007 that has a life span of 4 years
will have 48 depreciation records written to the table, one for
03/31/2007, 04/30/2007, 05/31/2007, etc until the asset completes its
life span or is disposed of. This is accomplished by using a for/next
loop and adding records to a table.

In addition I will need to do this same thing when looking at "all
assets" when determining a departments assets and current depreciation/
book values.

I now that having tables like these "bloats" the database. Is there a
different way I can do this with a select query or any other
suggestions?

 

[Guest]

Annette,

If this is straight line depreciation, then you could use the DateDiff( )
function to determine how many months since the placed in service date, and
compare that to the Depreciation periods value to get the pct of
depreciation, something like:

Select Department
, ItemID
, ItemName
, [PurchaseDate]
, [PurchasePrice]
, [Lifespan]*12 as [DepPeriods]
, 1-iif(DateDiff("m", [PurchaseDate], Date())>[DepPeriods],
1,
DateDiff("m", [PurchaseDate], Date())/[DepPeriods]) as
Depreciation
, [PurchasePrice] * [Depreciation] as [PresentValue]
From yourTable

It might be easier and run quicker to write a short function to calculate
the depriciation. Then you could just call this function in your query.

Public Function PresentValue(PurchasePrice as currency, _
PurchaseDate as date, _
LifeSpan as integer, _
Optional EvalDate as date = Date())

Dim intDepPeriods as integer, intPeriods as integer

intDepPeriods = LifeSpan * 12
intPeriods = DateDiff("m", PurchaseDate, EvalDate)
if intPeriods >= intDepPeriods then
PresentValue = 0
else
PresentValue = PurchasePrice * (intPeriods/intDepPeriods)
endif

end sub