Dynamic asset depreciation calculation

David W 123

New Member
Joined
May 14, 2020
Messages
9
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi everyone - I was hoping someone could please help me (this is my first time posting so fingers crossed!)

I have a list of assets (many thousand) with different purchase dates. I would like the cost to be depreciated over 12 months in line with the top table (% varies by month). I've manually completed asset number 1 as an example.

Is there a nice formula that could please help me do this analysis for several thousand assets?

Many thanks!
XLS query.PNG
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,807
Welcome to the forum!

Try:

Cell Formulas
RangeFormula
E7:AB11E7=$B7*IFERROR(INDEX($C$3:$N$3,DATEDIF($C7,E$6,"m")+1),0)
AD7:AD11AD7=SUM(E7:AB7)


For this I assumed that the values in E6:AB6 and C7:C11 are actual Excel dates, and that they refer to the first of the month. They are just formatted to show month-year. If your actual dates are otherwise, we might have to adjust things.
 

David W 123

New Member
Joined
May 14, 2020
Messages
9
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Eric
Thank you so much - this is ideal and you're a star!
Many many thanks!
David
 

Watch MrExcel Video

Forum statistics

Threads
1,129,530
Messages
5,636,857
Members
416,945
Latest member
Himu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top