Technical monthly depreciation question

**Tommy** · 05-05-2009, 07:02 AM

I have a monthly excel timeline for depreciation of capital contributions (highlighted yellow) (see attached for file).

Each contribution is to be depreciated over 5 years ie 60 months.

I am trying to pickup each contribution in a given month in row 13 and depreciate that contribution on a straight line basis for 60 months. There may be multiple contributions in each month which needs to be picked up, hence there is a month counter (col J). There is a counter on the timeline too (row9) inserted initially to match with counter.

I am having difficulty coming up with one elegant formula that picks up a given contribution(s) in a month and continues to depreciate until zero AND also includes subsequent contributions and depreciate to zero 60 months from month of that contribution!

For example, the 200 needs to be depreciated for 60 months from month 1, whilst the 800 comes in in month 20 (as shown in row 12) and initially with the 200 starts depreciating and continues to do so for an extra 20 months.

All help greatly appreciated.

Thanks

**DonkeyOte** · 05-05-2009, 07:25 AM

If I've understood...

Row 12 as I see it need not be a SUMPRODUCT - a SUMIF should suffice ?

F12: =SUMIF($A$23:$A$41,"<="&F$7,$E$23:$E$41)
copied across

Re: cumulative depreciation... I would first advise you add a column to hold month of "disposal" - ie start date + 59 months (given we're assuming you depreciate in month of acquisition / capital contribution), eg:

G23: =DATE(YEAR(A23),MONTH(A23)+59,1)
copied down to G41 (we're also assuming all dates 1st of month)

The additional date column avoids the need for repetitive calculations (given the end date is constant regardless of which month you're calculating the periodic depreciation) ... so based on G23:G41 holding last month of depreciation you can then have an approach along the lines of:

F13: =E13-SUMPRODUCT(--($A$23:$A$41<=F$7),--($G$23:$G$41>=F$8),$E$23:$E$41/60)
copied across

Again, this is all based on my interpretation... the above takes prior total and subtracts from it the depreciation calculation for the current month based on which assets remain active and their initial cost/capital value.