Calculate Depreciation in Correct Months

[Pandora23]

I am new to Access so hoping someone can help me out. I am trying to build a depreciation table. I have previously built this in Excel and use an IFERROR formula to do my calculations (

=IFERROR(IF(V$3=MEDIAN(EOMONTH($F58,-1)+1,$G58,V$3),$H58,0),0)

where V3 is 1/1/22, F58 is Acquisition Date, G58 is End of Life date and H58 is the Monthly Depreciation amount). I don't know how to do this in Access. I have created a table with the same Field Names as the Excel file although I don't know how to make Field Names recognized as dates in order to try and replicate the formula. Can anyone help guide me to where I need to start?



Thank you!

[Micron]

A couple of things to consider - spreadsheet layout usually makes for poor table design and typically we don't store calculations in a database. You calculate on the fly in forms and reports. That is because of the relational nature of databases, which is something you don't usually have to worry about with spreadsheets. I could suggest that you Google a term like ms access calculate depreciation to take advantage of existing solutions, but they might not help if your db is not normalized. A pic of your relationships (if you've created them) might help. If not, a pic of relationships window with the table(s) you're using for this calc might help us figure that out.

[CarlettoFed]

Maybe it's better if you attach a sample excel file.

[orange]

See if this helps.

[nilber]

What are you trying to calculate with this formula in Excel? If I'm interpreting your Excel formula properly, that whole formula is to determine if the asset depreciates on 1/1/22, with the resultant cell value being the depreciation amount or zero. Is this part of a larger worksheet that carries the asset value being depreciated? If so, this formula is one step in a larger dataset. To build a dataset like this in a database, you can't use the Excel technique that each row exists individually, and you can keep a running balance (or value) by referencing a cell in the previous row. Records (rows) in a database inherently don't exist in a specific order and you can't simply reference the previous row. Two basic methods for replicating this Excel technique are:

1. to use a record ID number, determine the ID number of the record you are performing this calculation, and decrement the ID by one to determine the "previous row"
2. determine the date of the record you are performing this calculation on, and decrement that by your time period (month, year, etc.) for the previous row.

Where is V3 being stored in the database? Is the depreciation table calculating depreciation over multiple years or months? IE is the value in V4 1/1/23?

Avoid any temptation to use a calculated column in your table, any calculations should be done in a query or using Visual Basic.

Assuming the table you built has the following fields ("Acquisition_Date", "End_Of_Life_Date", "Monthly_Depreciation_Amount", "Depreciated_Value" <-unknown column name with formula in it) and V3 isn't stored in the database, there are a number of ways to start this.

1. A parameter query on the table (the parameter prompting for manual input of the value in V3) and add a calculated field (right click in empty field name and select "Build" option). You will use Iif(), Datepart(), and something in place of median, then reference each field by the table and field name as [tbl_depreciation].[Acquisition_Date] to build your formula.
2. A basic form displaying the table you created. For the "Monthly_Depreciation_Amount" field use VBA to get the fixed values (Acq date, EOL date, monthly depreciation amount), calculate the depreciation amount, then update that into the form/update the value in the table.
3. Probably 100 other ways.

This sounds like it is probably part of a larger dataset where you are calculating multiple time periods of depreciation and multiple assets being depreciated.

My recommendation would be a minimum of two tables.
Table 1 would hold asset information

• AutoID
• IDENTIFIER (Name or description of asset)
• ACQ DATE
• USEFUL LIFE (As months or years...pick one...depends on how often you're calculating depreciation)
• INITIAL VALUE
• SALVAGE VALUE

Table 2 would hold depreciation data

• DepreciationID
• AutoID (relationship to table 1)
• DEPRECIATION DATE (similar to V3)
• DEPRECIATED VALUE

Table 1 is "manual inputs" to calculate all remaining information.

Build a query on top of table 1 that calculates:

• EOL DATE (ACQ DATE + USEFUL LIFE)
• MONTHLY DEPRECIATION AMOUNT (or 'yearly' if calculating depreciation annually) ( [INITIAL VALUE - SALVAGE VALUE] / USEFUL LIFE ) make sure to adjust depending on USEFUL LIFE

Reference this query in any forms/reports/etc so you have access to all relevant fields.

For table 2, use a combination of VBA and definition queries to create the depreciation data. The VBA will do the heavy lifting for calculating each row, and then append each row with a definition query to table 2 as VBA calculates the data. If changing Asset data, use VBA and an update query as needed to go into table 2 and adjust data for that asset. Table 2 would never be manually manipulated.

There's a lot to unpack there, but hopefully that gives you a little better idea on where to start (or the challenges you're facing).