Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Depreciation Of Fixed Asset

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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 ?


Similar Excel Video Tutorials

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







Hi,

I`m new here and desperately need of help

I have 40K asset list with different Capitalization dates, the depreciation will start either in the mid month, mid qtr or mid year (determined by dep key)

The goal is to come up with a accumulated depreciation up unitl 12/28/08.
The problem I`m running into is getting the depreciation start date (due to different years)

This is what I`m doing right now:
ie. asset cap date 12/5/2004
1. extract month: Dec
2. Extrac year: 2004

then I have table that says if the Month is Dec then the depreciation should be 12/15. However, when you enter 12/15 excel automatically adds in 2009. How do I replace the 2009 with 2004 ?

Or is there an easier way of doing this ? Sorry if I confused you


I am working on a depreciation schedule in which I want the monthly depreciation of an asset to automatically calculate and, if the asset is fully depreciation, caclulate a zero or the balance to be depreciation (if less than the monthly depreciation). Please see below example. As you can see my asset is fully depreciated at the end of February but because there remains a $0.01, the formula is calculating another month in March and then reversing it in April (less the $0.01). Here's the formula I'm using. What am I doing wrong?

Column H is March, Column C is my monthly depreciation, and column E is my beginning book value:

=-IF(ABS(SUM($F2:H2))>=$E2,(SUM($F2:H2)+$E2),IF($E2=$C2,$C2,$E2)))

Purchase
Price

Monthly Depreciation

Accumulated Depreciation 12/31/2009

1/1/2010 Beginning Book Value

Jan-10

Feb-10

Mar-10

Apr-10

May-10


LCD PROJECTOR 797.12 13.29 (770.54) 26.58 (13.29) (13.29) (13.29) 13.28 -




Help with straight line depreciation over life span of asset. For example:
Asset - $10,000
Salvage - (assuming zero)
Life Span - 7 years

I have the SLN forumla entered in the appropriate cell under each year
=SLN (cost,0,life)

I do receive the appropriate depreciation amount, however, I am stuck on how to enter the life span of the asset to stop depreciating after it reaches full depreciation.

I just found this forum and read the rules, I hope I am posting correctly. Thanks for any assistance you can give.


I am looking for an asset schedule that will show the following:
Cost
Date Acquired
Date in Service
Depreciation Start Date
Annual Depreciation (MACRS)
Annual Depreciation (Book)
Retired Assets
Accumulated Depreciation
Tangible Value
Tangible Condition
Salvage Value
Asset Life
(and anything else I'm not thinking of right now)

The spreadsheet should be able to handle asset retirements (full and partial), relief of A/D, gain or loss on sale. I've got this laid out pretty well but it's a high maintenance spreadsheet subject to error. Does anyone have a suggestion on where to find a downloadable model that handles these requirements? My spreadsheet is doing the job. It's just not doing it well any time there is a deviation from the original amortization schedule or the original life of the asset.


How to calculate monthly depreciation automatically by entering the following data:

Month Purchase
Life

My Month row will be Jan 10 , Feb 10, March 10.. If I purchase an asset in March 10, the depreciation shall start calculating in March 10.. How to make it dynamic?


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.

Thanks!


I'm trying to calculate the current depreciation for an asset, but I'm having trouble setting up the formula. My format for date placed in service 01/01/2006, the acquisition cost is $500, its depreciated straight line over 5 years with no salvage. I'm trying to calculate the accumulated depreciation at 01/01/2009. I realize that this is $100 per year so my answer for my accumulated depreciation will be $300. I'm just not sure how to use the date to help me calculate this formula. Any help is appreciated.


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

if((E2-D4)>=1826,C4,if(E2>D4,C4/5*(E2-D4)/365,0),(E2>D4,C4/5*(E2-D4)/365)))
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.
C4/5*(E2-D4)/365
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.


I need some help with a formula. The VDB formulaworks great for computing "MACRS" depreciation expense for a particular year , but I need to adjust the formula to compute accumulated depreciation (that is for several years).

MACRS is a specific tax calculation for depreciation, that is based on declining balance, with a switch to straight line if it is more advantageous. Further, for the first year , MACRS in computing depreciation expense, only take one half of the annual depreciation calculation

The VDB function is (cost, salvage value, asset life, start period, end period, factor [such as double decling balance) and will provide the expense for a particular year. I initially thought if my start period was at the beginning and end period was the current period, I would get the proper accumulated depreciation - but because MACRS switches to straight line when it is more advantageous, I believe I am not getting the correct answer.

I am thinking an array formula, that sums the annual calculation would work, but cannot figure out how to write this as an array.

The formula I am using for the annual depreciation for a 5 year asset is =VDB(1000,0,5,MAX(0,A3-1.5),MIN(A3-.05),2) [Where A3 is year 3 of the five year period (A4 is year 4, etc)]. I would think the formula is:
{=sum(VDB(1000,0,5,{MAX(0,A1-1.5:A3-1.5)},{MIN(A1-.5:A3-.5},2)} but this obviously does not work.

Any help would be appreciated. I can send the file if requested.



Dear All,

I am trying to find a formula to reconcile to schedule below

Bitmap FIXED ASSETS From: 01/01/2007 Asset Purchase Disposal Depreciation Mnths Disp Cumulative deprecciation Net Book Month's Gain/ To: 31/03/10 Cat value date date value meth rate age age on disp Opening Cum. Value depr. (loss) Building O 73,700 01/03/07 01/04/10 0 D 11.4% 46 37 22,956 0 22,956 0 0 (50,744)

Cell K13 uses C13*(1-(1-H13)^(L18/12))
which is Purchase value * (1-(1-rate)^Months/12))

I derive 22956 where as table derives 22928.


Building Purchase date: 01 March 2007 Depreciation for the first year has been apportioned for the 1 months that you owned the asset. Year ending Opening value Depreciation Depreciation Closing value Business Claim as tax 31-Mar rate for year use deduction 2,007 73,700 11.4% DV 700 73,000 100% 700 2,008 73,000 11.4% DV 8,322 64,678 100% 8,322 2,009 64,678 11.4% DV 7,373 57,305 100% 7,373 2,010 57,305 11.4% DV 6,533 50,772 100% 6,533

Does anyone have formula for reducing balance which can match 22,928 which is total Deprecaition?

Kind Regards,

Biz


E2 (31/12/2006) To Date
D4 (31/12/2001) Purchase Date
C4 (17000) Value of Asset

if((E2-D4)>=1826,C4,if(E2>D4,C4/5*(E2-D4)/365,0),(E2>D4,C4/5*(E2-D4)/365)))
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.
C4/5*(E2-D4)/365
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.


Help me which If Formula can do this.


16. Depreciation

Asset Cost $70,000
Salvage Value $10,000
Life in Years 5

Which depreciation formula would you use to calculate the depreciation as the same amount each year? What would be the book value of the asset after 3 years?

Deprecation Formula _____________
Book Value after 3 years _______________


ANSWER: ? I'M USING THIS FORMULA BUT ITS WRONG SO SOMEONE HERE PLZ FIX IT FOR ME ?

=SYD(averagecost,salvagevalue,life,3)
=SLN(AVERAGECOST,SALVAGEVALUE,LIFE)


Hello,

I want to define a UDF for calculating periodic depreciation charge, as and when assets are added or depleted, for my models.

Main theme is to pass a range(s) to the function for addition and depletion of asset along with the 'life of asset' (selected via lookup function from 'Depreciatoin Category list) as argument and use array method to calculate the depreciation charge for the period.

Can anyone please guide me?

Thanks

O.


I was wondering if anyone knew a way for this to be put into practice.

The company I've started working for has an accounting system that calculates depreciation in a very unusual way. The rules it follows are as follows:

1) Depreciation is on (roughly) a straight line basis
2) Depreciation must, after the first period, be in integers each period.
3) In the first period, any decimals in depreciation charges across the whole of its useful life are posted.

This enables every item on the register to have an integer net book value.

A result of rule 2) above can lead to the system having several periods of a lower integer depreciation charge, then catching up to an integer of a higher depreciation charge.

Is there a way that I can express this in excel? At the moment, reconciliations are done for the register manually for each asset line, and as you can imagine, this takes a lot of time.

Thanks for any help anyone is able to give.


Hi all,

My problem is the following:

I'm trying to model straight line depreciation over 5 years for certain investments. This could for example be done like this:

B C D E

1 Period 1 2 3 4

2 Value at beginning 20 16 12 8
3 Depreciation 4 4 4 4
4 Value at end of period 16 12 8 4

What I am trying to do is to find a formula that can calculate depreciation if for example I bought a new asset in period 3, and in period 4. I can no longer just divide 20 over 5 and deduct 4. I could split this out over several rows, but I would like to find a way to deal with this in as few rows as possible.

My investments look something like this:


Period 1 2 3 4 5 6
Investment 10 0 20 15 20 0
Depreciation ? ? ? ? ? ?

I hope someone can help me out! Thank you all for taking time to read and think about my question!!

Kind regards,

Andreas


Hi,

I bought an asset with a life of 5 years in say January 2009. I would depreciate if by 60 months from January onwards. Simple take the value of the asset ("Value") and divide by 60 and depreciate my asset until December 2013.

How do I approach the problem if I bought the asset in say May 2009. I would still want to depreciate it until Dec 2013 with a full year's depreciation for 2009 ((Value/5/(months from May until Dec 2009)). From 2010 until Dec 2013 will be normal depreciation like above.

Example :

Asset value = $5000 bought on January 2009,
Depreciation from January 2009 until Dec 2013 = 5000/5/12,

But if Asset Value = $5,000 bought on May 2009
Is there a way to depreciate a full year's depreciation, $1000 (5000/5) monthly ie from May - Dec and subsequently equal monthly depreciation , $1,000 from Jan 2010 until Dec 2013.


Many thanks in advance.


Is it possible to have a macro note when cells are changed in column "C". "E" or "F" (after the initial setup).

If any cell in the 3 columns is changed then the macro will keep column "H" the same.

So if C10 is changed after the asset has started depreciating the H10 will remain the same for the current month and will update when the workbook date is update for the next month.

Column "C" is the asset value
Column "E" is the asset start date
Column "F" is the asset end date
Column "H" is the prior accum. depreciation amount. (this should not change if the value of the asset is adjusted or if the start or end date changes after the asset has started depreciating).

Is this possible or do we have to make manual adjustments each time this happens

Thanks
Mark


I am trying to develop a formula that would calcluate depreciation by year. I an attached file, person would input Purchase Date (month/yr), Cost, and useful life. I am assuming salvage value =0. I want a formula in cells E5 - Q5 that will calculate deprecication depending on the inputted purchase date. So if purchase date is Feb 1, 2007, the depr amt in 2007 would be (11/12 )* (100,000/12). I also want the folrmula to not exceed accumulated Depr over the cost like in cell Q5.

Can anyone help? Thanks


I don't expect to get any bites on this one, but, "Hey, it's Friday -- it
could happen!"

Just got this one dropped on my lap -- we have a spreadsheet with items that
we depreciate -- this spreadsheep has been touched/maniulated by many over
the years (I just gained access to it, since it's not doing what the Finance
folks would like it to do).

Here's what it does ...
Col H has a description of the item
Col J has In Service Date
Col L has the Life of the item (ie, 60 mos, 120 mos)
Col M has the Original Cost of the item
Col N figures the Monthly Depreciation (=M2/L2)
Col O has Total Accumulated Depreciation (from In Service
Date until 12/31/Previous Year)
Col P-AA has the Depreciation Value (per month) Jan- Dec
Col AB has the Total Depreciation Value for the
preceeding 12-Month period
Col AC has Total Accumulated Depreciated
(12/31/Previous Year + Current Year)
Col AD has New Book Value (=+M2-AC2 OR Original Cost -
Total Accumulated Value)

Here's what I need ... I need some sort of IF statement (I assume this will
be the most logical concept) that will continue to calculate the fields up
to the point where the New Book = ZERO (or until the Total Accumulated
Depreciation is EQUAL to the Original Cost)

What's is the best way to approach this?

Thanks in advance.





I don't expect to get any bites on this one, but, "Hey, it's Friday -- it
could happen!"

Just got this one dropped on my lap -- we have a spreadsheet with items that
we depreciate -- this spreadsheep has been touched/maniulated by many over
the years (I just gained access to it, since it's not doing what the Finance
folks would like it to do).

Here's what it does ...
Col H has a description of the item
Col J has In Service Date
Col L has the Life of the item (ie, 60 mos, 120 mos)
Col M has the Original Cost of the item
Col N figures the Monthly Depreciation (=M2/L2)
Col O has Total Accumulated Depreciation (from In Service
Date until 12/31/Previous Year)
Col P-AA has the Depreciation Value (per month) Jan- Dec
Col AB has the Total Depreciation Value for the
preceeding 12-Month period
Col AC has Total Accumulated Depreciated
(12/31/Previous Year + Current Year)
Col AD has New Book Value (=+M2-AC2 OR Original Cost -
Total Accumulated Value)

Here's what I need ... I need some sort of IF statement (I assume this will
be the most logical concept) that will continue to calculate the fields up
to the point where the New Book = ZERO (or until the Total Accumulated
Depreciation is EQUAL to the Original Cost)

What's is the best way to approach this?

Thanks in advance.





I don't expect to get any bites on this one, but, "Hey, it's Friday -- it
could happen!"

Just got this one dropped on my lap -- we have a spreadsheet with items that
we depreciate -- this spreadsheep has been touched/maniulated by many over
the years (I just gained access to it, since it's not doing what the Finance
folks would like it to do).

Here's what it does ...
Col H has a description of the item
Col J has In Service Date
Col L has the Life of the item (ie, 60 mos, 120 mos)
Col M has the Original Cost of the item
Col N figures the Monthly Depreciation (=M2/L2)
Col O has Total Accumulated Depreciation (from In Service
Date until 12/31/Previous Year)
Col P-AA has the Depreciation Value (per month) Jan- Dec
Col AB has the Total Depreciation Value for the
preceeding 12-Month period
Col AC has Total Accumulated Depreciated
(12/31/Previous Year + Current Year)
Col AD has New Book Value (=+M2-AC2 OR Original Cost -
Total Accumulated Value)

Here's what I need ... I need some sort of IF statement (I assume this will
be the most logical concept) that will continue to calculate the fields up
to the point where the New Book = ZERO (or until the Total Accumulated
Depreciation is EQUAL to the Original Cost)

What's is the best way to approach this?

Thanks in advance.





I'm working on a Straight Line Depreciation Spreadsheet (this was a "Home
Grown" effort -- touched by many hands, and not derived from an Excel
Template/Example)

Basically, I have the following ...
Cost (CELL M2) -- Life, in months (CELL L2) -- In-Service Date (CELL J2) --
Depreciation per month (CELL N2) -- Life/Cost)

Beginning 01/01/2005, I have an "Accumulated Depreciation Total" (CELL O2)
and the logic I want to apply is this:

IF Value in Cell O2 < Cost (CELL M2) then I want to populate CELL P2 with
the value of CELL P2 + the Monthly Depreciation (CELL N2) and continue this
on out (giveing an Accumulated Depreciation Total 12/31/06 and each
end-of-year until the value in any subsequent cell is equal to the Cost
(CELL M2).

Is there some code that I could use to manage this? For example (based on
the Life -- 60 months, 120 months, ect. -- Auto Fill the appropriate number
of cells to the right to correspond with the number of months in the Life
Cycle.

Thanks in advance for any assistance in this troubling problem.





I'm working on a Straight Line Depreciation Spreadsheet (this was a "Home
Grown" effort -- touched by many hands, and not derived from an Excel
Template/Example)

Basically, I have the following ...
Cost (CELL M2) -- Life, in months (CELL L2) -- In-Service Date (CELL J2) --
Depreciation per month (CELL N2) -- Life/Cost)

Beginning 01/01/2005, I have an "Accumulated Depreciation Total" (CELL O2)
and the logic I want to apply is this:

IF Value in Cell O2 < Cost (CELL M2) then I want to populate CELL P2 with
the value of CELL P2 + the Monthly Depreciation (CELL N2) and continue this
on out (giveing an Accumulated Depreciation Total 12/31/06 and each
end-of-year until the value in any subsequent cell is equal to the Cost
(CELL M2).

Is there some code that I could use to manage this? For example (based on
the Life -- 60 months, 120 months, ect. -- Auto Fill the appropriate number
of cells to the right to correspond with the number of months in the Life
Cycle.

Thanks in advance for any assistance in this troubling problem.





I'm working on a Straight Line Depreciation Spreadsheet (this was a "Home
Grown" effort -- touched by many hands, and not derived from an Excel
Template/Example)

Basically, I have the following ...
Cost (CELL M2) -- Life, in months (CELL L2) -- In-Service Date (CELL J2) --
Depreciation per month (CELL N2) -- Life/Cost)

Beginning 01/01/2005, I have an "Accumulated Depreciation Total" (CELL O2)
and the logic I want to apply is this:

IF Value in Cell O2 < Cost (CELL M2) then I want to populate CELL P2 with
the value of CELL P2 + the Monthly Depreciation (CELL N2) and continue this
on out (giveing an Accumulated Depreciation Total 12/31/06 and each
end-of-year until the value in any subsequent cell is equal to the Cost
(CELL M2).

Is there some code that I could use to manage this? For example (based on
the Life -- 60 months, 120 months, ect. -- Auto Fill the appropriate number
of cells to the right to correspond with the number of months in the Life
Cycle.

Thanks in advance for any assistance in this troubling problem.





I'm working on a Straight Line Depreciation Spreadsheet (this was a "Home
Grown" effort -- touched by many hands, and not derived from an Excel
Template/Example)

Basically, I have the following ...
Cost (CELL M2) -- Life, in months (CELL L2) -- In-Service Date (CELL J2) --
Depreciation per month (CELL N2) -- Life/Cost)

Beginning 01/01/2005, I have an "Accumulated Depreciation Total" (CELL O2)
and the logic I want to apply is this:

IF Value in Cell O2 < Cost (CELL M2) then I want to populate CELL P2 with
the value of CELL P2 + the Monthly Depreciation (CELL N2) and continue this
on out (giveing an Accumulated Depreciation Total 12/31/06 and each
end-of-year until the value in any subsequent cell is equal to the Cost
(CELL M2).

Is there some code that I could use to manage this? For example (based on
the Life -- 60 months, 120 months, ect. -- Auto Fill the appropriate number
of cells to the right to correspond with the number of months in the Life
Cycle.

Thanks in advance for any assistance in this troubling problem.