Straight line depratiation calculation..

[kikola123]

Hello friends
i have data register where i have non-current assets. I know purchase date,depretiation percentage and cost for 2016 year for this items.I need formula i h5 whic would calculate accumulated depretiation for 01/12/2016 year.Basis need to be straight line depratiation formula.Then in K5 also using this basis for depretiation calculate expanse for 2016 year.Please help it is important to me...

[AliGW]

You need to help us by filling in your expected results in column K manually - the first 20 rows of the table should be enough.

[kikola123]

This is example for one account there sum of 1589 need to be in h5 and so would be for other accounts.This is just one example how is straight line calculated.

[Torrel]

I think this should do it?

The value should never exclude the maximum value paid.

And should also show the remaining balance on the years depreciation expense if you have only half years of depreciation before the item hits its full value.

It doesn't however account for leap years, so may be off by a 0.00068 every 4 years. But I think that should be ok for the use!

[Bo_Ry]

Please try at H5

=G5*(1-(1-F5)^ROUNDUP(($F$2-E5)/365,))

[kikola123]

It works Bo_Ry but it rounds up to the year I need not to do so...

[AliGW]

Take the ROUNDUP function out:

=G5*(1-(1-F5)^($F$2-E5)/365)

[Bo_Ry]

What do you need?

Maybe round
=G5*(1-(1-F5)^ROUND(($F$2-E5)/365,))

not round
=G5*(1-(1-F5)^(($F$2-E5)/365))

[kikola123]

Yes it woorks without round... Now i need depretiation expanses for only 2016 year could you help me "not accumulated but for only 2016 year..

[Bo_Ry]

Is it 128?
=G5*((1-F5)^ROUNDDOWN(($F$2-E5)/365,)-(1-F5)^(($F$2-E5)/365))

If not then please provide correct answer and how you calculate it.

[kikola123]

No, in attached file there is explanation what i intend to do.... thanks in advanced.

[Bo_Ry]

Sorry, I don't know any of this "depretiation part of additions in days" or "straight line depretiation". I just follow your calculation from post#3

If you mean 500 and 400 from J10 and J12, I have no idea how you get that value.

What is the value of "depretiation part of additions in days" and how you calculate it?

[kikola123]

In my example in J10 and j12 there is middle year purchase (addition). and we need to calculate there depreciation until the year end. Like you helped me with your formula.But there was aslo opening residual values in 2016 this is g10 and g12 we need to add full year depretiation of this 20% of 1850 and "addition" in j10 middle year purchase.So we need to calculate also its part depretiation until the year end. But i need formula that would consider if the year is 2016 year not other years is it clear? if not i would explain it further

[Bo_Ry]

I guess

K10
=IF(YEAR(E10)=2016,G10*F10+J10*(1-(1-F10)^(($F$2-E10)/365)),G10*(1-(1-F10)^(($F$2-E10)/365)))


or "add full year depretiation of this 20% of 1850 and addition in j10"

K10
=IF(YEAR(E10)=2016,G10*F10+(20%*G10+J10)*(1-(1-F10)^(($F$2-E10)/365)),G10*(1-(1-F10)^(($F$2-E10)/365)))

[kikola123]

Thanks Bo_Ry it works first formula you are genious..