Cost Allocation/Amortisation

[Karthikg17]

Hi All,

Need help in cost allocation over a period of time.

Data is as follows

I have a cost incurred on a monthly basis like

Dec-09 - $3000
Jan-10 - $2500
Feb-10 - $4000

Each of these costs need to be amortised over a period of 3 months
Dec-09 will need to have $1000
Jan-09 will need to have $1000 (of Dec-09) + $833.33 (of Jan-10)
Feb-09 will need to have $1000 (of Dec-09) + $833.33 (of Jan-10)+$1333.33 (of Feb-10).
Mar-09 will have $833.33 (of Jan-10)+$1333.33 (of Feb-10)

Is there a combination of functions that I could use to directly do this in a single cell against each month.

Your help is highly appreciated

Regards,

 

[Yard]

Hi,

edit : re-reading the post

 

[Yard]

Hi,

I still suspect there's more to this, but with your dollar amounts in B1, B2 and B3, try this in C1 and copied down:

=SUM(OFFSET(B1,IF(ROW()<3,ROW()-1,2)*-1,0,IF(ROW()<3,ROW(),3)))/3

 

[Yard]

NB If you could have 2 blank rows above your first dollar amount then you could make it much simpler:

e.g. dollar amounts in B3:B5 and this in C3:

=SUM(B1:B3)/3

 

[MickG]

Hi, Try this:-
NB:- Cost in Column "B" , month apart, Results Column "C".

[COLOR=navy]Sub[/COLOR] MG30Nov27
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, nRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] bound [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] term, Am, c, n
[COLOR=navy]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
term = 3
 [COLOR=navy]For[/COLOR] Am = 1 To Rng.Count
        [COLOR=navy]For[/COLOR] n = Am To term + Am - 1
            Range("C" & n).Value = Range("C" & n).Value + (Range("B" & Am) / 3)
         [COLOR=navy]Next[/COLOR] n
    [COLOR=navy]Next[/COLOR] Am
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Karthikg17]

Yard and Mick.. Thanks a ton guys..
Got the solution

 

[Jobowo]

Yes, the simple accumulations would seem to solve the problem as stated but perhaps the financial functions AMORDEGRC and AMORLINC might also come in handy at some time in the future.