Depreciation Spreadsheet

Hi

I'm having a little trouble calculating some formulas in my depreciation 
budget worksheet.

What I need to be able to calculate is when an asset is only depreciated for 
the first 4 months of a year and ending its useful life as an asset.  What is 
happening now is that our Accounting program gives us the planned 
depreciation for the upcoming year but not the remaining months so this 
planned depreciatin could be for 12 months or 4 months.  Then we have come 
along and applied 12 to this to get monthly depreciation and used formulas to 
put it into months etc.  The total figure is still correct, but the phasing 
monthly is wrong.  EG, Planned Depn is $100 for asset that ends useful life 
at 30 Sept.  Planned depn ($100) has been split by 12, instead of 4 
(June-Sept).  Monthly Depn should be $25 for four months, however I have 
calculated $8.33 for 12 months.

So a formula to somehow include creation date, useful life = remaining months.

Here's how the data is laid out.

D2 - Depn Start Date (01/08/2005)
E2 - Life (in years) (5)
F2 - Months (12)
J2 - Planned Depreciation
L2 - Monthly Depreciation

M1 thru to X1 are the relevant Months for the year Jun 2010 to May 2011 with 
a formulas of

M2   =ROUND(IF(F2=12,L2,0),2)
N2    =ROUND(IF(M2>0,M2,IF($F2=11,$L2,0)),2)
O1 to X1 use the same formula as N1 but just looks to previous cell.

I've tried using DATEDIF formulas is extra but am having trouble to get it 
to automatically go - yip that has 12 plus months remaining put 12, or yip 
that is between 0 and 12 so put whatever it is, or this is negative there 
should be no depreciation.

Hopefully someone can help me!!  Look forward to hearing from you.

Cheers Michele




0
Utf
4/8/2010 7:05:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1644 Views

Similar Articles

[PageSpeed] 28

This is the DATEDIF Formula I was using - 

=IF((DATEDIF(D2,31/05/2011,"m"))>(E2*12),12-((DATEDIF(D2,31/05/2011,"m"))-(E2*12)),12)

Probably not much help.......

Ta 
Michele

"Michele" wrote:

> Hi
> 
> I'm having a little trouble calculating some formulas in my depreciation 
> budget worksheet.
> 
> What I need to be able to calculate is when an asset is only depreciated for 
> the first 4 months of a year and ending its useful life as an asset.  What is 
> happening now is that our Accounting program gives us the planned 
> depreciation for the upcoming year but not the remaining months so this 
> planned depreciatin could be for 12 months or 4 months.  Then we have come 
> along and applied 12 to this to get monthly depreciation and used formulas to 
> put it into months etc.  The total figure is still correct, but the phasing 
> monthly is wrong.  EG, Planned Depn is $100 for asset that ends useful life 
> at 30 Sept.  Planned depn ($100) has been split by 12, instead of 4 
> (June-Sept).  Monthly Depn should be $25 for four months, however I have 
> calculated $8.33 for 12 months.
> 
> So a formula to somehow include creation date, useful life = remaining months.
> 
> Here's how the data is laid out.
> 
> D2 - Depn Start Date (01/08/2005)
> E2 - Life (in years) (5)
> F2 - Months (12)
> J2 - Planned Depreciation
> L2 - Monthly Depreciation
> 
> M1 thru to X1 are the relevant Months for the year Jun 2010 to May 2011 with 
> a formulas of
> 
> M2   =ROUND(IF(F2=12,L2,0),2)
> N2    =ROUND(IF(M2>0,M2,IF($F2=11,$L2,0)),2)
> O1 to X1 use the same formula as N1 but just looks to previous cell.
> 
> I've tried using DATEDIF formulas is extra but am having trouble to get it 
> to automatically go - yip that has 12 plus months remaining put 12, or yip 
> that is between 0 and 12 so put whatever it is, or this is negative there 
> should be no depreciation.
> 
> Hopefully someone can help me!!  Look forward to hearing from you.
> 
> Cheers Michele
> 
> 
> 
> 
0
Utf
4/8/2010 7:11:01 AM
In DATEDIF(D2,31/05/2011,"m") the second "date" is 31 divided by 5 divided 
by 2011, which is .003083, or 4 minutes and 26 seconds from the start of the 
0th of January 1900.
If what you wanted was the 31st of May 2011, try 
DATEDIF(D2,DATE(2011,5,31),"m") or (less reliably as a format, as it depends 
on Windows Regional Settings in Control Panel, not in Excel) 
DATEDIF(D2,"31/05/2011","m")
--
David Biddulph


"Michele" <Michele@discussions.microsoft.com> wrote in message 
news:682899E3-FEDC-4143-83E0-18207D8B933D@microsoft.com...
> This is the DATEDIF Formula I was using -
>
> =IF((DATEDIF(D2,31/05/2011,"m"))>(E2*12),12-((DATEDIF(D2,31/05/2011,"m"))-(E2*12)),12)
>
> Probably not much help.......
>
> Ta
> Michele
>
> "Michele" wrote:
>
>> Hi
>>
>> I'm having a little trouble calculating some formulas in my depreciation
>> budget worksheet.
>>
>> What I need to be able to calculate is when an asset is only depreciated 
>> for
>> the first 4 months of a year and ending its useful life as an asset. 
>> What is
>> happening now is that our Accounting program gives us the planned
>> depreciation for the upcoming year but not the remaining months so this
>> planned depreciatin could be for 12 months or 4 months.  Then we have 
>> come
>> along and applied 12 to this to get monthly depreciation and used 
>> formulas to
>> put it into months etc.  The total figure is still correct, but the 
>> phasing
>> monthly is wrong.  EG, Planned Depn is $100 for asset that ends useful 
>> life
>> at 30 Sept.  Planned depn ($100) has been split by 12, instead of 4
>> (June-Sept).  Monthly Depn should be $25 for four months, however I have
>> calculated $8.33 for 12 months.
>>
>> So a formula to somehow include creation date, useful life = remaining 
>> months.
>>
>> Here's how the data is laid out.
>>
>> D2 - Depn Start Date (01/08/2005)
>> E2 - Life (in years) (5)
>> F2 - Months (12)
>> J2 - Planned Depreciation
>> L2 - Monthly Depreciation
>>
>> M1 thru to X1 are the relevant Months for the year Jun 2010 to May 2011 
>> with
>> a formulas of
>>
>> M2   =ROUND(IF(F2=12,L2,0),2)
>> N2    =ROUND(IF(M2>0,M2,IF($F2=11,$L2,0)),2)
>> O1 to X1 use the same formula as N1 but just looks to previous cell.
>>
>> I've tried using DATEDIF formulas is extra but am having trouble to get 
>> it
>> to automatically go - yip that has 12 plus months remaining put 12, or 
>> yip
>> that is between 0 and 12 so put whatever it is, or this is negative there
>> should be no depreciation.
>>
>> Hopefully someone can help me!!  Look forward to hearing from you.
>>
>> Cheers Michele
>>
>>
>>
>> 

0
David
4/8/2010 7:38:29 AM
Hi Michelle

If you had one other piece of information, the original cost of the 
asset, then you could do it as follows. (you would not need columns J and K)

I have assumed an Asset value of 500 placed in C2
=IF(M$1<DATE(YEAR($D2)+$E2,MONTH($D2),DAY($D2)),$C2/($E2*$F2),"")

Copy across and down

 From your example data, I can't see how a date of 01/Aug/2005 will give 
4 months worth of depreciation in the year commencing June.
If you use 1500 in C2 and 01/Oct/2005 in D2, then the formula above 
correctly calculates 25 per month for Jun, Jul, Aug and Sep, with 
nothing in the remaining months.

--
Regards
Roger Govier

Michele wrote:
> This is the DATEDIF Formula I was using - 
> 
> =IF((DATEDIF(D2,31/05/2011,"m"))>(E2*12),12-((DATEDIF(D2,31/05/2011,"m"))-(E2*12)),12)
> 
> Probably not much help.......
> 
> Ta 
> Michele
> 
> "Michele" wrote:
> 
>> Hi
>>
>> I'm having a little trouble calculating some formulas in my depreciation 
>> budget worksheet.
>>
>> What I need to be able to calculate is when an asset is only depreciated for 
>> the first 4 months of a year and ending its useful life as an asset.  What is 
>> happening now is that our Accounting program gives us the planned 
>> depreciation for the upcoming year but not the remaining months so this 
>> planned depreciatin could be for 12 months or 4 months.  Then we have come 
>> along and applied 12 to this to get monthly depreciation and used formulas to 
>> put it into months etc.  The total figure is still correct, but the phasing 
>> monthly is wrong.  EG, Planned Depn is $100 for asset that ends useful life 
>> at 30 Sept.  Planned depn ($100) has been split by 12, instead of 4 
>> (June-Sept).  Monthly Depn should be $25 for four months, however I have 
>> calculated $8.33 for 12 months.
>>
>> So a formula to somehow include creation date, useful life = remaining months.
>>
>> Here's how the data is laid out.
>>
>> D2 - Depn Start Date (01/08/2005)
>> E2 - Life (in years) (5)
>> F2 - Months (12)
>> J2 - Planned Depreciation
>> L2 - Monthly Depreciation
>>
>> M1 thru to X1 are the relevant Months for the year Jun 2010 to May 2011 with 
>> a formulas of
>>
>> M2   =ROUND(IF(F2=12,L2,0),2)
>> N2    =ROUND(IF(M2>0,M2,IF($F2=11,$L2,0)),2)
>> O1 to X1 use the same formula as N1 but just looks to previous cell.
>>
>> I've tried using DATEDIF formulas is extra but am having trouble to get it 
>> to automatically go - yip that has 12 plus months remaining put 12, or yip 
>> that is between 0 and 12 so put whatever it is, or this is negative there 
>> should be no depreciation.
>>
>> Hopefully someone can help me!!  Look forward to hearing from you.
>>
>> Cheers Michele
>>
>>
>>
>>
0
Roger
4/8/2010 7:53:02 AM
Reply:

Similar Artilces:

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

Pasting Portions of an Excel Spreadsheet
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop <a href="http://screencast.com/t/YjhhYTU5YzU">http://screencast.com/t/YjhhYTU5YzU</a> <br><br>Hello, above is a short video with my question. I'm trying to paste a portion of an excel spreadsheet into an email and retain the boxes that are in the original spreadsheet. <br><br>Thanks for your help, <br><br>Robby On 1/18/10 5:43 AM, in article 59bb12ee.-1@webcrossing.JaKIaxP2ac0, "RobbyBarbaro@officeformac.com" <Robby...

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

Can I import a Lotus 1-2-3 spreadsheet into Excel and save all fo.
I would like to import a 1-2-3 spreadsheet that contains many formulas. It would be really great if the formulas were understood by Excel and preserved during the import. Is this possible? I think it depends on your versions. I use Excel XP (2002) and when I file/Open, I get a choice of file types, including an option to select "Lotus 1-2-3 (*.wk?)" file type. I think Lotus superseded that with *.123 file types (and for all I know they may have moved on from that ... it has been a while). Lotus also has an option to save a file as .xls or .xlw file type, but again depending on...

How do I use excel spreadsheets created in Excel 2000 atwork on my
I use documents on my laptop at work which uses excel 2000, my home pc has microsoft works - so excel is more up to date. Other than using Excel Viewer, which means that I can't adapt documents, what options do I have for using the documents on both computers? hi, option 1. purchase excel for your home pc. option 2. see option 1. Regards Frank >-----Original Message----- >I use documents on my laptop at work which uses excel 2000, my home pc has >microsoft works - so excel is more up to date. Other than using Excel >Viewer, which means that I can't adapt docume...

Mirror spreadsheet?
Hi there, What I'm trying to do is work in a spreadsheet and having an exact one as a backup, like when you link cells and when you change one cell the other one change too, but in this case I need the whole spreadsheet, when I work in one the other one change, add or delete data too. It would work like a mirror hard drive. Is this posible? Thanks in advance. Rick From Microsoft's help file ----------------- Automatically save a backup copy of a workbook You can use AutoRecover to have Excel automatically save a backup copy each time you save a workbook. The backup copy provi...

Linked Spreadsheets
Is there anyway to tell if an excel spreadsheet contains links without opening each spreadsheet individually? Thank you for your help. go to Edit and look to see if the "links" selection is available or not. If available, click on it and it will show your links. >-----Original Message----- >Is there anyway to tell if an excel spreadsheet contains >links without opening each spreadsheet individually? > >Thank you for your help. >. > ...

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 to password protect WORKS spreadsheet
Does anyone know how you can prevent a spreadsheet from being openned and viewed by applying a password requirement? Use the Save as.. feature. When you type in the name of the file (or existing name is highlighted), you would see 3 buttons on the right side. The 3rd button is Options... Click on it& your doors to password protection would open ...

font of spreadsheet tabs' name
How to extend (change) font of spreadsheet tabs' name? Thank you. kemerat The size of the sheet tabs are governed by the setting for Scrollbars in Windows Display Appearance. Mine are at 18. Start there and work your way up or down. The font will increase or decrease with the size of sheet tabs. So: with Excel Application closed. Win98 as example... Start > Settings > Control Panel > Display > Appearance(WinXP Appearance>Advanced) "Item" drop down select Scrollbar Change setting to Taste Apply OK Depending upon your settings, you may have to re-boot to s...

How do I copy spreadsheets with charts?
I am constantly making workbooks with 12 spredsheets (one for each month of the year). In these spredsheets I have embedded charts. When I have done the setup for the first month I make 11 copies of the January sheet and do search replace in these ones to make them correct. Problem is - I haven't found any way to avoid having to change all datasources of the charts manually. They are all linked to the original spredsheet and not their new "mother" - the copy. I am very grateful for help in this one. Best, Helge Hi Helge, Right-click the sheet tab, select move or copy, ...

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

Linkage between spreadsheets .... link from file1 sheet3 to file 2 sheet 1
Hi. I have a password protected master spreadsheet with 10 worksheet included, I would like to create another password protected spreadshee where the viewer may only see the contents of sheet 3 from the firs spreadsheet. It is vital that the second spreadsheet is password protected. Thanks Dav -- Message posted from http://www.ExcelForum.com Hi just link the cells from this sheet. something like =[book1.xls]Sheet3'!A1 in cell a1 of your new worksheet -- Regards Frank Kabel Frankfurt, Germany "dwalley >" <<dwalley.16v79j@excelforum-nospam.com> schrieb im New...

How do I unlock specific cells in a spreadsheet that I protected? #2
I have a spradsheet that I protected and I now want to unlock some specific cells so my users can update the cells. I tried to do this by going to Format then Cells and so on, but when I clicked on the Format tab then on the Cell option, the cell option is grayed out(not active). What is causing this and is there a work around?....Tks, MM By default all cells are locked when the sheet is protected. First, you must Unprotect the sheet via Tools>Protection>Unprotect sheet. Then.......... Select the cells you wish to unlock and Format>Cells>Protection. Check "unlocked&q...

Passwording a linked spreadsheet
Hi All, I've been asked to create a series of spreadsheets that link into one master spreadsheet. Each spreadsheet will have a summary column where the users will input what they want to see on the master spreadsheet. The master spreadsheet will then only show that field. However I want users to have access to the master spreadsheet but only selected personnel can have access to all of the seperate spreadsheets. I would like to password each of these files with a different password. Only problem is that when your in the master spreadsheet and you update the links, it asks for the pas...

How to import MS Excel Spreadsheets into MS Money 2004 Deluxe
Is there a mechanism to import a MS Excel Spreadsheet into MS Money 2004? A bank will only provide the transactions in Excel spreadsheets. Any help will be appreciated. Thanks, Varun See http://umpmfaq.info/faqdb.php?q=8. "Varun Gupta" <varunsgupta@yahoo.com> wrote in message news:17d0a01c449b6$376fdd10$a301280a@phx.gbl... > Is there a mechanism to import a MS Excel Spreadsheet into > MS Money 2004? > A bank will only provide the transactions in Excel > spreadsheets. > Any help will be appreciated. ...

Asset accounts for cars and depreciation question
I have an asset account for my car so I can weigh it against money I borrowed to finance it. Over time the car will, of course, depreciate and I was wondering how others are accounting for this. Your input is greatly appreciated. Thanks, Richard Annual adjustments in value to reflect www.nadaguides.com, entered as an expense categorized as transportation. You can categorize it however you choose. I believe GAAP requires not revising the actual asset account, but creating a contra-asset account and expensing that. Management reports then include both asset and contra-asset, which ...

How to email a spreadsheet
I just installed 2007 Student and want to email a spreadsheet. I open the desired page, click the email btton in the toolbar and nothing happens. What am I missing?? Hi Herb, > I just installed 2007 Student and want to email a spreadsheet. I open the > desired page, click the email btton in the toolbar and nothing happens. What > am I missing?? Sending Email from Excel directly requires Outlook, which is not part of Office 2007 Students and teachers edition. Look at www.rondebruin.nl for alternative solutions Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Membe...

Linking two spreadsheets
Hi, I have a master document which includes training information for al employess. Currently, we have tabs for each employee (small company) which describes all of their daily/weekly tasks. The problem is that you can't edit the file if someone else has i open. We'd like to create an individual spreadsheet for each employee an still be able to display them in the master document. Is there a way t pull all the data from the individual spreadsheets into the maste document? I know you can link individual cells from multiple documents but because we don't know how big each person&#...

what is straight line depreciation and double line depreciation?
See http://en.wikipedia.org/wiki/Depreciation In article <928DC52C-D390-45C6-9847-693648A1A5DA@microsoft.com>, MISSY <MISSY@discussions.microsoft.com> wrote: "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message news:jemcgimpsey-F62312.22355624092007@msnews.microsoft.com... > See > > http://en.wikipedia.org/wiki/Depreciation > Quite a good article for wikipedia, but it misses one very important point. The original purpose of charging depreciation to the P&L was to build up a reserve in order to replace the asset when it became obsol...

How to set up spreadsheet to track replaced parts
I track parts that are replaced on our machinery. Most of these are not a problem, but there's one that has me hoping for an easier way. One part is used in 170 different places. When one is replaced, I need to note some parameters, reason for replacement, and the position code of the part. At certain points, we pull the machine in and replace all 170 in one fell swoop, including any newer replacements. To top it off, there may be different brands of these parts - all the same part, just from a different place, and that also needs to be noted. And then I need to calculate the lifespan...

Copying a macro to another spreadsheet
I have a macro (assigned to a button) within my excel spreadsheet. I want to give this macro (and button) to several my colleagues for them to insert into their spreadsheet. What is the easiest way of doing this? I right clicked on my button and selected Copy. I then loaded up one of the spreadsheets and pasted it in. Looked good. Button and Macro worked in her sheet. However, one of the "side effects" was that when she clicked the button it opened my spreadsheet on her computer. (we are on a network and our spreasdsheets are in the same directory). Obvioulsly the button I co...

Totalling non adjacent cells in a spreadsheet
Hi all I am running WXP with Excel 2003 edition. I wish to know if, and how to set up a running total for various predefined categories in a spreadsheet. I have set up my monthly expenditure with debit & credit columns, totals are fine. I list all for say, August, enter Septembers beneath with a C/F balance, each expenditure is given a category name i.e. Utilities, Housekeeping etc. Can I then select all the entries entered under say, Housekeeping, and place them into a cell as a running total, (doesn't matter where i put these results on the spreadsheet) other than manually en...

Depreciate new year transactins
Is it possible to calculate January ‘09 depreciation without closing the fixed asset module for 2008? Any issues with this? Sara, I don't believe GP will let you do this. You must close 2008 first. -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports http://www.flex-solutions.com/gpreports.html blog: www.victoriayudin.com "Sara" <Sara@discussions.microsoft.com> wrote in message news:0BED82D4-F4BD-4BBA-972D-942A9F0C7419@microsoft.com... > Is it possible to calculate January ‘09 depreciation without closing the > fixed asset module for 2008...

Inventory Depreciation
Hello, GP8.0 / SQL2000. We 'depreciate' inventory that has been dead stock for 2 years or more as a summary on the G/L. I would like to adjust the inventory value of each of those affected items in our inventory subledger. We are using moving average cost. What is the best way to accomplish that? Are there any built in tools, or do I need to write my own queries? I can easily prepare the list of dead stock items and their current cost. Is it a simple matter of applying a percentage reduction to each of these items in the IV00101 table? Or, is there more to it than that?...