Depreciation Calculation Multi Year

ultracyclist

Active Member
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
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).

ultracyclist

Active Member
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

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
Edit the custom format to your preference such as "Cost FY"#

ultracyclist

Active Member

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
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
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
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))))

Replies
3
Views
95
Replies
1
Views
61
Replies
5
Views
499
Replies
3
Views
173
Replies
9
Views
111

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.

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

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