David W 123
New Member
- Joined
- May 14, 2020
- Messages
- 9
- Office Version
- 2019
- 2016
- Platform
- Windows
Hi everyone.
I have a list of assets (many thousand) with different purchase dates. I would like the cost to be depreciated over 4 months in line with the lower table (% varies by month) and I already have a formula which works well for assets 1 and 2 in the table. However is there a formula which adjusts for number of days, so that for asset 3 (purchased 8 March) : March would be ~23 days of the 40% cost, and April would be ~8 days of the 40% cost and ~23 days of the 30% cost?
Many thanks for your time and help!
David
I have a list of assets (many thousand) with different purchase dates. I would like the cost to be depreciated over 4 months in line with the lower table (% varies by month) and I already have a formula which works well for assets 1 and 2 in the table. However is there a formula which adjusts for number of days, so that for asset 3 (purchased 8 March) : March would be ~23 days of the 40% cost, and April would be ~8 days of the 40% cost and ~23 days of the 30% cost?
Book1 | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
3 | Asset | Cost | Purchase date | Jan-21 | Feb-21 | Mar-21 | Apr-21 | May-21 | Jun-21 | Jul-21 | Aug-21 | Sep-21 | Oct-21 | Nov-21 | Dec-21 | |||
4 | 1 | 500,000 | 01-Feb-21 | 0 | 200,000 | 150,000 | 100,000 | 50,000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||
5 | 2 | 600,000 | 01-Mar-21 | 0 | 0 | 240,000 | 180,000 | 120,000 | 60,000 | 0 | 0 | 0 | 0 | 0 | 0 | |||
6 | 3 | 800,000 | 08-Mar-21 | 0 | 0 | 0 | 320,000 | 240,000 | 160,000 | 80,000 | 0 | 0 | 0 | 0 | 0 | |||
7 | ||||||||||||||||||
8 | Cost allocation phasing | |||||||||||||||||
9 | Month 1 | Month 2 | Month 3 | Month 4 | ||||||||||||||
10 | 40% | 30% | 20% | 10% | ||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F4:Q6 | F4 | =$C4*IFERROR(INDEX($F$10:$I$10,DATEDIF($D4,F$3,"m")+1),0) |
Many thanks for your time and help!
David