Calculating CGT (Capital Gain Tax)

[mwvirk]

Hello

I am using the following formula to calculate the CGT (capital gain tax)
if sold items are before 01-Jul-2021 then CGT will be 15%
but if sold after 01-Jul-2021 then CGT is 12.5%
i was hoping that in future CGT % might change so i gave a approx. date 01-Jul-2030 to charge CGT by 10%

=IF(BK2<0,0,IF(AC2<DATE(2021,7,1),BK2*15%,IF(AND(AC2>=DATE(2021,7,1),AC2<DATE(2030,7,1)),BK2*12.5%,BK2*10%)))

but the things went in other way and it's totally confusing since the new scenario is as below:

Where the security is acquired on or before June 30, 2022 12.5% CGT will be charged

Where the security is acquired on or after July 1, 2022:
Where the holding period does not exceed one year 15.0% CGT will be charged
Where the holding period exceeds one year but does not exceed two years 12.5% CGT will be charged
Where the holding period exceeds two years but does not exceed three years 10.0% CGT will be charged
Where the holding period exceeds three years but does not exceed four years 7.50% CGT will be charged
Where the holding period exceeds four years but does not exceed five years 5.00% CGT will be charged
Where the holding period exceeds five years but does not exceed six years 2.50% CGT will be charged
Where the holding period exceeds six years 0.00% CGT will be charged

please help me if this can be done by updating my exiting conditions and formula.

Thank you

 

[Dave Patton]

T202209a.xlsm
	A	B	C	D	E	F
1	Date purchased	22-Sep-22		Date of sale	31-Dec-26	4
2	Date	30-Jun-22
3
4
5		0	15.0%
6		1	12.5%
7		2	10.0%
8		3	7.5%
9		4	5.0%
10		5	2.5%
11		6	0.0%
12
13	Tax	5.0%	5.0%	5.0%
3b

Cell Formulas
Range	Formula
F1	F1	=DATEDIF(B1,E1,"y")
B13	B13	=IF(B1<=B2,0.125,LOOKUP((E1-B1)/365,B5:C11))
C13	C13	=IF(B1<=B2,0.125,LOOKUP(F1,B5:C11))
D13	D13	=IF(B1<=B2,0.125,LOOKUP(DATEDIF(B1,E1,"y"),B5:C11))

 

[Dave Patton]

Try the alternatives that you prefer

T202209a.xlsm
	A	B	C	D	E	F
1	Date purchased	22-Sep-22		Date of sale	31-Dec-22	0
2	Date	30-Jun-22
3
4
5		0	0.15
6		1	0.125
7		2	0.1
8		3	0.075
9		4	0.05
10		5	0.025
11		6	0
12
13	Tax	15.0%	15.0%	15.0%	15.0%	15.0%
14
3b

Cell Formulas
Range	Formula
F1	F1	=DATEDIF(B1,E1,"y")
B13	B13	=IF(B1<=B2,0.125,LOOKUP((E1-B1)/365,B5:C11))
C13	C13	=IF(B1<=B2,0.125,LOOKUP(F1,B5:C11))
D13	D13	=IF(B1<=B2,0.125,LOOKUP(DATEDIF(B1,E1,"y"),B5:C11))
E13	E13	=IF(B1<=B2,0.125,LOOKUP(DATEDIF(B1,E1,"y"),{0,0.15;1,0.125;2,0.1;3,0.075;4,0.05;5,0.025;6,0}))
F13	F13	=IF(B1<=B2,0.125,MAX(0,0.15-(DATEDIF(B1,E1,"y")>0)*DATEDIF(B1,E1,"y")*0.025))

 

[mwvirk]

Thanks, Dave. is it possible to add more conditions in the existing formula so I don't have to add more cells

=IF(BK2<0,0,IF(AC2<DATE(2021,7,1),BK2*15%,IF(AND(AC2>=DATE(2021,7,1),AC2<DATE(2030,7,1)),BK2*12.5%,BK2*10%)))

if you can update the above formula that can cover all the tax slabs
Where the security is acquired on or before June 30, 2022 12.5% CGT will be charged
Where the security is acquired on or after July 1, 2022:

maybe we can count the number of days. like if buying date and selling date are less than 365 days then it will consider less than 1 year
if buying date - sell date = more than 365 but less than 730 then it will fall in the next category and so on.

because I can change the old rules and charges I have already paid:
if sold items are before 01-Jul-2021 then CGT will be 15%
but if sold after 01-Jul-2021 then CGT is 12.5%

Profit	CGT 15% Charges By NCCPL (Updated To 12.5%)	Balance Profit After CGT Charges
-2,146.4285​	0.0000​	-2,146.4285​
17.0515​	2.5577​	14.4938​
512.2751​	76.8413​	435.4338​
-1,702.0083​	0.0000​	-1,702.0083​
351.3905​	52.7086​	298.6819​

 

[Dave Patton]

Did you try any of my suggestions?

N.B. You can download the suggestion. Click the icon below the f(x) in the header and paste to a clean sheet.

Review the formulas with formulas evaluate Formula.

T202209a.xlsm
	A	B	C	D	E	F	G
1	Date purchased	44814		Date of sale	46387	4
2	Date	44742
3
4		0	0.15
5		1	0.125
6		2	0.1
7		3	0.075
8		4	0.05
9		5	0.025
10		6	0
11
12	Tax		0.05	0.05			0.05
13
3b

Cell Formulas
Range	Formula
F1	F1	=DATEDIF(B1,E1,"y")
C12	C12	=IF(E1<=B2,0.15,IF(B1<=B2,0.125,LOOKUP(F1,B4:C10)))
D12	D12	=IF(E1<=B2,0.15,IF(B1<=B2,0.125,LOOKUP(DATEDIF(B1,E1,"y"),B4:C10)))
G12	G12	=IF(E1<=B2,0.15,IF(B1<=B2,0.125,LOOKUP(F1,aL)))

N.B, The formula in G12 uses named information; the lookup information is named.

 

[mwvirk]

Did you try any of my suggestions?

N.B. You can download the suggestion. Click the icon below the f(x) in the header and paste to a clean sheet.

Review the formulas with formulas evaluate Formula.

T202209a.xlsm
	A	B	C	D	E	F	G
1	Date purchased	44814		Date of sale	46387	4
2	Date	44742
3
4		0	0.15
5		1	0.125
6		2	0.1
7		3	0.075
8		4	0.05
9		5	0.025
10		6	0
11
12	Tax		0.05	0.05			0.05
13
3b

Cell Formulas
Range	Formula
F1	F1	=DATEDIF(B1,E1,"y")
C12	C12	=IF(E1<=B2,0.15,IF(B1<=B2,0.125,LOOKUP(F1,B4:C10)))
D12	D12	=IF(E1<=B2,0.15,IF(B1<=B2,0.125,LOOKUP(DATEDIF(B1,E1,"y"),B4:C10)))
G12	G12	=IF(E1<=B2,0.15,IF(B1<=B2,0.125,LOOKUP(F1,aL)))

N.B, The formula in G12 uses named information; the lookup information is named.

i tried to apply the multiple conditions since i need the results in the exiting cell and not possible to add extra cells. here is the formula i tried:

IF(BK2<0,0,IF(AC2<DATE(2021,7,1),BK2*15%,IF(AND(AC2>=DATE(2021,7,1),AC2<DATE(2022,7,1)),BK2*12.5%,IF(AND(AC2>=DATE(2022,7,1),AC2-E2<365),BK2*15%,IF(AND(AC2>=DATE(2021,7,1),AND(AC2-E2>365,AC2-E2<=730)),BK2*12.5%,IF(AND(AC2>=DATE(2021,7,1),AND(AC2-E2>730,AC2-E2<=1095)),BK2*10%,IF(AND(AC2>=DATE(2021,7,1),AND(AC2-E2>1095,AC2-E2<=1460)),BK2*7.5%,IF(AND(AC2>=DATE(2021,7,1),AND(AC2-E2>1460,AC2-E2<=1825)),BK2*5%,IF(AND(AC2>=DATE(2021,7,1),AND(AC2-E2>1825,AC2-E2<=2190)),BK2*2.5%,BK2*0%)))))))))


now if is it possible to reduce the length and any other alternative w/o adding new cells?


this was the requirement:

if sold items are before 01-Jul-2021 then CGT will be 15%
but if sold after 01-Jul-2021 then CGT is 12.5%
i was hoping that in future CGT % might change so i gave a approx. date 01-Jul-2030 to charge CGT by 10%

=IF(BK2<0,0,IF(AC2<DATE(2021,7,1),BK2*15%,IF(AND(AC2>=DATE(2021,7,1),AC2<DATE(2030,7,1)),BK2*12.5%,BK2*10%)))

but the things went in other way and it's totally confusing since the new scenario is as below:

Where the security is acquired on or before June 30, 2022 12.5% CGT will be charged

Where the security is acquired on or after July 1, 2022:
Where the holding period does not exceed one year 15.0% CGT will be charged
Where the holding period exceeds one year but does not exceed two years 12.5% CGT will be charged
Where the holding period exceeds two years but does not exceed three years 10.0% CGT will be charged
Where the holding period exceeds three years but does not exceed four years 7.50% CGT will be charged
Where the holding period exceeds four years but does not exceed five years 5.00% CGT will be charged
Where the holding period exceeds five years but does not exceed six years 2.50% CGT will be charged
Where the holding period exceeds six years 0.00% CGT will be charged



thank you

 

[Dave Patton]

The named information is mentioned in the earlier posts. In Formulas Name Manager, the Lookup information is named aL and the value is ={0,0.15;1,0.125;2,0.1;3,0.075;4,0.05;5,0.025;6,0}.

T202209a.xlsm
	H	I	J	K	L	M
1
2	Date	30-Jun-22
3
4
5	Puchase Date	Amount	Sale Date	Amount	Profit	Tax
6	5-May-20	10,000.00	5-Jun-22	15,000.00	5,000.00	750.00
7	5-May-20	20,000.00	31-Dec-22	32,000.00	12,000.00	1,500.00
8	5-Jul-22	30,000.00	31-Dec-23	32,000.00	2,000.00	250.00
9	9-Sep-22	100,000.00	31-Dec-32	140,000.00	40,000.00	0.00
10
3b

Cell Formulas
Range	Formula
L6:L9	L6	=K6-I6
M6:M9	M6	=IF(J6<=$I$2,0.15,IF(H6<=$I$2,0.125,LOOKUP(DATEDIF(H6,J6,"y"),aL)))*L6

Does the above provide the correct results?

If you want us to edit your formula, please post an extract of your sheet with the forum's tool named XL2BB.

 

[Dave Patton]

You can put the date June 30, 2022 in the formula or name the information.

 

[mwvirk]

The named information is mentioned in the earlier posts. In Formulas Name Manager, the Lookup information is named aL and the value is ={0,0.15;1,0.125;2,0.1;3,0.075;4,0.05;5,0.025;6,0}.

T202209a.xlsm
	H	I	J	K	L	M
1
2	Date	30-Jun-22
3
4
5	Puchase Date	Amount	Sale Date	Amount	Profit	Tax
6	5-May-20	10,000.00	5-Jun-22	15,000.00	5,000.00	750.00
7	5-May-20	20,000.00	31-Dec-22	32,000.00	12,000.00	1,500.00
8	5-Jul-22	30,000.00	31-Dec-23	32,000.00	2,000.00	250.00
9	9-Sep-22	100,000.00	31-Dec-32	140,000.00	40,000.00	0.00
10
3b

Cell Formulas
Range	Formula
L6:L9	L6	=K6-I6
M6:M9	M6	=IF(J6<=$I$2,0.15,IF(H6<=$I$2,0.125,LOOKUP(DATEDIF(H6,J6,"y"),aL)))*L6

Does the above provide the correct results?

If you want us to edit your formula, please post an extract of your sheet with the forum's tool named XL2BB.

Thanks, Dave for giving your valuable time. it's working fine for me.