Hi
I am having a little trouble calculating some formulas in my depreciation
budget worksheet.
What I need to be able to calculate is when an asset is only depreciated for
the first 4 months of a year and ending its useful life as an asset. What is
happening now is that our Accounting program gives us the planned
depreciation for the upcoming year but not the remaining months so this
planned depreciatin could be for 12 months or 4 months. Then we have come
along and applied 12 to this to get monthly depreciation and used formulas to
put it into months etc. The total figure is still correct, but the phasing
monthly is wrong. EG, Planned Depn is $100 for asset that ends useful life
at 30 Sept. Planned depn ($100) has been split by 12, instead of 4
(June-Sept). Monthly Depn should be $25 for four months, however I have
calculated $8.33 for 12 months.
So a formula to somehow include creation date, useful life = remaining months.
Here is how the data is laid out.
D2 - Depn Start Date (01/08/2005)
E2 - Life (in years) (5)
F2 - Months (12)
J2 - Planned Depreciation
L2 - Monthly Depreciation
M1 thru to X1 are the relevant Months for the year Jun 2010 to May 2011 with
a formulas of
M2 =ROUND(IF(F2=12,L2,0),2)
N2 =ROUND(IF(M2>0,M2,IF($F2=11,$L2,0)),2)
O1 to X1 use the same formula as N1 but just looks to previous cell.
I have tried using DATEDIF formulas is extra but am having trouble to get it
to automatically go - yip that has 12 plus months remaining put 12, or yip
that is between 0 and 12 so put whatever it is, or this is negative there
should be no depreciation.
Hopefully someone can help me!! Look forward to hearing from you.
Cheers Michele