Calcualtion of renewal

[Latha]

I want to create an automatic renewal for calcualtion of depreciation
after x years. To explain
this is what I want it to look like. I want a formula for this

No; of Years Year 1 Year 2 Year 3 Year 4 Year 5...... Year 30

3 Years

4 Years

5 Years

7 Years

10 Years


If a asset is purchased in the first year it will have to be renewed at
the end of 3 years, or 4 years or 5 years depending on the life cycle
of the asset. How do I come up with a formula that will automatically
put the value of the asset every 3 years if it is a 3 year asset or put
the value every 5 years if it is a 5 year asset etc. To complicate
things I have set out the Year as 12 monthly periods> I am sure there
will be someone out there to help me solve this problem. I am looking
for a single forumula that will calculate the renewals.

I hope I have made myself clear.

 

[Roger Govier]

Hi

I think you would need something like

A1 Asset Name
B1 Asset Value
C1 Asset Life
D1 Year1
E1 Year2 etc.

Then with values entered in A2, B2 and C2 enter in D2
=IF(B2="","",IF(MOD(COLUMN()-4,$C2)=0,$B2,""))
Copy across your columns through to Year30
Copy the formulae down for the extent of the number of assets you will
need to list