BYU Student Author: @klayton, @Marta_Ellsworth, @Dallin_Gardner
Reviewers: @Jae, @Mark, @Hyrum, @Kyle_Nilsen
Estimated Time to Solve: 20 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
Overview
You are a CPA working at mid-size, regional firm. Your responsibility is to process hundreds of people’s tax returns every year, including their 2022 tax returns. Many of your clients are wealthy and frequently trade stocks in the stock market. Today, your task is to determine whether each stock was held “long-term” or “short-term” using Excel. To be considered “long-term,” the stock must have been held for over a year.
The distinction between long- and short-term capital gains is significant. Long-term gains are taxed at a preferential rate, while short-term gains are taxed at the normal income tax rate. The difference in tax rates can be substantial and could potentially save your clients hundreds to thousands of dollars in taxes. As a tax planning opportunity, you will output a file that lists how many more days your client needed to hold onto each of their short-term stocks to qualify for long-term capital tax treatment.
Instructions
-
The data provided includes all your client’s stock transactions. Calculate the total long- and short-term capital gain or loss.
a. The final table should contain the following information: -
For part 2, filter so you have only the short-term stocks.
a. Calculate how many days more the client would have needed to hold onto the stock in order to sell them as long-term assets instead of short term.
b. The final table should contain the following information:
Data Files
Suggestions and Hints
a. You can use a pivot table to group the data for Part 1.
b. Reminder, in order to be long-term, the difference between the sale date and the purchase date must be greater than 1 year (365 days).
c. You are preparing 2022 returns. Any stocks sold outside of 2022 shouldn’t be included in the reports.
Solution