Depreciation Of Fixed Asset

I'm trying to calculate the Depreciation of the fixed asset for some items
I've tried the formulas that came with Excel but i don't know its not working or not give the correct value
so I attached a file as an example
what I'm trying to have is
1- straight line method along the asset life
2- salvage must be ( 1 )
3- if the purchase date is equal to or before the middle of the month (14-15/02/2004) so the start of depreciation have to start from the beginning of the current month ( Feb) but if the date excess the day 15 ( 16/02/2004) the middle of the month , the the depreciation must start from the next month.
so any one can help ?

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

Similar Excel Tutorials

Split Text into Multiple Cells in Excel
How to split text from one cell into multiple cells quickly and easily in Excel.  This includes how to split names, ...
Make Y Axis Start at 0 in Charts in Excel
How to make the Y axis start at 0 in charts in Excel. This is a simple formatting issue. Select the Chart and go to ...
Error bars in Charts in Excel
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
Change Axis Units on Charts in Excel
You can change the size of the units on a chart axis, their interval, where they start, where they finish, and mor ...

Helpful Excel Macros

Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

Similar Topics

I need to make a depreciation schedule for assets that are purchased throughout the year. Attached is a sample schedule for purchases of telephone systems.

Column C is where I want to input the cost of the asset and when the asset was purchased. I then want to depreciate the asset starting on the purchase month (all assets are expected to be fully depreciated in 48 months).

Right now, I'm inputting the purchase amount and period and then manually going to the appropriate column and starting my depreciation there. Obviously this is quite inefficient if I have multiple assets and purchase months; I was wondering if anyone can figure out a formula to automate this process so I can just input the amount and month of purchase and have depreciation go to the appropriate columns. Any help would be greatly appreciated.


As on
Code Asset Valuation Pur. Date Acc. Dep
A001 Furniture 173,995 2001, 31 -Dec.

This formula is not working,

I want to calculate Accumulated depreciation in this way,
if E2 is lesser than D4, no depreciation
if E2 is greater than D4, then calculate Accumulated Depreciation for the period.
but if E2 - D4 is greater than 1825 then just write C4 (value in C4)
I mean Accumulated depreciation should not more than value of asset.

Hey guys, first time poster but a fan of this website; its helped alot before.

However I find myself quite frazzled and here is my conundrum:
For school I have been trying to work out the Excel formula for straight line depreciation over a number of years. I will give the best description I can, here is my data and the cells it is in:
C61: 40,000 <-- This is the cost price/acquisition price of the item
C62: 5 000 <--- This is the residual value of the item i.e. its salvage value
C63: 5 <---- This is the expected life of the item in years

I am required to use the IF function to work out the straight line depreciation over the 5 years and then place it into a 10 year table. Obviously the item will depreciate at $7,000 a year and I did get my results to say this. However we must then be able to copy that formula into the 9 other years and have the depreciation stop after 5 years, as it has hit the salvage value. When I copied my formula it continued the depreciation for the full 10 years, not just the 5 years it was meant to (ie. If i had a list of cells from 1-10 which represented the Life Years of the item, after 5 years the formula should give me 0 amounts as the item has depreciated to $5,000 and would have been sold or disposed). After this I must then also test the formula by changing the expected years of the item, say from 5 to 6, and then it should give me an updated yearly depreciation for 6 years and then starts saying 0 after 6 years as it has reached its salvage value

Could anyone tell me which excel function can be applied below to calculate depreciation by Reducing balance method & how?

Q.Calculate Depreciation using Diminishing Balance Method for 5 Years no extra columns to be added.
Cost $20,000.00 Salvage $2,000.00 Life 6 Total Depreciation for 5 Years


In cell C1 I have a start date (this could be blank).
In cell D1 I have an end date (also could be blank).

I would like to work out the difference in months between the end date and the start date.


If the start/end date DAY is less than 15 then I want to consider it a full month.

If the start/end date DAY is greater than or equal to 15 then I do not want to consider the month.

I have the following to work out the difference but it's big and ugly and I figure there must be a slicker method.



Any help / suggestions appreciated



I am trying to calculate business days in a month. I know I can use NETWORKDAYS to remove holidays, but I don't have a end date for each month, just the start date. Each date for example is 1/1/2010, 2/1/2010, etc... I don't want to manually go in and fill in the end date for each month, so I am trying to find a different way.

For the start date of each month I just start for example with 1/1/2000 and then copy down below with that EDATE(A1,1) then then just copy down so it keeps adding a month and to save calculation time I just paste special value after to update it permanently and not have to have it recalc.

I know I have to add the holidays to the network days function.... unfortunately. Unless there is a way to fill the US federal holidays automatically.... but even if so, that would be a pain because if for example July 4th falls on a Sunday, everyone gets Monday off. I guess it may be possible to somehow calculate all federal holidays, but Im sure I'd have to pull from a external source and I think the time spent writing that would be more of a pain than doing it by hand, and I'd have to add if it fell on a sunday, to give Monday as a holiday.

Anyways, basic idea is, a way to provide me # of buisness days without manually having to enter a end date for each month using NETWORKDAYS. EDATE works half way for the end date... for example I can start with 1/31 then EDATE + 1 to 2/28 because excel knows there isnt 31 days in feb. But then to continue every day for the rest will show as 3/28, 4/28 etc.... how can I change that to the last day of that month?

hello everyone. i am a store keeper, taking care all IT equipments. i need help. i want to make a database where i can capture the name of the asset, the model number, the serial number and how many assets i have in the store room. as the assets are given to the customers the database should subtract.

May you kindly help me with the code to support these needs. I want to insert the information in the excel database using a form.

for example:

asset Name asset Number Asset model asset Serial Number taken by date total left

Hi Guys,
I'm trying to calculate a monthly leave accrual formula for staff on a monthwise table.

In the sample file attached, John has joined on 01-NOV-2010 so his leave accrual will start from Nov @ 2.5 per month. But I would like to have a formula that returns accrual as zero for the months prior to his joining.

Similarly, Mary has joined on 15-Dec-2010 so in the joining month of Dec2010 her leave accrual will be (2.5/31 x 17 days of service in Dec) and for the following months it will be 2.5 per month

Like Ali, who joined on 17-Feb-2011 wil have the zero accruals till the month of Jan and in Feb it will be (2.5/28 x 12days of service in Feb )

I hope the sample is clear. Any remote soultions to build on will be helpful.

I have a spreadsheet with the cost of asset and its useful life to indicate when the asset needs to be replaced. To the right I have 30 columns, one column for each year starting with year 2012. At the top I have the year and the future value factor for each year. I want a formula in the cells to the right of
each asset that calculates the future replacement unit of that assets in the year it needs replacing. I figured out how to do this with an "if" formula but it I can't get it to work for multiple replacements, like when an asset needs to be replaced every 5 years (6 times during the 20 yr replacement cycle). Any ideas? TQ

This may be an accounting function, but I am trying to do the following.

Let's say:
Contract is for $100,000
Start Date is 6/15/09
End Date is 9/30/09

I know I could subtract the dates and divide against $100,000 to get the daily revenue. I could then multiply the daily by the number of days in each month.

However, what I really want to do is get an Excel formula to calculate the monthly amount in the correct fields no matter what the start and end dates and no matter how many days in each month.

The answer in this case would be
Jun = $14,019
Jul = $28,972
Aug = $28.972
Sep = $28,037

Sept only has 30 days, whereas Jul and Aug have 31.


Hello All

New to the board and mostly new to excel. :/

I've found many ways to calculate up time and down time. What my problem is that I need to track these over a peroid of a year in 1 month chunks.

What Ive got so far is;
Colums A & B contain unit Stop and Start Date & time
Ex: 11/2/2010 8:30

Column C subtracts A & B and is formatted Custom [h]:mm
No problems here.

Colum D my Uptime/Availability formula
Where N5 = "=DAY(DATE(YEAR(M2),MONTH(M2)+1,0))*24"
[All of this by the way gleened from various sources on the internet.]

What this does is give me tracking for down time per row and the uptime.

But what it does is change the month each month which is what I wanted to start with. Now what I've been tasked with is to track each month over a year.
Can this be done with what I've got all on one sheet?

How do you calculate the number of weeks in a month in excel. I have a budge with the start date of each month in a cell and I want to compute the number of weeks for that start date's month. How do you do this?


I'm trying to figure out a function to where Excel will calculate headcount. For example if a person worked any day in that month, I need Excel to calculate that as 1 headcount for that month. Also if that person is leaving within the coming month's I need to forecast that person leaving also. I've tried the following function:



A1=Start Date
C1=End Date
B1=First Day of the Month in Question

This IF statement works fine, but it does not capture headcount for people who only worked for a few days or so in the month. For example if A1=8/5/09, C1=8/10/09, and B1=8/1/09, then someone under this circumstance would fall through the cracks so to speak. Can someone help me with this? I'm so close, but yet so far. TIA...

Currently I am using (.htm) files import to excel one by one from a folder in Z drive. In this drive there is one foler named Vidal which is fixed. In this folder there are some sub folders like Year wise folder(2010,2011 etc). In Year folder there is Month wise folder like (May,June etc). In month folder there are day wise folders like (010611,020611, ddmmyy). In this folder some htm files we are storing regular basis. The htm file names like (C_MTM_CM_5067_20110603_CM.htm). Every day only date is changed and remaining all keeps same. C_MTM_CM_ is a fixed name. 5067 also fixed with Client Code. Only this code (20110603) is changes as per current date. Like (YYYYMMDD).
Eveready I have to change the folder name & date and import it. Have any code to import every day with current date htm files from the current month folder. We have some multiple files and all files will stored in the Client Code based sheets. Like 5067,5252,5511. Example of the file names mentioned here. (C_MTM_CM_5067_20110603_CM.htm, C_MTM_CM_5252_20110603_CM.htm & C_MTM_CM_5511_20110603_CM.htm)
I have attached file for your reference.


I have a worksheet of data with dates in ascending order in column A, and in columns B and C there are a list of investment returns at each date for two assets:

Date Return for asset 1 Return for asset 2

I would like to create a formula that will calculate the correlation (using Excel's CORREL function) of the returns of the two assets between any two dates represented in the range. I would like the formula to refer to the start and end dates in cells d1 and e1 respectively so that I can change the dates in those two cells and have the correlation formula recalculate for the new period selected.

Can someone please help.


Hello this is my first post so thanks in advance for your help. I have a spreadsheet I am developing that requires the user to enter three variables (1) Project Start Date, (2) Project End Date, (3) Total Project Cost. I then need the Total Project cost to be distributed based on a specific 12 month cash flow profile (i.e. 5% in the first month, 7% in the second month, 3% in the third month, etc...). The % allocation by month is fixed over 12 periods, but I would like it to be applied to a project that may be longer or shorter than 12 months yet still maintain the same profile (cash flow curve shape). I have looked into Fourier Analysis, but it can't be applied in this case. Anyone know if a quick solution?


Please suggest a formula for the table below to calculate the rent.

Please note the following, while formulating.
1) Escalation of rent happens after every 2 months. ie 3rd month rent is 15% higher than the previous month.
2) Rent has to be date specific, in other words, if the rent start date is 15th March, then the rent to be charged in the month of March is for 17 days.
3) Rent escalation is date specific, in other words if the rent start date is 19th June, the escalation of rent shall happen after 2 months i.e. 19th Aug, for the month of Aug the rent shall be calculated in the following manner, 1st Aug to 18th Aug as per previous months rent from 19th Aug to 31st Aug with 15% escalation.

Hope this makes sense. I am using excel 2007.


  A B C D E F G H I J K 2 Tenant Rent Start Date Rent per month Escalation after every Escalation %             3 ABC 01-Jan-2011            5,000.00 2 months 15%             4 XYZ 19-Mar-2011            5,000.00 2 months 15%             5                       6 Rent Every Month                     7 Tenant 31-Jan-2011 28-Feb-2011 31-Mar-2011 30-Apr-2011 31-May-2011 30-Jun-2011 31-Jul-2011 31-Aug-2011 30-Sep-2011 31-Oct-2011 8 ABC                     9 XYZ                    

Excel tables to the web >> Excel Jeanie HTML 4


Hope you can help. We have sales reps that sell online advertising space that can start and finish when the customer wants. EG booking from 06/01/06 to 05/02/06 for a total of $1,000.

The accounts department needs to show the revenue in their accounting month. In this case Jan month is from 01/01/06 to 27/01/06, Feb month is 28/01/06 to 24/02/06.

So in this case some of the $1000 would be Jan revenue and some Feb revenue.

What sort of formula could I use to show what accounting month the revenue should appear in based on the start and finish date of the advertising?

i.e The sales rep can enter the contract amount and the start and finish date of the contract and the formula will work out what accounting month the revenue should appear in and show this in the appropriate month columns.



I'm interested in writing a clean monthly compounding formula that incorporates the following (I'm compounding cash flows):

* Compounding that starts on a particular month (say, for example, a construction start month).
* From the start month, compounding would be at a user defined rate for 12 mos. (e.g. 3%).
* Starting the 13th month, compounding would change to another user defined rate until the 24th month, it would change again in the 25th month and so forth.
* I want the user to be able to enter the following type of information, compounding start month, and then the rate for each of the 12 month periods.
Start: June 09
Months 1-12: 2%
Months 13-24: 3%
Months 25-26: 4%

and so on...

My thoughts inculded using a vlookup or the count function, but wanted to see if any of you guys can think of something simple and clean.

Hello everyone I'm new here but hopefully I can get some help with this question and of course be happy to help anyone that I can with my limited knowledge.

I need to show monthly invoice charges based on a bill cycle date, a service start date, and a service end date.

I will provide monthly rate for a service (variable on type of service) and will provide the start and end dates (changing for various services) as well as the bill cycle end day (the same for every account but may change from one account to the next so this being a variable in the formula would be great - could be 12th day of month or 1st day of month or whatever as it may be.) I've been able to accomplish the formula and have it work great for monthly invoices as well as total for a given time period EXCEPT the following cases:

The bill cycle does not start on the first of the month, and so the service start day() value may be < or > the billcycle day() value.

The start and end dates may be in the same month which causes an error in the calculation of the "in between months" when calculating the total.

Any ideas? Thanks.

I have a chart that is pulling data from a table with daily data. The data table starts in the middle of a month. I am charting ~ four months of data and I want the tick marks to be positioned at the first day of the month.

The problem I am having is the data table starts in the middle of the month, which is resulting in the tick marks being placed at the incorrect position on the x-axis. I tried to change the minimum value to the first day of the month but that causes the chart to plot a blank area. I have attached small sample for better clarification. Any comments are appreciated. Thanks

I want to caluclate Written down value of assets by using depreciation rate
on WDV Concept


i can't seem to find any in the forum regarding what i want to do in excel.

attached is the sample report that i want to create.

* i want the info in sheet 1 automatically populate whenever i add rows in sheet 2
* beg FTE counts the beginning number of staff hired for the month indicated
* end FTE counts the number of employees active less the number who left
* date start counts the number of employees who started/hired on the said month
* resignation/transfer/terminated counts the number of employees who left on the said month

i wanted to manage the running active employees that we have and how many left on a particular month.


I am trying to calculate the week number for a date in a month, i have a formula to calculate it from the start of a month but i need the count (ie week 1) to begin on the 18/01/2010 to the 14/03/2010 if this makes sense? Can anyone help?

Hi all,

I want to display the text: "Period Report to: December 2009" on the print out of a report I produce every month. I always publish the report at the start of the following month, so December's report will come out in January and January's in Fenruary, etc.

I know I could change the headers every month, but I always forget to do it. I have this code, but don;t know how to get it to say the previous month instead of today's date:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.RightHeader = "& Period Report to: " & Format(Now, "DD-MMM-YY")
End With
End Sub

Any help would be great.