Accumulate Amortized Expenditures by month

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

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?
 
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
 
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.
 
Back
Top