Hi Michelle
If you had one other piece of information, the original cost of the
asset, then you could do it as follows. (you would not need columns J and K)
I have assumed an Asset value of 500 placed in C2
=IF(M$1<DATE(YEAR($D2)+$E2,MONTH($D2),DAY($D2)),$C2/($E2*$F2),"")
Copy across and down
From your example data, I can't see how a date of 01/Aug/2005 will give
4 months worth of depreciation in the year commencing June.
If you use 1500 in C2 and 01/Oct/2005 in D2, then the formula above
correctly calculates 25 per month for Jun, Jul, Aug and Sep, with
nothing in the remaining months.
--
Regards
Roger Govier
Michele wrote:
> This is the DATEDIF Formula I was using -
>
> =IF((DATEDIF(D2,31/05/2011,"m"))>(E2*12),12-((DATEDIF(D2,31/05/2011,"m"))-(E2*12)),12)
>
> Probably not much help.......
>
> Ta
> Michele
>
> "Michele" wrote:
>
>> Hi
>>
>> I'm 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's 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've 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
>>
>>
>>
>>
|
Similar Threads
