Depreciation Spreadsheet

[Michele]

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

 

[Michele]

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

 

[David Biddulph]

In DATEDIF(D2,31/05/2011,"m") the second "date" is 31 divided by 5 divided
by 2011, which is .003083, or 4 minutes and 26 seconds from the start of the
0th of January 1900.
If what you wanted was the 31st of May 2011, try
DATEDIF(D2,DATE(2011,5,31),"m") or (less reliably as a format, as it depends
on Windows Regional Settings in Control Panel, not in Excel)
DATEDIF(D2,"31/05/2011","m")

 

[Roger Govier]

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.