SLN - Straight Line Depreciation

T

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?
 
S

Sean Connolly

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.
 
T

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.
 
S

Sean Connolly

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top