Depreciation Of Fixed Asset 


Depreciation Of Fixed Asset  Excel 
View Answers 
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 (1415/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 ?
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 (1415/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 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, ...
How to split text from one cell into multiple cells quickly and easily in Excel. This includes how to split names, ...
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 ...
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
Create Gantt Chart in Excel Easily
Easy stepbystep guide to creating a Gantt Chart in Excel. Following these steps, it should take no more than 5 m ...
Easy stepbystep guide to creating a Gantt Chart in Excel. Following these steps, it should take no more than 5 m ...
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
 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
 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
 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
 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
 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`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
Hi all,
I'm hoping that someone may have come accross a similar issue. Please find attached an extract of my fixed asset register. It is quite large due to reporting in UK and US GAAP. The problem I am faced with is we have an asset that was put into storage last year, and it has now gone back into production. The asset is not going to be impaired and the remaining life is not going to alter. Depreciation was frozen when the asset was placed in storage and should start again from now (Depriciation charge will be higher per month due to the freeze). How can I start depreciating the asset again, without the spreadsheet charging me the depreciation for the hiatus. I am trying to keep the register as neat as possible so I dont want to add another line for the same asset.
Any help would be greatly appreciated.
R's
Om
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
Jan10
Feb10
Mar10
Apr10
May10
LCD PROJECTOR 797.12 13.29 (770.54) 26.58 (13.29) (13.29) (13.29) 13.28 
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
Jan10
Feb10
Mar10
Apr10
May10
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.
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.
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?
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!
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!
As on
31/12/2006
Code Asset Valuation Pur. Date Acc. Dep
A001 Furniture 173,995 2001, 31 Dec.
if((E2D4)>=1826,C4,if(E2>D4,C4/5*(E2D4)/365,0),(E2>D4,C4/5*(E2D4)/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*(E2D4)/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.
31/12/2006
Code Asset Valuation Pur. Date Acc. Dep
A001 Furniture 173,995 2001, 31 Dec.
if((E2D4)>=1826,C4,if(E2>D4,C4/5*(E2D4)/365,0),(E2>D4,C4/5*(E2D4)/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*(E2D4)/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,A31.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,A11.5:A31.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.
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,A31.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,A11.5:A31.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(1H13)^(L18/12))
which is Purchase value * (1(1rate)^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 31Mar 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
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(1H13)^(L18/12))
which is Purchase value * (1(1rate)^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 31Mar 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
I need to create a depreciation schedule and my boss would like me to use an if statement to calculate the number of months of depreciation a particular asset has in a year. I came up with a formula, but it really only works for assets that have a full years worth of depreciation or that the asset is placed in use in the particular year. I am having trouble coming up with a way to manipulate the formula now to calculate remaining months for an asset that will have reached it's useful life during the year (the formula just keeps calculating months, it doesn't stop once useful life is reached) and it also won't give me a zero for assets not placed in use yet (it gives a negative amount, like how far away it is from being in use). I was hoping a fresh set of eyes or someone more skilled in excel than me might be able to find my mistake. I attached the spreadsheet, column g is the one in particular I need help with. Thank you!forum help for depreciation calc.xlsx
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 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.
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
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.
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.
Hi all, sorry for my bad english, i hope i can tell you my question well.
You may know, we use VDB function to calculate depreciation. My question is that it does not give the results i expected. Here is how;
lets say we have an asset, costs 25.000 USD, have 5.000 USD salvage value, and useful life is 10 periods. We want to use linear depreciation method, so the "factor" is 1.
so, easily we expect that depreciation amount should be (25.0005.000)/10= 2.000 USD for every period.
However the formula "=VDB(cost;salvage value;useful life;start period;end period;1;FALSE)" gives different amount for each period.
I can't understand the reason, how does the formula find those results?
Period Depreciation
1 TRY2.500,00
2 TRY2.250,00
3 TRY2.025,00
4 TRY1.889,29
5 TRY1.889,29
6 TRY1.889,29
7 TRY1.889,29
8 TRY1.889,29
9 TRY1.889,29
10 TRY1.889,29
Total TRY20.000
thanks in advance
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
Can anyone help? Thanks
Hello,
I have a seemingly complex issue that I cannot resolve.
I have a worksheet that I use to project depreciation expense.
Assets are placedinservice (PIS) and accumulate depreciation based off of a Fiscal Period PIS, and depreciate for a predetermined amount of time, based on “Life in Weeks”.
We use a fiscal calendar, with a 4week month, 4week month, and a 5week month structure per quarter, so the base depreciation figures are based off of weeks.
Please see attached to follow along.
Column A represents “Acquisition Value”
Column B represents “life in weeks”
Column C represents weekly depreciation amount (“weekly”)
Column D represents the when the asset is placedinservice (“Fiscal Period PIS”)
Row 1, starting at column E, represents the “number of weeks” in the fiscal period
Row 2, starting at column E, represents the “fiscal period”
What I want to do is have a single rolling formula (array?) that:
1. Checks to see if the asset should be placedinservice, therefore taking depreciation
a. I can do this, no problem, see the orange cells in row 3 (col G and H)
2. Checks to see if taking depreciation, when does it fall off?
a. I can make the depreciation “fall off,” meaning that the value, after the “life in weeks” is exhausted, it no longer takes depreciation, but I cannot base it off of Fiscal Period PIS – see green highlights in row 4, this first number should be 156.
b. Also, the value of the depreciation, in total, should be the Acq Value, which you can see does not happen. The reason for this is the “rolling summation” formula starts with column E, or Per1404, no matter when the asset is placed in service.
The problem I am having is that I cannot make the remaining weeks calculation based off of the fiscal PIS column, and the subsequent depreciation calculation is then errant if asset is placed in service in a period other than 1404.
Please help!
Thank you,
BW78
example.xlsx
The attached file should explain better. I'm trying to write formulas for line 4 and 5 but more importantly for 4.
Basically I have an asset with a value, which is set at a certain point in time. Then I need to depreciate it to 0 until the point when it increased again. As you can see I have different length of time for the depreciation to bring the value to 0.
I have the formula that will provide the value increases in line 3.
Thanks so much!
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 PAA has the Depreciation Value (per month) Jan Dec
Col AB has the Total Depreciation Value for the
preceeding 12Month period
Col AC has Total Accumulated Depreciated
(12/31/Previous Year + Current Year)
Col AD has New Book Value (=+M2AC2 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.
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 PAA has the Depreciation Value (per month) Jan Dec
Col AB has the Total Depreciation Value for the
preceeding 12Month period
Col AC has Total Accumulated Depreciated
(12/31/Previous Year + Current Year)
Col AD has New Book Value (=+M2AC2 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 PAA has the Depreciation Value (per month) Jan Dec
Col AB has the Total Depreciation Value for the
preceeding 12Month period
Col AC has Total Accumulated Depreciated
(12/31/Previous Year + Current Year)
Col AD has New Book Value (=+M2AC2 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.
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 PAA has the Depreciation Value (per month) Jan Dec
Col AB has the Total Depreciation Value for the
preceeding 12Month period
Col AC has Total Accumulated Depreciated
(12/31/Previous Year + Current Year)
Col AD has New Book Value (=+M2AC2 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 PAA has the Depreciation Value (per month) Jan Dec
Col AB has the Total Depreciation Value for the
preceeding 12Month period
Col AC has Total Accumulated Depreciated
(12/31/Previous Year + Current Year)
Col AD has New Book Value (=+M2AC2 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.
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 PAA has the Depreciation Value (per month) Jan Dec
Col AB has the Total Depreciation Value for the
preceeding 12Month period
Col AC has Total Accumulated Depreciated
(12/31/Previous Year + Current Year)
Col AD has New Book Value (=+M2AC2 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.
Hi all,
I'm trying to figure out a depreciation formula that will return the cumulative depreciation on an asset TODAY(), ie. that will recalculate each time the book is opened.
If you need more details, please let me know.
Regards,
Thomas.
I'm trying to figure out a depreciation formula that will return the cumulative depreciation on an asset TODAY(), ie. that will recalculate each time the book is opened.
If you need more details, please let me know.
Regards,
Thomas.
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 110 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
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 110 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
I am trying to make a calculator which will automatically calculate the depreciation values of vehicles based on age/mileage/purchase price.
I have been given the following figures to work with:
10% depreciation when registered
20% after 6 months
35% after 12 months
50% after 24 months
65% after 36 months
This is based on a fixed annual mileage of 6,000 miles per year. For every extra 1,000 miles depreciation is increased by 2% on purchase price.
So what I want to have is a kind of calculator where the user will input the purchase price, age (in months) and mileage, and it will calculate the depreciation value based on the conditions as above, e.g.
Value = 10,000
Mileage = 5,000
Age = 9 months
Depreciation value (output) = 7250
Please could someone give me a few tips on the best way to do this.
Thanks
I have been given the following figures to work with:
10% depreciation when registered
20% after 6 months
35% after 12 months
50% after 24 months
65% after 36 months
This is based on a fixed annual mileage of 6,000 miles per year. For every extra 1,000 miles depreciation is increased by 2% on purchase price.
So what I want to have is a kind of calculator where the user will input the purchase price, age (in months) and mileage, and it will calculate the depreciation value based on the conditions as above, e.g.
Value = 10,000
Mileage = 5,000
Age = 9 months
Depreciation value (output) = 7250
Please could someone give me a few tips on the best way to do this.
Thanks
Hey,
I am having trouble with a very complex Nested IF formula. The problem: I would like to calculate the depreciation expense for a given month of an asset, given its relevant data  cost, salv val, useful life, purch month, year, sale month, year. The "year" refers to years 1, 2, 3, 4, 5, etc. I have written an if statement that basically illustrates the idea, and wrote the excel formulas based on that code. If you can tell me where I went wrong I would greatly appreciate this (I am working on an attachment that omits the rest of the workbook that I am working on, because this is just one element):
This is first my shorthand writing of the code to show how I expect it to work. The relevant elements for this formula a Current Year (CY), Sale Year (SY), Purchase Year (PY), Current Month (CM), Sale Month (SM), Purchase Month (PM), Depreciation per month for Asset (DpM)
Calculating Depreciation Expense for the Month for a Given Asset:
If CY > SY, 0, [If the CY is greater than SY, dep exp should = 0]
If CY = SY AND CM > SM, 0 [if cy = sy, then it depends on if cm > sm or if it cm <= sm]
If CY = SY AND CM <= SM, DpM,
If CY < SY, DPM [in the case of cy < sy, the DPM formula will calculate the necessary result.
The DPM formula was based on the idea of purchases first only, but I also needed to add the idea of sales occurring randomly. So I've been trying to piece it together.
As I was reading for answers, one of the Moderators on this site stated that if the line of code took more than a few lines, then it was probably done wrong. So, my question is  what have I done wrong?
The index,match elements basically refer to the numerical value of the months and years. This problem occurs when Excel evaluates this bolded/underlined expression. The step by step will show the change
=IF(1>6,0,IF(AND(...)
to IF(False,0,IF(AND(...)
to IF(False, #N/A(IF(AND(...)
It actually evaluates the bolded/underlined cell fine, but I can't seem to decipher what the issue can be. Your help will be greatly appreciated
=IF(INDEX('RND LTA DATA'!$E$19:$E$24,MATCH($C$2,'RND LTA DATA'!$D$19:$D$24,0))>INDEX('RND LTA DATA'!$E$19:$E$24,MATCH('RND PURCH LTA ACC DEP n EXP'!F9,'RND LTA DATA'!$D$19:$D$24,0)),0,
IF(AND((INDEX('RND LTA DATA'!$E$19:$E$24,MATCH($C$2,'RND LTA DATA'!$D$19:$D$24,0))=INDEX('RND LTA DATA'!$E$19:$E$24,MATCH('RND PURCH LTA ACC DEP n EXP'!F9,'RND LTA DATA'!$D$19:$D$24,0))),INDEX('RND LTA DATA'!$C$19:$C$31,MATCH(A8,'RND LTA DATA'!$B$19:$B$31,0))>INDEX('RND LTA DATA'!$C$19:$C$31,MATCH('RND PURCH LTA ACC DEP n EXP'!E9,'RND LTA DATA'!$B$19:$B$31,0))),0,
IF(AND((INDEX('RND LTA DATA'!$E$19:$E$24,MATCH($C$2,'RND LTA DATA'!$D$19:$D$24,0))=INDEX('RND LTA DATA'!$E$19:$E$24,MATCH('RND PURCH LTA ACC DEP n EXP'!F9,'RND LTA DATA'!$D$19:$D$24,0))),INDEX('RND LTA DATA'!$C$19:$C$31,MATCH(A8,'RND LTA DATA'!$B$20:$B$31,0))>INDEX('RND LTA DATA'!$C$19:$C$31,MATCH('RND PURCH LTA ACC DEP n EXP'!E9,'RND LTA DATA'!$B$19:$B$31,0))),0,
IF(AND('RND PURCH LTA ACC DEP n EXP'!D21=$C$2,(INDEX('RND LTA DATA'!$C$19:$C$31,MATCH('RND LTA DATA'!M8,'RND LTA DATA'!$B$19:$B$31,0)))<=(INDEX('RND LTA DATA'!$C$19:$C$31,MATCH('RND DDB Dep Exp Monthly (3)'!A8,'RND LTA DATA'!$B$19:$B$31,0)))),'RND PURCH LTA ACC DEP n EXP'!G9/(13INDEX('RND LTA DATA'!$C$19:$C$31,MATCH('RND LTA DATA'!M8,'RND LTA DATA'!$B$19:$B$31,0),0)),0))))
Give me time and I will put together a worksheet that uses the data relevant to how this procedure has been going.