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. 


0
7/7/2006 8:08:31 PM
excel 39880 articles. 2 followers. Follow

1 Replies
305 Views

Similar Articles

[PageSpeed] 51

Hello doctorjones,

"...that will continue to calculate the fields up > to the point where the 
New Book = ZERO".    Which fields are you referring to?   the New Book Value 
in Column AD?  other field(s)?

Art.


<doctorjones_md@yahoo.com> wrote in message 
news:edkzfEgoGHA.4152@TK2MSFTNGP04.phx.gbl...
>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.
> 


0
artmacneil (88)
7/9/2006 4:44:40 PM
Reply:

Similar Artilces:

'formula 1' appears in Pivot Table after grouping
Hi, when I use 'Group Selection' inside a pivot table, the words 'formula 1' suddenly appear inside the data I am trying to group. and then if I am trying to group say 10 rows of data, it only highlights 9 of them plus the row beneath with the word 'formula 1' as the 10.. and the 10 data value appears in the row below, but is excluded from the group. Any guidance please? Thank you. What is in the field you are trying to group and what type of field is it - row, column or data field? Usually when you apply the Group Selection command the word Group1 appears ...

Formula help #38
These are the two formulas I'm using. Do you see any errors that pop out at you? If not, can you help me figure out a way to make this work? Column F is gender so the only options are M or F. Column G is ethnicity so the only options are A,B,C,H,N,M,and O. Column T is the level of placement, so the options are 1,2,3,4, or 5. The formulas are intended to exclude data without these criteria in a total count. =SUMPRODUCT(--(F6:F232="F"),--(G6:G232="C"),-- (T6:T232="1")) =SUMPRODUCT(--(G6:G232="C"),--(T6:T232="1")) Column G is Et...

Easy Formula?
I Have A Column A & B I Have A Formula At Column C =sum(a2*b2)+(a3*b3)+(a4*b4) Answer Is 31 I Want To Multiply A1 And B1 Up To The Last Range Of Data Add The Total Product The Problem Is: If Have A Lot Of Data In Column A & B, The Formula Is So Very Long Theres Any Easy Formula? A B 3 5 2 7 2 2 -- xtrmhyper ------------------------------------------------------------------------ xtrmhyper's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23851 View this thread: http://www.excelforum.com/showthread.php?threadid=3908...

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

Need help with formulas and functions
I am building a testing chart for equipment. I have 5 tests on each item and have to put in their numerical value. I need to average the values dropping the highest and lowest values as sometimes the values can be a misread of the test equipment. The question is, is ther are formula, argument or function that can be as follows: =AVERAGE (C1:F1) ignore =MIN(C1:F1) and =MAX(C1:F1) I have been looking through the help built in to Excel and have not had success in finding the answer. greyhwk Greyhwk, If your data were in A1:A5 =(SUM(A1:A5)-MIN(A1:A5)-MAX(A1:A5))/(COUNT(A1:A5)-2) Dan E &...

grade sheet formula
im a teenager trying to do a project for school on excel. i need to re-do my grade history sheet on excel. this is fine, i've done it all, and i'm fine on that part. where i'm lost at is the points part. the thing is for every class i have a grade. for every grade there are points (A=4, B=3, C=2, D=1, F=0). that and also, if one of my classes is honors gifted, a bit higher in level, you get an extra point (A=5, B=4, C=3, D=2, F=1). and if you're in an AP class, advanced placement, it's 2 more points (A=6, etc.). what i need to know is a formula so that whatever grade...

Formula Excel
Wondering if someone would be able to help me with this: A B C Year Year Service 01-Sep-89 30-Jun-10 = 21 Years Teachers From September 1, 1989 to June 30, 1990 = 1 Year of Service. I know it only 10 months, but that is their year. Is there a formula that could caculate the number of years from September 1, 1989 to June 30, 2010. -- Newfie The general formula for years between dates is =Year(latest date) - Year(earlier date) and 2010-1989 = 21 years. So that might work for most cases? A complication arises if they begin, but do...

Help with formula for IF ANY
Is there an ANY formula for an excel Cell?? i want to set the conditional formatting for a range of cells in a excel 2007 column to the text "OFF" currently i have the formatting for each cell in the range to highlight if the value contains "OFF" i would like to have the entire range of cells highlighted if ANY of the cells in the range contain "OFF" Am i nuts or is this possible?? Thanks for your help Select your range (say C7:e9) Then use formula is: =countif($c$7:$e$9,"off")>0 And apply the format you like. Barry A&P wrote: > >...

Depreciations
We are having a problem with the fixed asset depreciation on some assets. The auditors have found that the depreciation on these assets is calculating a larger amount than expected. The items involved all are Buildings and they are have an acquisition date that precedes our implementing the system. They were loaded into the system on 7/7/2000. How can we fix this? Not enough info Andrea to advise a fix. If you can give us all the particulars of the asset, the depreciation settings, current depreciation amounts, etc., maybe we can help. The more detail the better. Frank Hamelly MCP...

Formula #16
How do I add a colum of numbers? Thanks One way: =SUM(A:A) In article <8BB32BDC-BB5B-427F-B548-10F9A1336110@microsoft.com>, turner <finance@turnerleasing.com.au> wrote: > How do I add a colum of numbers? > Thanks > turner =SUM(A1:A10) entered in B1 =SUM(A:A) entered in B1 Select the range of numbers then click on the AutoSum button on the Standard Toolbar. Looks like a sideways M Gord Dibben Excel MVP On Sun, 21 Dec 2003 13:41:05 -0800, turner <finance@turnerleasing.com.au> wrote: >How do I add a colum of numbers? >Thanks >From: =?Utf-8...

Label work...formula requested
I have a label work which will be finally imposed (mail merge method) in Corel DRAW, an illustration software. The data is available as an Excel sheet. Each label contains ONLY four lines (Col A, Col B, Col C, Col D) I have to reposition these fields in columns E,F,G and H based on the number of empty fields. For this I have used =CountA(range) function and put the same in Col I Beyond this I am hitting blank walls. The requirement is as under. If the record (row) has ALL the fields populated, then E1,F1,G1 and H1 should be be replaced with A1, B1, C1 and D1 respectively.. If the recor...

Pasting a Formula into a new column or worksheet, but so that it doesn't change
Hello - When I copy and paste a formula into a new column, Excel assumes that I wanted to use references to different columns. That is, each time that I want to copy and paste a formula into a new column, or sheet, I have to either first make it an absolute reference---or I have to paste it into Word, and then paste it into Excel to keep it as the same formula. Does anyone know if there is an easier way to paste a formula so that the reference doesn't change, and the formula thus stays the same? Thanks!! One way is to copy it *from the formula bar*! Click in the cell containing the fo...

Formula in MSP
Hi, I am using MSP 2003. I want to know about all the customizing formulas used in MS project with examples. It is there any website, how the formula we can use in ms project?. Thanks In the Project Help screen, type in "formula" and you should get an option for all formulas in custom fields. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > Hi, > I am using MSP 2003. I want to know about all the customizing > formulas > used in MS project with examples. It is there any website, how the > formula we > can use in ms project?. ...

Formula in an Excel comment
If I have an item identifier in an Excel cell I would like to be able to see a summary of info about the item as a comment. As there will be many such identifiers possible in many rows, use of a formula in a comment to build the summary info from other cells/worksheets would be a big help. Even better would be if I could copy the comment from cell to cell so that the formula addressing automatically changes to suit the identifier in the new cell. ...

copying formulas from worksheet to worksheet
I need to copy a row of cells from one worksheet to a column of another worksheet. I want it to reference the cells listed in the row of one worksheet to the column of the other worksheet. I found out how to do the reverse - =offset('worksheet name'!$a$1,0,row(1:1)-1) - but it doesn't work for copying rows to columns? Can you help? Think the way that your Q was phrased, an adaptation of the formula you posted should work Assume you have in Sheet1,in B2:E2, the numbers: 1,2,3,4 In Sheet2 ------------ Put in the start-cell, say B2? : =OFFSET(Sheet1!$B$2,,ROWS($A$1:A1)-1)...

Round formula
How can I round 980 to 1,000? With 980 in A1, you can use this: =3DROUNDUP(A1,-3) Hope this helps. Pete On Apr 1, 3:07=A0pm, Khaled <khaled...@gmail.com> wrote: > How can I round 980 to 1,000? It did help, thanks Pete. On Apr 1, 4:23=A0pm, Pete_UK <pashu...@auditel.net> wrote: > With 980 in A1, you can use this: > > =3DROUNDUP(A1,-3) > > Hope this helps. > > Pete > > On Apr 1, 3:07=A0pm, Khaled <khaled...@gmail.com> wrote: > > > > > How can I round 980 to 1,000?- Hide quoted text - > > -...

printing formulas #2
I am trying to print the formulas that I created in a worksheet. Does anyone know how to do that? Alfonso, Flip to formula view (Ctrl-` - left next toe the keyboard numbers), and then just print it. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Alfonso" <anonymous@discussions.microsoft.com> wrote in message news:9E94F456-31DC-4C50-9643-5C5AFA95E95E@microsoft.com... > I am trying to print the formulas that I created in a worksheet. Does anyone know how to do that? Bob, Which ...

Date driven formula/worksheet
Hello. I've tried this a couple of times in the past and have been unsuccessful and was hoping someone out there has already wrote a formula for it. The question is regarding a date driven schedule. For instance, a lease might be from 11/1/05 thru 10/31/08. I have a full schedule with columns for the periods ending 06/30/05, 06/30/06, 06/30/07 and so on (June 30 year-ends). Is there a formula I could use to calculate the amount of months that will fall within these buckets? I'll add to this throughout the year and was hoping to just put in the time frame and it would automatical...

Help with Formula #12
Hi, I'm after help with the following formula. I have a number in Cell A1, I would like a formula in Cell A2, that will put a number depending on what the number in Cell A1 is into Cell 2. If the number in Cell A1 is 1 to 20 then Cell A2 should show 1 If the number is Cell A1 is 21 to 26 then Cell A2 should show 2 and If the number is Cell A1 is 27 to 32 then Cell A2 should show 3. Many Thanks something like =if(a1>26,3,if(a1>20,2,if(a1>0,1,""))) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "grobertson" <grobertson@discussions.microsoft.c...

in a macro how to make a part of the formula a variable
I am trying to copy a cell from one file into another file in a macro. There are hundreds of files all linking to the same cell so I thought I would set the formula up so that is has a variable for the filename but I can't figure out the syntax. The formula should link to cell $r$16 in worksheet "monthly" in the changing file name and the variable I used for the changing file name is budget_file. The following didn't work. Can someone tell me how to do this? ActiveCell.FormulaR1C1 = "=" & budget_file & 'monthly'!$r$16&qu...

Formula help #19
I have a production report by company that includes a column for (# of creations) and a column that is for the date. The date goes by month so that you have 1/1/2005 - 1/31/2005 and so on for each month for the whole year of 2005. The NEW report that I am working on will be merged with this one. I need to create a weekly report for creation by date - example Production Report for week of 1/1/2005 and this will include only five days. I have a multitude of companies that will be on this report so when viewed production will know exactly how many creations were made for each company that we...

MACRS Depreciation
For companies that have assets with MACRS depreciation methods, does anybody know the averaging convention that you would combine with one of the double declining methods to make the schedule work? -- Charles Allen, MVP Charles: I've always used Half Year convention to simulate MACRS in GP. It works. Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com That's what I've done as well. Works fine. Mark On Sep 11, 12:05=A0pm, "Frank Hamelly, MCP-GP, MCT, MVP" <fhame...@eastcoast-dynamics.com> wrote: > Charles: > > I've a...

How do I read tab numbers into formula?
I want to use the tab number to automatically create a date. For example, I want to use tab number 1 to create a date in a cell on the same page. 1 for the 1st, 10 for the 10th, etc. How do I read the tab number into my formula? take a look here: http://www.mcgimpsey.com/excel/formulae/cell_function.html In article <2D0BE3A6-FD87-4A13-847F-BCF42917430B@microsoft.com>, "Gregg" <Gregg@discussions.microsoft.com> wrote: > I want to use the tab number to automatically create a date. For example, I > want to use tab number 1 to create a date in a cell on the...

Depreciation
I am not too familiar with Great Plains. One of my clients told me that when he closes out book depreciation for the year, he must also close out tax depreciation. Is this true? If not, how does it work? I have used several other systems (especially macola) and you can close one book without closing the other. Thanks. You do not have to close all books at the same time. -- Charles Allen, MVP "Neo" wrote: > I am not too familiar with Great Plains. One of my clients told me that when > he closes out book depreciation for the year, he must also close out tax &g...

formula #15
Is there a way to add a formula that has a perpetual number in the formula, so that every time the excel file is opened, it generates a new number in sequence. Use the Workbook_Open event: right-click the Excel LOGO near the file menu, select View Code, then enter this: Private Sub Workbook_Open() Sheets(1).Range("C3").Value = Sheets(1).Range("C3").Value + 1 End Sub ========change C3 to the appropriate cell. "Brian" <anonymous@discussions.microsoft.com> wrote in message news:0db701c3bf46$f1596750$a501280a@phx.gbl... > Is there a way to add a formu...