Depreciation Calculation Multi Year

[ultracyclist]

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
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	Hardware Cost	Hardware Purchase Year	Cost Type	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030
2	$197,762	2020	Hardware	0	0	0	0	0	0												2020
3																					2021
4																					2022
5																					2023
6																					2024
7																					2025
8																					2026
9																					2027
10																					2028
11																					2029
12																					2030
Sheet1

Cell Formulas
Range	Formula
P2	P2	=IF($N2="2020",$M2*10%,0)
Q2	Q2	=IF($N2="2020",$M2*20%,IF($N2="2021",$M2*10%,0))
R2	R2	=IF($N2="2020",$M2*20%,IF($N2="2021",$M2*20%,IF($N2="2022",$M2*10%,0)))
S2:T2	S2	=IF($N2="2020",$M2*20%,IF($N2="2021",$M2*20%,IF($N2="2022",$M2*20%,0)))
U2	U2	=IF($N2="2020",$M2*10%,IF($N2="2021",$M2*20%,IF($N2="2022",$M2*20%,0)))

Cells with Data Validation
Cell	Allow	Criteria
N2:N12	List	=$AG$2:$AG$12

Thansks,
Tom

 

[ultracyclist]

Dan,

Thank you for all of your help. Everything is working as expected.

Tom

 

[Dave Patton]

Glad it is working for you.
Why are you using Text for 2020 etc.
N.B. Your post is showing results of 0.
The post below is very much like post #2.

T202010b.xlsm
	M	N	O	P	Q	R	S	T	U	V
1	Hardware Cost	Hardware Purchase Year	Cost Type	FY2020	FY2021	FY2022	FY2023	FY2024	FY2025	FY2026
2	197,762.00	2020	Hardware	19,776.20	39,552.40	39,552.40	39,552.40	39,552.40	19,776.20	0.00
1e

Cell Formulas
Range	Formula
P2:V2	P2	=IF($N2>P$1,"",IF($N2=P$1,$M2*0.1,MAX(0,MIN($M2-SUM($O2:O2),$M2*0.2))))

 

[ultracyclist]

Are you referring to formatting Column N or P as Text? One thing I have noticed with the formulas is if I change the column header in Column P to something other than what you provided in your example, it doubles the value in P2 and does the same for subsequent rows where the formula carries across the row? This is the first time I have run into this where a formulas function is dependent on the format of the column header or cell row. Is there a default format I should use which does not impact the output of the formula?

Here's an example from one of your posts where the cost was 50,000 and the column header is FY20. If I change Fy20 to something else it doubles the value from $8333 to $16667

=IF($N2>P$1,"",IF($N2=P$1,$M2*0.16667,MAX(0,MIN($M2-SUM($O2:O2),$M2*0.33333))))

Thanks,
Tom

 

[Dave Patton]

Hello Tom
1. Did you copy any of the posts and try them on a clean sheet?
You can use the icon just below the f(x) at the top of the post and then paste the information to M1 of a clean sheet.
2. Move to P2 and use Excel's Formula Evaluate to review the formula.
3. As stated previously, the numbers in N2 and P1 etc. preferably are consistent 2020 and 2020 or 20 and 20. You can custom format the numbers
to appear as you prefer. The formula uses real numbers not TEXT.
4. I did include a hybrid solution since you may try using 2020 and 20! P1 contains 20 and it is Custom Formatted to show FY20. "FY"#.
The formula is a little more complex but it works.
5. The number in P3 is 20. You can review the formula. You could Custom Format the 20 to appear as 2020 but that could be confusing in the future.

The next post puts the rate in O2.
I used numbers like 2020 in Q1:V1 and N2.

T202010b.xlsm
	M	N	O	P	Q	R	S	T	U
1	Hardware Cost	Hardware Purchase Year	Cost Type	Cost FY20	Cost FY21	Cost FY22	Cost FY23	Cost FY24	Cost FY25
2	50,000.00	2020	Hardware	6,250.00	12,500.00	12,500.00	12,500.00	6,250.00	0.00
3	50,000.00	20	Hardware	6,250.00	12,500.00	12,500.00	12,500.00	6,250.00	0.00
1cc

Cell Formulas
Range	Formula
P2:U2	P2	=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:U3	P3	=IF($N3>P$1,"",IF($N3=P$1,$M3*0.125,MAX(0,MIN($M3-SUM($O3:O3),$M3*0.25))))

 

[Dave Patton]

T202010b.xlsm
	M	N	O	P	Q	R	S	T	U
1	Hardware Cost	Hardware Purchase Year	Rate	Cost Type	Cost FY2020	Cost FY2021	Cost FY2022	Cost FY2023	Cost FY2024
2	50,000.00	2020	25.0%	Hardware	6,250.00	12,500.00	12,500.00	12,500.00	6,250.00
3	100,000.00	2021	50.0%	Hardware		25,000.00	50,000.00	25,000.00	0.00
1ccc

Cell Formulas
Range	Formula
Q2:U3	Q2	=IF($N2>Q$1,"",IF($N2=Q$1,$M2*$O2/2,MAX(0,MIN($M2-SUM($P2:P2),$M2*$O2))))

 

[ultracyclist]

Hi Dave,

Thanks for sharing the tip about using the icon just below the f(x) formula bar to copy the content into my sheet. This is my first time working with the the XL2BB add-in, so I was copying the formulas you provided in your responses or just clicking on the individual cells and copying them to my sheet. Makes sense.

The only thing that does not carry over properly during the copy and paste process is the custom formatting for column headings Q1:U1. When I paste the content it shows the 4 digit year which is fine.

Thank you again for all your help.

Tom

 

[Dave Patton]

Thanks for the feedback. Dave