Excel 2007 : Straight line depreciation

I am attaching a copy of the worksheet I am trying to develop for the depreciation. I am stuck on columns I-R, in carrying out the depreciation per year. I am wondering if there is a formula to tell the cell to fill the appropriate depreciation expense in each year.

Thank you so much for any help you can give me!

I got you started with formulas for each year's depreciation.

With a little time I can provide formulas for the columns for Past Years' Depreciation and Current Year's Depreciation.

The one that needs a little more thought it "2002 and prior years" which is a little bit more complicated than just figuring it for one year.

None of these are that hard but I'm a little tight on time at the moment. If nobody else jumps in to help then I'll take another look later.

THANK YOU SO MUCH! I have worked for many hours trying to come up with the correct formula for this outcome. If there is a formula for "2002 and prior years" that would be amazing but I can use what you have given me so far to hopefully complete my project.

Have a wonderful day

Here is the formula for 2002 & Prior Years. Put this in I3, and copy down as many rows as desired

Please Login or Register  to view this content.

If you're trying to deconstruct the other formulas, I used a technique to document what I was doing that might not be obvious. The function N will return 0 if you give it a string, so adding

+N("comment")

to any result has no effect on the outcome. It's a sneaky way to include comments.

I was pulled off this project and was able to get back to it today. I inserted the above formula for the prior years and it would work PERFECTLY!!!! Your help is very much appreciated.

Thank you!

Hi 6StringJazzer,

Thanks for posting these formulas. This came in handy to create an awesome Depreciation schedule. One thing I did notice, I have assets that are older than their expected life on the schedule and the result of dragging this code down the rows created prior years depreciation greater than their original cost.

Just as an FYI for anyone that uses this formula going forward, I added a simple IF statement at the end to check if the total depreciation is greater than the original cost. If so, then the original cost will be placed in the cell.

Please Login or Register  to view this content.

Hope this helps anyone.