Accumulate Amortized Expenditures by month

I need some help with an excel sheet and you guys may be the ones to
help.

I have a list of capital expenditures that I want to amortize over X
months.

Lets say office equipment is $10,000 and we are amortizing it over 12
months starting 1/1/11. That gives us a monthly cost of $833.33


Total capital expenditures by month
1/1/11 - $833.33
2/1/11 - $833.33
3/1/11 - $833.33
4/1/11 - $833.33
5/1/11 - $833.33
6/1/11 - $833.33
7/1/11 - $833.33
8/1/11 - $833.33
9/1/11 - $833.33
10/1/11 - $833.33
11/1/11 - $833.33
12/1/11 - $833.33

So that is just one expense. No lets say in June we buy more
computers. Again they cost 10,000 and we are paying it off over 12
months.


Now my matrix becomes this....

Total capital expenditures by month
1/1/11 - $833.33
2/1/11 - $833.33
3/1/11 - $833.33
4/1/11 - $833.33
5/1/11 - $833.33
6/1/11 - $833.33 + $833.33
7/1/11 - $833.33 + $833.33
8/1/11 - $833.33 + $833.33
9/1/11 - $833.33 + $833.33
10/1/11 - $833.33 + $833.33
11/1/11 - $833.33 + $833.33
12/1/11 - $833.33 + $833.33
1/1/12 - $833.33 + $833.33
2/1/12 - $833.33 + $833.33
3/1/12 - $833.33 + $833.33
4/1/12 - $833.33 + $833.33
5/1/12 - $833.33 + $833.33

This would continue for each item we add to the list of expenditures.

I need to create an excel sheet that will accumulate totals for each
month.

What is the best way to accomplish this?
0
5/18/2011 2:06:08 AM
excel 39880 articles. 2 followers. Follow

2 Replies
282 Views

Similar Articles

[PageSpeed] 23

One way...

List dates in ColA, List expenditures in in dividual cols starting in 
ColC.

ColA will run continuous for any given number of years, 12 rows per 
year.

ColC:Col? will contain amounts for the expenditures as applied to each 
month.

ColB will total ColC:Col?

Rows(1:2) are header rows where you can enter a description for the 
expenditure in Row1, and the cost in Row2.

The formula for cells under each expenditure should only be entered in 
rows that relate to the amortization period, to keep auto-calc overhead 
to a minimum.

  Example for ColC, Rows(3:14):  =C$2/12
  Example for ColD, starting in Jun: Rows(8:19):  =D$2/12

  Optionally, you could give C$2 a Column-relative, Row-absolute
  defined name whereafter the formula will be the same for all cols.

    Select C2 and enter the following in the defined name dialog...
    Name:  'sheet name'!Exp_Amount
    RefersTo:  =C$2

    Cell formula for all cells:  =Exp_Amount/12

Result:
  A data table showing individual expenditure amounts for each month, 
and totals for months with multiple expenditure payments. Unused 
(non-current) cols/rows can be hidden if you don't want to see them, 
leaving only 'active' payments visible.

HTH

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
5/18/2011 2:35:45 AM
BTW...

Your example for the first 5 months of 2012 should be blank in the 
first column, showing only the 2nd expenditure in the 2nd column.

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
5/18/2011 2:37:50 AM
Reply:

Similar Artilces:

ACCUMULATE
HOW DO YOU MAKE A LINKED CELL ACCUMULATE Take a look here: http://www.mcgimpsey.com/excel/accumulator.html In article <036501c389e9$860585c0$a401280a@phx.gbl>, "SPARKY" <ROUGHNLUMPY@AOL.COM> wrote: > HOW DO YOU MAKE A LINKED CELL ACCUMULATE this does not work.. adds every number in the colum. ex. =c8 + c32 whereas c8 is 100 c32 is 100 until you input another # in the c colum. >-----Original Message----- >HOW DO YOU MAKE A LINKED CELL ACCUMULATE >. > ...

update month itervals automatically
If say something was done in May-04 and it's not due again for another 15 monthes how do I get excel to due the math and then when it get's done in 15 monthes exel will do the math when I put the date in 4/05/2004 15 4/08/2005 you could use EDATE a1 = start date a2 = months a3 = =EDATE(A1,A2) SEE NOTES ON EDATE BELOW If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. R Pete -- (][ This Email has been scanned by Norton AntiVirus. ][) "Odart" <Odart@discussions.microsoft.com> wrote ...

4 month old messages and connectors in queue
I did some stuff to stop spam and relaying. Turned off ndr, only accepted messages for users in AD, reject messages with no subject. This was done back in April. I cleared out the queues of all that junk. I visit a few months later and some of the junk is back in the queues. Now here it is in August and junk from April 14 is showing up again. What does it take to delete old messages/connectors and get them to stay deleted? I realize that connectors are created when the server needs to send to a domain but how is it picking up old junk mail that I deleted more than once? Jim B ...

Counting by month
Hi everyone...my employees are required to do 2 safety inspections each month and if they meet that requirement, at the end of the quarter, they get a small bonus. I have a table where their inspection information goes. It contains empname, area they inspected, date of inspection and some other info not pertinent to this issue. I have several different reports based on queries where I can look at an individual empl and see what areas they inspected and see that they did both inspections for a particular month OR I have one where it shows a list of all empl and gives me just a count of inspec...

Automated Email Through Outlook On The 1st of Ever Month
Hello All, At my place of employment we are trying to send an automated email messaged on the first of each month to a Mailing List of employees in our Exchange 2000 Server to visit a local web address to submit their votes for "Employee of the Month". We've got our web form working great but we need to setup the HR Admin's machine (Windows 2000 Pro, Outlook 2000) to automatically send a message on the first of each month. Is this at all possible, if its not could you please suggest a valid solution. Thanks Roger See http://www.outlookcode.com/d/forms/skedrpt.ht...

disc date for next month
We want to be able to choose a discount date for the next month the discount is allowed. Our terms are 2% 10th net 15th. We can choose the due date for the 15th of the next month, but we can't choose the discount date as the 10th of the next month. All the invoices entered into the system from the 1st thru the 10th calculate the discount for the current month instead of the next month. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the me...

Language for month
I'm making a database to track a my activities in Access 2007. For every activity I have fields for date, description and duration. I have also set up a report, grouped by month. The name of the month + the year is the heading for every group. However, the name of the month is written in Norwegian. I would like this to be in English. That is, "October" instead of "Oktober". Is there any way I could do this? A label can be translated to any language that the keyboard and your copy of Windows supports. As to the data, itself, you'd need to translate that to the...

Autofill between dates in Fiscal Month
I am trying to autofill a column in a spreadsheet based on the dates in a fiscal month. Basically, I want the user to enter the starting and ending dates of the fiscal month, then have the dates autofill on a seperate spreadsheet. The problem is that certain months do not fall in a cosecutive range. For example, June starts on May 29 (sun) and ends on Sat June 25th. I want the autofill to start with 29 and go to 31 then start up with 1 and fill to 25, any ideas? This could be probably be done with formulas, but the specified start and end dates must remain available for that sheet, or the f...

accumulating notes
Hello, I am working on a productivity sheet that concatonates (vertically), 4 lines of notes at the top of a single page, from the horizontal notes below. I want the notes at the top only to show the last row of notes, each time a new set of notes is entered. Ex. (notes at top) Jim Miller 555-4897 30 day lock - exp date 11/22/03 250 hit for cash out refi sent confirm to 555-4855 Ex. (cumulative notes) Jan Smithe 548-7878 548 2256 30 day... Mark Rich 145-4565 fax 859-8686 60 d... Jim Miller 555-4897 f 555-4855 30 day lo.....(etc.) Anyone know how this is done...I'm stumpe...

Nee Help Customizing a 12 month sales report
I have this 12 month unit sales report that is helpful in my business. It deos great job but would be much more helpful if i could have option to see few more fields Here is a link to the report file http://www.centralfoodandliquor.com/files/Memorized-12%20Month%20Unit%20Sales.qrp I would like to add the option to add following fields /columns Price Cost Margin Any help would be appreciated Robert wojak@usa.com Edit the report with notepad and join the ITEM table to ViewItemMovementHistory so it reads: TablesQueried = "FROM ViewItemMovementHistory LEFT JOIN Department...

Computer
See how you can earn thousands per day without ever picking up a phone. This opportunity is exploding right now. Work 1 on 1 with top earner. Get the details here www.0to20k.com ...

How to calculate a pay-back period by the accumulated cash-flow automatically?
Hello How to calculate a pay-back period by the accumulated cash-flow automatically? How to count number of negative values in a row range? How to count ratio of a first positive value to module of the last negative value automatically? Questions 1 and 2 are related: if you calculate payback by counting months where cumulative cash flow is less than zero, you can use this formula to count negative values in a range: =COUNTIF(A1:L1,"<0") .... where months 1 - 12 are in A1:L1. You may need to extend this range if your timeline requires it. Also, add 1 to this figure to refle...

Amortizing Mortgage Incorrectly
Hello, Money 2005. I have a mortgage entered in Money which is about two years old. Somewhere along the way (mid 2006), it began to amortize incorrectly. When I check my monthly statement from the mortgage company, the principal and interest allocations were different. I went back with my statements and started to manually reallocate the payments. The first was off by some amount I cant remember (not more than $.50 or so). When I adjusted the first one, I received the following message: In the [Mortgage Company Name] loan account, there are loan payments following the one you are chan...

Select Month, get the dates
Hi, I want to be able to select the month from the drop down list and once the month is selected, I want the first date cell to change itself to the first date of the month selected. For rest of the date cells I have just used the formula "First date cell + 1" any help will be appreciated!! Thanks a lot in advance. P.S. - Sorry for posting this in two forums. I realized later that this one is probably the right place to post it. Thanks. Assuming you select the month by giving a number 1-12, the formual is: (assuming year 2010) =DATE(2010,A2,1) Or, if the d...

Convert X-Axis Scale from Day/Month/Year Options to Actual Numbers
1) I have some charts where the X-Axis scale is calibrated by entering dates, and minor/major tick marks are in whole days/months/years. 2) I have similar charts where the X-Axis is calibrated using numbers that represent dates (i.e. 39287 for 7/24/07) and I can use numbers with multiple decimal places to set the major minor tick marks. How can I convert (1) to (2)? Chart (1) is difficult to use because the dates never line up as I want (with a specified beginning and ending date). Chart (2) does allow me to set a specified date at each end of the X-Axis. The first axis sounds like a...

Bond Premium Amortization
Does MSM amortize bond premium or discount? LCS In microsoft.public.money, Leonard Schwab wrote: >Does MSM amortize bond premium or discount? No. You can use a return of principal transaction to account for amortization of a premium, but you can't do a negative return of capital to account for accretion for discounts below par. -- Michael Gordon MVP "Leonard Schwab" <lcs510@earthlink.net> wrote in message news:_lRpb.8110$qh2.2612@newsread4.news.pas.earthlink.net... > Does MSM amortize bond premium or discount? > > LCS > > ...

Months #2
Hi, Is there a way (formula or whatever) to have the 12 months instead of typing them. I used Jan in cell B2 and Feb in cell C2, selected both cells and then used the mouse to copy them to the range D2:M2. Some of the months were Ok but not all of them? Can any one help? Khalil Handal You should be able to.... Select cells B2:C2 Click and hold on the black box in the lower right corner of C2 Then drag across to the right to fill in more months. What values do you get when you do that? *********** Regards, Ron XL2002, WinXP "Khalil Handal" wrote: > Hi, > Is there ...

how do i show overcrowding over a 3 month period in excel in a gr
trying to do a spreadsheet but i just cant get to grips need graph to indicate when the overcrowding happend on dates On Fri, 30 May 2008, in microsoft.public.excel.charting, new user <new@user.?.microsoft.com.invalid> said: >trying to do a spreadsheet but i just cant get to grips need graph to >indicate when the overcrowding happend on dates Let's start with what you've got, which is the data. You will have at least two columns (or rows) of numbers, of which one is the dates. What is/are the other/s? How many numbers do you have? Can you show them to us? -- Del Co...

Accumulate a value
Hi!! I need to accumulate a value in receipt.xml. How do it? Regards Cesar hi Cesar, what you mean by accumulate value??? where you want to print?? in line items or in footer of the receipt. Please confirm so we can help you out in this. Akber "Cesar" wrote: > Hi!! > > I need to accumulate a value in receipt.xml. > How do it? > > Regards > > > Cesar ...

Formatting Calendar the same for each month
I've created a monthly calendar in Publisher and now want to apply all the formatting changes that I made to th first month's calendar to all the other 11 months. I've tried all the help files and can't find anythiing that will help. Help! After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Gromit <anonymous@discussions.microsoft.com>... > I've created a monthly calendar in Publisher and now want to apply > all the formatting changes that I made to the first month's calendar > to all the other 11 months. I've tried all the h...

Display Month
How do I format excel to just show the current month. I know that TODAY() shows todays date dd/mm/yy, so how do I pick out just the month? I would also like it to display the actual month such as September rather than 09. -- Christine Wilso ------------------------------------------------------------------------ Christine Wilso's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10429 View this thread: http://www.excelforum.com/showthread.php?threadid=264148 Hi, The number of current month, you can get by: =MONTH(TODAY()) and the name of the current month by...

Adding separate accumulators for multiple cells
I've learned to add an accumulator to multiple cells using the code on http://www.mcgimpsey.com/excel/accumulator.html Private Sub Worksheet_Change(ByVal Target As Excel.Range) Static dAccumulator As Double With Target If Not Intersect(.Cells, Range("C8:O9")) Is Nothing Then If Not IsEmpty(.Value) And IsNumeric(.Value) Then dAccumulator = dAccumulator + .Value Else dAccumulator = 0 End If Application.EnableEvents = False .Value = dAccumulator Application.Ena...

SUMIF or SUMPRODUCT to calculate monthly sales of various products
Sheet1 In column C I have dates formatted as day/month/year; for example 31/01/2008 (usually there are several entries per day). In column D I have product names (two products) In column E I have the value of the product sales on that date. Sheet 2 Column A I have the date formatted as mmm-yy In Column's B and C I would like the sum of the sales of that product for the month. For example Sheet2 A B C Month Shoes Bags Jan-07 $3250 $1800 Feb-07 $1250 $4210 etc I tried formatting the date column in Sheet1 as mmm-yy and then using a pivot table but t...

Smartlist Monthly & Yearly Sales
I need help get a Smartlist create - Monthly sale, Yearlly sale & last year sales amount in one report. Can anyone help me. Buzz Dann Buzz, Could you specify if you need this by customer, by item? What type of layout are you looking for? If you are looking to get this with standard SmartList, I am afraid it is not possible and most likely you will have to use a combination of SmartLists Builder and SQL Server views. Best regards, MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "Buzz Dann" wrote: > I need help get ...

accumulated total in table
I have a stored procedure that i am trying to update a table coming from a bunch of different calculations. Main issue: i have a temporary table that has item, count, acc_count. i populate the item and count with values, but i need the acc_count to be accumulation of the count in descending count value: Item count acc_count itemA 3 32 itemB 10 25 itemC 4 29 itemD 15 15 I tried to do an update but the update won't let me do an ordered by in the sub-query for my sum: update #tmp_table set acc_count=isnull((sel...