Depreciation Calculation Multi Year

ultracyclist

Active Member
Joined
Oct 6, 2010
Messages
255
Office Version
  1. 365
Platform
  1. Windows
Hi,

I’m using the following formulas to calculate depreciation costs over a 6 year period assuming the first year is 2020. Looking for help on how I adjust the formulas if the starting year is 2021 thru 2030 so it always maintains a 6 year rolling period based on the first year. In column V, I have a drop-down list that goes from 2020 through 2030

Depreciation Schedule.xlsx
MNOPQRSTUVWXYZAAABACADAEAFAG
1Hardware CostHardware Purchase YearCost Type20202021202220232024202520262027202820292030
2$197,7622020Hardware0000002020
32021
42022
52023
62024
72025
82026
92027
102028
112029
122030
Sheet1
Cell Formulas
RangeFormula
P2P2=IF($N2="2020",$M2*10%,0)
Q2Q2=IF($N2="2020",$M2*20%,IF($N2="2021",$M2*10%,0))
R2R2=IF($N2="2020",$M2*20%,IF($N2="2021",$M2*20%,IF($N2="2022",$M2*10%,0)))
S2:T2S2=IF($N2="2020",$M2*20%,IF($N2="2021",$M2*20%,IF($N2="2022",$M2*20%,0)))
U2U2=IF($N2="2020",$M2*10%,IF($N2="2021",$M2*20%,IF($N2="2022",$M2*20%,0)))
Cells with Data Validation
CellAllowCriteria
N2:N12List=$AG$2:$AG$12


Thansks,
Tom
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,766
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,766
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ultracyclist

Active Member
Joined
Oct 6, 2010
Messages
255
Office Version
  1. 365
Platform
  1. Windows
Dave,

Thanks for the formula that worked perfectly. I have two follow up questions.

1. If I want to change the column headers in Row P1 thru V1 to say FY20 Cost, FY21, Cost, etc, what should change in the formula so its not looking for an exact match in the rows under column N? The values in column N will stay the same as provided in my example, 2020, 2021, etc.

2. If I have to shorten the depreciation cycle to a 4 or 5 year period in the future, how would those formulas look? On a 4 year depreciation cycle, it would be 16.5% for years 1 and 4 and 33% for years 2 and 3. On a 5 year depreciation cycle, it would be 12.5% for years 1 and 5 and 25% for years 2,3, and 4

Thank you again for responding to my post.

Tom
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,766
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

P1 etc have numbers like 20 with Custom format of "FY"#
Another alternative would be to reference line one as in first suggestion and hide that row.
Insert a new row 2 for the descriptions.

For Column N, you can use just numbers like 20 or custom format like "20"#.
Other alternatives include
- using a helper column
- extracting the 20 .. 21 in the formula
Edit the formula etc to your preference.

T202010b.xlsm
MNOPQRSTUV
1Hardware CostHardware Purchase YearCost TypeFY20FY21FY22FY23FY24FY25FY26
250,000.0020Hardware8,333.5016,666.5016,666.508,333.500.000.000.00
360,000.0021 10,000.2019,999.8019,999.8010,000.200.000.00
480,000.0022  13,333.6026,666.4026,666.4013,333.600.00
5100,000.0023   16,667.0033,333.0033,333.0016,667.00
1cc
Cell Formulas
RangeFormula
P2:V5P2=IF($N2>P$1,"",IF($N2=P$1,$M2*0.16667,MAX(0,MIN($M2-SUM($O2:O2),$M2*0.33333))))
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,766
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Edit the custom format to your preference such as "Cost FY"#
 

ultracyclist

Active Member
Joined
Oct 6, 2010
Messages
255
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

My preference for column N would be to keep the 4 digit year format and row 1 in the format you have FY20, FY21, etc. I tried several variances, but it does not seem to work. Is it possible to create a spreadsheet and attach it in this thread with 3 separate formulas showing the different depreciation cycle formulas on different rows

5 year = 10%, 20%, 20%, 20%, 10%
4 year = 12.5%, 25%,25%,12.5%
3 year = 16.5%, 33%, 33%, 16.5%

I'm very close and hoping to figure this out today.

Thanks
Tom
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,766
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Your rates total to the following. Are these the numbers that you want to use?

T202010b.xlsm
ABCDEFG
1Total
2510.00%20.00%20.00%20.00%10.00%80.00%
3412.50%25.00%25.00%12.50%75.00%
4316.50%33.00%33.00%16.50%99.00%
1d
Cell Formulas
RangeFormula
G2:G4G2=SUM(B2:F2)
 

ultracyclist

Active Member
Joined
Oct 6, 2010
Messages
255
Office Version
  1. 365
Platform
  1. Windows
Good catch, I missed an extra 20% in the 5 year schedule and missed an extra 25% in 4 year schedule.

It seems the first formula you provided in Cell P2 =IF($N2>P$1,"",IF($N2=P$1,$M2*0.1,MAX(0,MIN($M2-SUM($O2:O2),$M2*0.2)))) is what I need to see with the different depreciation cycles and the 4 year date format in column N and the column headers in row 1 to show FY20, FY21, etc

If you can show 3 separate rows (P2, P3, and P4) with the different formulas for each depreciation schedule that should give me what I need.

Thanks
Tom
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,766
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You did not specify the rates that wanted to use.
The formulas use the rates shown below.
There are many ways to specify the years. The most logical in my view is to use either full 4 digits such as 2020 in both parts or 2 digits in both parts.
As I stated before you could custom format 20 to appear as 2020.
Ignoring my preferences, I used a hybrid solution in the post.

T202010b.xlsm
ABCDEFG
112345Total
250.125000.250000.250000.250000.12500100.00%
340.166670.333330.333330.16667100.00%
430.250000.500000.25000100.00%
5
1d
Cell Formulas
RangeFormula
B2B2=10%/0.8
C2:E2C2=20%/0.8
F2F2=B2
B3B3=12.5%/0.75
C3:D3C3=25%/0.75
E3E3=B3
G2:G4G2=SUM(B2:F2)



T202010b.xlsm
MNOPQRST
1Hardware CostHardware Purchase YearCost TypeCost FY20Cost FY21Cost FY22Cost FY23Cost FY24
2100,000.00202012,500.0025,000.0025,000.0025,000.0012,500.00
3100,000.00202016,667.0033,333.0033,333.0016,667.000.00
4100,000.00202025,000.0050,000.0025,000.000.000.00
5100,000.002021 25,000.0050,000.0025,000.000.00
1cc
Cell Formulas
RangeFormula
P2:T2P2=IF($N2>(20&P$1)+0,"",IF($N2=(20&P$1)+0,$M2*0.125,MAX(0,MIN($M2-SUM($O2:O2),$M2*0.25))))
P3:T3P3=IF($N3>(20&P$1)+0,"",IF($N3=(20&P$1)+0,$M3*0.16667,MAX(0,MIN($M3-SUM($O3:O3),$M3*0.33333))))
P4:T5P4=IF($N4>(20&P$1)+0,"",IF($N4=(20&P$1)+0,$M4*0.25,MAX(0,MIN($M4-SUM($O4:O4),$M4*0.5))))
 

Watch MrExcel Video

Forum statistics

Threads
1,132,936
Messages
5,656,011
Members
418,264
Latest member
Reiper79

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