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 ?
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
E2 (31/12/2006) To Date
D4 (31/12/2001) Purchase Date
C4 (17000) Value of Asset
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.
Help me which If Formula can do this.
D4 (31/12/2001) Purchase Date
C4 (17000) Value of Asset
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.
Help me which If Formula can do this.
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
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)
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 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.
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
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
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
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
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
I want the depreciation begin the same month as the begin date. But for some reason it will only recognize beginning from following month. I do not know what I have done wrong.
I have attached the file here. Thank you for the help
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.