If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Depreciation Formula
I don't expect to get any bites on this one, but, "Hey, it's Friday -- it
could happen!" Just got this one dropped on my lap -- we have a spreadsheet with items that we depreciate -- this spreadsheep has been touched/maniulated by many over the years (I just gained access to it, since it's not doing what the Finance folks would like it to do). Here's what it does ... Col H has a description of the item Col J has In Service Date Col L has the Life of the item (ie, 60 mos, 120 mos) Col M has the Original Cost of the item Col N figures the Monthly Depreciation (=M2/L2) Col O has Total Accumulated Depreciation (from In Service Date until 12/31/Previous Year) Col P-AA has the Depreciation Value (per month) Jan- Dec Col AB has the Total Depreciation Value for the preceeding 12-Month period Col AC has Total Accumulated Depreciated (12/31/Previous Year + Current Year) Col AD has New Book Value (=+M2-AC2 OR Original Cost - Total Accumulated Value) Here's what I need ... I need some sort of IF statement (I assume this will be the most logical concept) that will continue to calculate the fields up to the point where the New Book = ZERO (or until the Total Accumulated Depreciation is EQUAL to the Original Cost) What's is the best way to approach this? Thanks in advance. |
#2
|
|||
|
|||
Depreciation Formula
You will need to surround each calculation with an If statement, as in:
N3: =if(ad2=0,"",m3/l3) Repeat this for each cell where you want it to be blank once the asset has been fully depreciated. You will have to create enough rows for the longest life of any asset (eg 10 years in your example). Once you've created the first row, you should be able to simply copy down all the columns. -- Regards, Fred wrote in message ... I don't expect to get any bites on this one, but, "Hey, it's Friday -- it could happen!" Just got this one dropped on my lap -- we have a spreadsheet with items that we depreciate -- this spreadsheep has been touched/maniulated by many over the years (I just gained access to it, since it's not doing what the Finance folks would like it to do). Here's what it does ... Col H has a description of the item Col J has In Service Date Col L has the Life of the item (ie, 60 mos, 120 mos) Col M has the Original Cost of the item Col N figures the Monthly Depreciation (=M2/L2) Col O has Total Accumulated Depreciation (from In Service Date until 12/31/Previous Year) Col P-AA has the Depreciation Value (per month) Jan- Dec Col AB has the Total Depreciation Value for the preceeding 12-Month period Col AC has Total Accumulated Depreciated (12/31/Previous Year + Current Year) Col AD has New Book Value (=+M2-AC2 OR Original Cost - Total Accumulated Value) Here's what I need ... I need some sort of IF statement (I assume this will be the most logical concept) that will continue to calculate the fields up to the point where the New Book = ZERO (or until the Total Accumulated Depreciation is EQUAL to the Original Cost) What's is the best way to approach this? Thanks in advance. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Locking portions of a formula | tiggatattoo | Worksheet Functions | 2 | June 5th, 2006 04:51 PM |
assign formula to another cell | Dannycol | Worksheet Functions | 3 | May 12th, 2006 09:46 PM |
Formula checking multiple worksheets | sonic-the-mouse | Worksheet Functions | 11 | June 6th, 2005 06:37 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 11:26 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 08:51 PM |