SLN - Straight Line Depreciation

[tr2yhb]

In A I have entered the current year.

In C I have entered the month of purchase.
In D I have entered the day of purchase (1st - 31st).
In E I have entered the year of purchase.
In F I have entered the amount of the purchase.
In G I have entered the life of the assets.
In I I have entered the salvage value.

In K I have entered the following =SLN(F,I,G)

What I want to do (with a function if possible):

I want K to calculate mid-month SL depreciation.
Let say an assets was purchased on January 16 for $700, with a life of
7 years. A normal year (full year) would calculate $100 worth of
depreciation.

With mid-month depreciation, if an asset is purchased after the 15th of
the month, you start depreciating the first of the next month.

In the example above, the depreciation would be $641.67 (700/12*11).

How do I do this?

 

[Guest]

Hi,

I believe that the answer to the question that you have asked might be
something like ...

=IF(D>15,SLN(F,I,G),SLN(F,I,G)/12*11)

However, please carefully note the Excel Help on the SLN function. The SLN
function will return the straight-line-depreciation amount for an asset for
one PERIOD. The PERIOD will be the same as period that you use for the useful
life of the asset. (i.e. years->years or months->months etc.).

Are you, perhaps, trying to calculate depreciation chargable in a given
financial year for an asset that is purchased as some point during that year
and/or not held for the full financial year?

Grateful if you could please explain further, because that will *most
definitely* change the answer ;-).

Cheers, Sean.

 

[tr2yhb]

I am trying to calculate depreciation chargable in a given financial
year for an asset that is purchased as some point during that same
year.

 

[Guest]

Thanks for the clarification. If your sheet is set up as described in your
original post, with the financial year (assuming Jan-Dec) for which you want
to calculate the annual depreciation chargable for in the rows of Column A,
then the following formula should do it for you ...

=IF($E<$A,SLN($F,$I,$G),SLN($F,$I,$G)/12*IF($D>15,12-$C,12-$C+1))

Assumes also that Column G (useful life) is in years. Using the mid-month
straight-line-depreciation method and NOT taking into account the possibility
of any disposals during the year - which you may want to consider (or not!).

HTH, Sean.

 

[tr2yhb]

Thank you!

Tim
Deerfield Beach, Florida