Dynamic CCA Schdule

[Trixie1919]

Hi All,

Trying to make a dynamic model around a CCA Depreciation Schedule. Not sure if this is possible
Notes:

1. Can start the claim once revenue (Row 3) comes on. I.e Capital Spend to date can start to be claimed.
   1. As it stands now it could be D8=If(D3=0,0,D4), then repeat this on, however if revenue comes on prior to April 25 I will not work
   2. And if we have additional Capital Spend post March 2025 if will not work
2. Any ideas on how to set this up?

Any help would be greatly appreciated.

 

[Dave Patton]

Welcome to the forum.
N.B. You can post an extract of your sheet to the forum with the forum's tool named XL2BB.

It will save a lot of volunteer time, if you provide clear information.
N.B. We do not not know your challenge, your business, what your actually require, your country, rules for the calculation, etc.
Are you working on an accounting schedule of a tax schedule?
What is the "deduction base"?
Is the amount calculated annually or for each month?
What is your year-end?

 

[Trixie1919]

CCA - Model.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
2		Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25	Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25	Jan-26	Feb-26	Mar-26	Apr-26	May-26	Jun-26	Jul-26	Aug-26	Sep-26	Oct-26	Nov-26	Dec-26
3	Facility Revenue	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 270,562	$ 417,701	$ 533,858	$ 801,780	$ 801,780	$ 773,900	$ 1,073,853	$ 1,037,197	$ 1,073,853	$ 1,073,853	$ 963,883	$ 1,073,853	$ 1,037,197	$ 1,073,853	$ 1,037,197	$ 1,073,853	$ 1,073,853	$ 1,037,197	$ 1,678,000	$ 1,621,855	$ 1,678,000
4	Capital Spend	$ 2,557,420	$ 2,557,420	$ 2,557,420	$ 2,557,420	$ 2,557,420	$ 2,557,420	$ 2,557,420	$ 2,557,420	$ 2,557,420	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
5
6	CCA Depreciation Schedule
7	Opening Balance	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 11,316,584	$ 11,127,975	$ 10,942,508	$ 10,760,133	$ 10,580,798	$ 10,404,451	$ 10,231,044	$ 10,060,526	$ 9,892,851	$ 9,727,970	$ 9,565,837	$ 9,406,406	$ 9,249,633	$ 9,095,472	$ 8,943,881	$ 8,794,817	$ 8,648,236	$ 8,504,099	$ 8,362,364	$ 8,222,991
8	Net Additions	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 23,016,782	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
9	Deduction Base	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 11,508,391	$ 11,316,584	$ 11,127,975	$ 10,942,508	$ 10,760,133	$ 10,580,798	$ 10,404,451	$ 10,231,044	$ 10,060,526	$ 9,892,851	$ 9,727,970	$ 9,565,837	$ 9,406,406	$ 9,249,633	$ 9,095,472	$ 8,943,881	$ 8,794,817	$ 8,648,236	$ 8,504,099	$ 8,362,364	$ 8,222,991
10	Claim	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ (191,807)	$ (188,610)	$ (185,466)	$ (182,375)	$ (179,336)	$ (176,347)	$ (173,408)	$ (170,517)	$ (167,675)	$ (164,881)	$ (162,133)	$ (159,431)	$ (156,773)	$ (154,161)	$ (151,591)	$ (149,065)	$ (146,580)	$ (144,137)	$ (141,735)	$ (139,373)	$ (137,050)
11	Ending Book Value	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 11,316,584	$ 11,127,975	$ 10,942,508	$ 10,760,133	$ 10,580,798	$ 10,404,451	$ 10,231,044	$ 10,060,526	$ 9,892,851	$ 9,727,970	$ 9,565,837	$ 9,406,406	$ 9,249,633	$ 9,095,472	$ 8,943,881	$ 8,794,817	$ 8,648,236	$ 8,504,099	$ 8,362,364	$ 8,222,991	$ 8,085,941
Sheet1

Cell Formulas
Range	Formula
E7:AG7	E7	=D11
D8	D8	=IF(D3=0,0,$D$4)
E8	E8	=IF(E3=0,0,$D$4+$E$4)
F8	F8	=IF(F3=0,0,$D$4+$E$4+$F$4)
G8	G8	=IF(G3=0,0,$D$4+$E$4+$F$4+$G$4)
H8	H8	=IF(H3=0,0,$D$4+$E$4+$F$4+$G$4+$H$4)
I8	I8	=IF(I3=0,0,$D$4+$E$4+$F$4+$G$4+$H$4+$I$4)
J8	J8	=IF(J3=0,0,$D$4+$E$4+$F$4+$G$4+$H$4+$I$4+$J$4)
K8	K8	=IF(K3=0,0,$D$4+$E$4+$F$4+$G$4+$H$4+$I$4+$J$4+$K$4)
L8:M8	L8	=IF(L3=0,0,$D$4+$E$4+$F$4+$G$4+$H$4+$I$4+$J$4+$K$4+$L$4)
D9:AG9	D9	=+D7+ IF(D8>0,D8/2,D8)
D10:AG10	D10	=(-D9*0.2)*(1/12)
D11:AG11	D11	=D9+D10

Hi Dave,

Thank you!

Deduction base is what we feeds into the claim amount, row10. Amount is calculated each month. We go Year end at December. For this modeling task the time period will be July 2024 to Dec 2026.

 

[Dave Patton]

1. This is not a CCA schedule that I am familiar with.
2. You provided very little information!
3. You could probably have a simpler schedule.

Review the following guess and see if it helps.

T202401a.xlsm
	A	B	C									L	M	N
1
2												45717	45748	45778
3			Facility Revenue									200,000	270,562	417,701
4			Capital Spend									2,557,420	10000	0
5
6			CCA Depreciation Schedule
7			Opening Balance									0	11,316,584	11,132,891
8			Net Additions									23,016,782	10,000	0
9			Deduction Base									11,508,391	11,321,584	11,132,891
10			Claim									-191,807	-188,693	-185,548
11			Ending Book Value									11,316,584	11,132,891	10,947,343
12
3b

Cell Formulas
Range	Formula
L7:N7	L7	=K11
L8	L8	=SUM($D$4:L4)
M8:N8	M8	=M4
L9	L9	=IF(L8,L8/2)
M9:N9	M9	=M8/2+M7
L10:N10	L10	=(-L9*0.2)/12
L11:N11	L11	=L9+L10

 

[Dave Patton]

T202401a.xlsm
	A	B	C	D								L	M	N
1
2				01-07-24								01-03-25	01-04-25	01-05-25
3			Facility Revenue	0								200,000	270,562	417,701
4			Capital Spent	2557420.2								2,557,420	10000	0
5
6			CCA Depreciation Schedule
7			Deduction Base	0								11,508,391	11,321,584	11,132,891
8			Claim	0								191,807	188,693	185,548
9			Ending Book Value	0								11,316,584	11,132,891	10,947,343
10
3bb

Cell Formulas
Range	Formula
D7,L7:N7	D7	=(D3>0)*SUM($D$4:D4)/2-SUM($C$8:C8)
D8,L8:N8	D8	=D7*0.2/12
D9,L9:N9	D9	=D7-D8