Modeling investment tax planning with Excel

Editor: Annette Nellen, Esq., CPA, CGMA

Ensuring that professionals can effectively use ever-changing technology is one of the biggest challenges facing the accounting industry (see Drew, “Staffing Concerns Again Top List of CPA Firm Issues,” Journal of Accountancy (June 14, 2017). Technology skills are nearly always listed as a requirement for entry-level accounting jobs (see Robert Half, ­”Accounting Skills You Need to Succeed on the Job” (May 8, 2018). These skills are so important, in fact, that accounting firms such as PwC consider a foundation in Microsoft Excel a core competency of accounting undergraduate education and recommend that accounting programs include a computer course that stresses spreadsheet, database, and programming skills (see the PwC report Data Driven: What Students Need to Succeed in a Rapidly Changing Business World (February 2015).

While new technologies evolve frequently, Excel remains one of the key technologies that accounting firms and corporations expect their accountants to be able to use. The AICPA also expects those who are pursuing a CPA license to have strong Excel skills. Effective April 1, 2018, the AICPA replaced the generic spreadsheet tool that was embedded within the CPA Exam with Excel. Because Excel is useful in analyzing many types of transactions, accounting firms are investing significant amounts to create and offer courses to help their employees improve Excel skills. Accounting faculty can also assist students in building strong Excel skills by incorporating assignments that require students to combine technical tax knowledge with Excel’s analytic capabilities.

This column presents a series of cases designed to give students an opportunity to use Excel to perform various analyses related to stock and mutual fund investments. In addition to helping students improve their Excel skills, the cases allow students to see the value of Excel in examining various real-world tax scenarios. These cases require students to use the analyses performed in Excel to make decisions and/or provide recommendations to a client. In practice, practitioners may use tax-planning software specifically designed to analyze these complex scenarios with minimal inputs. However, by having students use Excel to create formulas, the cases encourage students to think analytically about the decision process underlying the tax planning software. The following articles from The Tax Adviser provide examples of how Excel can be used in a tax course: Evans, et al., “Using Excel in the Classroom: Performing a Multilevel Tax Analysis of an S Corporation Conversion,” 47 The Tax Adviser 350 (May 2016); Evans and Hansen, “Preparing the Income Tax Footnote: A Comprehensive Study in Excel,” 48 The Tax Adviser 826 (November 2017); and Brink and Hansen, “Using Big Data to Identify Tax Risk,” 49 The Tax Adviser 318 (May 2018).

Each case is independent of the other, allowing instructors the flexibility to use the case(s) appropriate for their course. The cases differ in terms of their levels of Excel use and technical tax knowledge required and may be used at the undergraduate and/or graduate level. Case 1 and Case 2 provide Excel templates for students to complete. These templates include embedded correctness checks to assist students in performing their analyses. Case 3 requires students to develop their own Excel worksheet. Instructors have the flexibility with Case 3 to tailor the requirements for student worksheets to include those Excel skills they want students to demonstrate, such as requiring the use of IF functions and/or PV functions. The templates for all three cases are available below:

Case 1

Case 1 Solution

Case 2

Case 2 Solution

Case 3

It is recommended that instructors who use these Excel case studies modify the facts of each case annually to update them for tax law changes, but also to maintain the academic integrity of each case. Additionally, while the cases have been designed to emphasize investment tax planning from an individual taxation standpoint, instructors can use these cases in other tax areas with minor modifications, as the rules applicable to investments are similar for corporations and investment funds, and other passthrough entities.

Case 1 requires students to use Excel to perform calculations applying the wash-sale rules to stock sale and purchase transactions. To complete the case successfully, students must employ a multilevel analysis that incorporates the loss disallowance and subsequent basis adjustment rules. Students will use a formula-based approach for their analysis, using a predesigned Excel template that accompanies the case. After completing the case, students should have gained a deeper understanding of the application of the wash-sale rule and how it can affect both the taxpayer’s current tax position, through the disallowed loss, and the taxpayer’s future tax position, through the tax basis adjustment made to repurchased shares.

Case 2 requires students to engage in problem-solving and decision-making, applying various permissible methods to determine the tax basis used to compute gain or loss on mutual fund dispositions and deciding which method is appropriate given alternate fact patterns. To complete the case successfully, students must apply their understanding of the permissible methods used to determine tax basis of mutual fund dispositions, as well as how the time value of money affects investment decisions. Students will perform an analysis of mutual fund transactions using four permissible methods for determining cost basis. Students will use a formula-based approach for their analysis, using a predesigned Excel template that accompanies the case. After completing the case, students should gain a deeper understanding of the permissible alternative methods for calculating capital gains or losses on the disposition of mutual funds. Students also should gain a deeper understanding of the impact of the time value of money on investment decisions and the interplay between the amount of a capital gain and the differential tax rates that apply to long-term versus short-term capital gains.

Case 3 requires students to design their own Excel spreadsheet to assist in a tax planning decision. Students assume the role of a tax adviser, analyzing a client’s proposed transactions and recommending the best course of action given the client’s overall objectives. To complete the case successfully, students must apply their understanding of the tax rates and restrictions applicable to capital gains and losses. Students must also understand the effect of the time value of money when transactions occur in different periods.

Students must demonstrate their ability to work with Excel by generating their own worksheets to analyze the proposed transactions. Upon successful completion of this case, students should have a stronger understanding of the implications of tax rates and tax rate changes on tax planning, the impact of the time value of money on investment decisions, and the importance of nontax factors when making tax planning decisions.

Implementing these cases offers educators an opportunity to invite local tax practitioners into the classroom or to extracurricular accounting events to discuss the importance of Excel skills in practice. While not all accounting practitioners are engaged in individual tax investment planning decisions like those in the provided cases, most practitioners employ Excel-based analyses to make decisions within their practice area (e.g., financial statement analysis, asset vs. stock sale analysis, and multistate tax planning). Following instructor debriefing of the case study, practitioners could be asked to present samples of their own Excel analyses. Practitioners’ demonstration of real-world Excel analyses would be highly valuable in reinforcing to students the importance of mastering Excel.

Microsoft Excel templates

Cases 1 and 2 provide students with Excel templates and ask them to perform calculations in specified cells. Case 3 requires students to prepare their own Excel worksheet. Using Excel to complete projects not only provides students with the opportunity to develop skills with a tool used frequently in practice, but also effectively reinforces classroom learning. Educators can also benefit from using Excel because it may improve the efficiency of grading projects and providing feedback to students.

Excel templates, like the ones accompanying two of the cases, can include mechanisms that allow students to check their work. Using an IF function in the cells adjacent to required answers could help provide immediate feedback to students, displaying “please review” until the student inputs the correct answer. By allowing students to acknowledge and correct their errors in real time, it may be possible to enhance their learning experience. Note that educators must lock cells including the IF function if they want the contents to remain hidden.

In addition to requiring numerical answers, the cases require students to answer qualitative questions. For example, in Case 2, after calculating the capital gain or loss on the sale of mutual fund shares using (1) FIFO, (2) LIFO, (3) average cost, and (4) specific identification, students are asked to choose the optimum method for tax minimization given the current income tax position and given an alternate income tax position. These questions require students to not only understand the tax laws regarding how to calculate net capital gains or losses, but also to understand the applicable tax rates for capital gains, rules regarding the carryover and usage of capital losses, and how to help taxpayers plan their sales transactions in a way that creates the most beneficial tax results. By using the Excel data validation tools, instructors can create a dropdown list of potential responses to these questions for students to choose. The dropdown menu of options helps students comprehend the purpose of the question, and instructors can more easily determine the correctness of the response.

Details of the case studies

Case 1

Case 1 is a problem-solving case that requires students to use Excel to apply the wash-sale rules to stock sales and purchases. The case requires students to calculate recognized capital gains or losses on stock sales in a setting where a subsequent repurchase of the shares occurs within the 30-day window prescribed under the wash-sale rules. Students are also required to calculate tax basis of shares held after the occurrence of the wash sale. Students receive an Excel template to complete this case.

Students are provided with purchase and sales information for 10 stock holdings, which allows them to calculate realized capital gains or losses. Students then receive purchase information for subsequent purchases of the same 10 stock holdings that occur within the 30-day window that would fall under the wash-sale rules. Students integrate the initial sale and subsequent purchase information to calculate (1) the amount of capital gain or loss realized versus recognized after the application of the wash-sale rules, and (2) the basis of the shares held after the application of the wash-sale rules.

Of the 10 stock holdings, three of the initial sales result in capital gain, while seven result in a capital loss. Students must correctly apply the wash-sale rules only to those sales that result in an initial loss, in order to correctly calculate the total recognized capital gain or loss. In addition to showing how to calculate the disallowed loss, the case helps students understand the impact of the disallowed loss on the taxpayer’s overall capital gain or loss position.

In five of the 10 repurchase transactions, more shares are repurchased than were initially sold. In the remaining five repurchase transactions, fewer shares were repurchased than were initially sold. To calculate the correct recognized capital gain or loss, students must understand and apply the wash-sale rules on a stock-by-stock basis. To calculate the total tax basis in shares held after the wash sale, students must calculate the adjusted basis for the repurchased shares as well as the basis in any shares remaining from the initial holdings. This enables students to gain an understanding of the impact of the wash-sale rules on the tax basis of the repurchased shares and reinforces that the same stock holdings can frequently have different tax bases.

Case 2

Case 2 is a problem-solving and decision-making case that requires students to use Excel to analyze mutual fund dispositions. Students are required to use various permissible methods to determine tax basis and, consequently, gain or loss when a taxpayer holds multiple lots of the same mutual fund. Specifically, the case requires students to calculate recognized capital gains or losses and the income tax liability on mutual fund sales using the (1) FIFO, (2) LIFO, (3) average cost, and (4) specific identification methods. Students are also required to use their calculations to make various decisions. Students receive an Excel template to complete this case.

The case informs students that they have sold a substantial number of mutual fund shares, and these shares were purchased in various lots over the past three years. Students receive information about the mutual fund holdings, including the date purchased, the number of shares purchased, and the purchase price for each lot. Students use this information to calculate the total capital gain or loss using the four methods listed above. Students must also calculate the income tax liability associated with the capital gain or loss under each method.

In addition to ensuring that students can use Excel to perform calculations, the case reinforces students’ decision-making skills. The specific identification method allows taxpayers to choose which shares they sell rather than following a predetermined formula. With an overall goal of current tax minimization, students must understand that selling shares with the highest basis will result in the smallest gain or largest loss, and the least amount of tax. Students must use this understanding when applying the specific identification method to correctly determine which shares should be identified to minimize tax.

The case requires students to use their calculations and decision-making skills to answer several questions. First, students are asked what method they would choose based on their calculations. To answer this question correctly, students must apply an overall goal of tax minimization to their calculations and select the method that results in the least amount of income tax. Next, students are asked which method they would choose assuming they have a capital loss carryforward of $20,000 and that they do not expect to have any capital gains in the next seven years. To correctly answer this question, students must recognize that because of the time value of money, tax savings now are worth more than tax savings in the future. Students must also be able to apply this concept to their calculations and recognize that using as much capital loss as possible in the current year results in a higher present value of tax savings than would carrying the capital loss forward and using it at $3,000 per year.

Finally, the case tests students’ understanding of the interplay between the amount of a capital gain and the differential tax rates that apply to long-term versus short-term capital gains. Students write a minimum of two sentences to explain why, if long-term capital gains are taxed at lower preferential rates than short-term gains, they might prefer to sell shares that result in short-term gains rather than shares that result in long-term gains. To answer this question correctly, students must think through the calculation of income tax (Income tax = Tax rate × Tax base). Students must understand that, because of the impact of tax base on the computation of income tax, lower tax rates do not always result in lower income taxes.

Case 3

Case 3 is a problem-solving and decision-making case, requiring students to use Excel to provide tax planning advice related to stock sale transactions. The case requires students to assume the role of tax adviser to a taxpayer who is interested in selling his stock holdings in the most tax-efficient manner. Students must calculate the present value tax cost of various alternate stock sale options and advise the client of the best alternative. Students also answer additional tax planning questions. This case allows students to demonstrate their ability to work with Excel by requiring them to generate their own worksheets.

The case informs students that their client holds stock in three corporations: Pulaski Inc., Roan Corp., and Harper Inc. The client is interested in selling all of these stocks. The client wants to minimize his present value tax cost and is willing to sell all of the stock this year, some of the stock this year and some of the stock next year, or all of the stock next year. Students receive details of the taxpayer’s stock holdings, including tax basis, fair market value (FMV), and holding period. Students use this information to calculate the total capital gain or loss, assuming that (1) all three stocks are sold in the current year; (2) the Pulaski and Harper stocks are sold in the current year and the Roan stock is sold in the subsequent year; (3) the Pulaski and Roan stocks are sold in the current year and the Harper stock is sold in the subsequent year; or (4) all three stocks are sold in the subsequent year. Students must calculate the income tax liability associated with the capital gain or loss for each scenario and the present value of each income tax liability. Students are told to assume a 6% rate of return and that tax rates are constant for the current year and the subsequent year.

In addition to using Excel to perform basic calculations, the case reinforces students’ decision-making skills by asking them to answer several questions. First, students are asked, based on their calculations, which sale scenario they would advise the client to engage in. To answer this correctly, students must apply the client’s goal of tax minimization to their calculations, selecting the sales scenario that has the lowest present value tax cost. Next, students are asked to perform the same calculations as above assuming that Congress enacts new tax rates for ordinary income and capital gains for the subsequent year. This question helps students understand the implications of tax rate changes on tax planning and may provide an opportunity to discuss proposed tax law changes.

Finally, students are reminded that nontax factors may also be important when making tax planning decisions. Students are asked to provide at least two nontax factors that the client might consider when deciding how to time the stock sales. To answer this question correctly, students must put themselves in the position of the taxpayer and think through factors that could affect the need to sell the stocks now or later, such as expected future fluctuations in stock prices, expected increases in transaction costs, or current or future cash flow needs.

Variations

Several modifications can be made to each of the cases, lending flexibility in the timing of offering the cases and the learning objectives. While Cases 1 and 2 include Excel templates, instructors can opt to not provide them and instead have students create their own Excel schedules. Also, instructors could remove the “please review” indicators provided in the templates. Doing this may require a different level of student effort for successful completion. Generally, the design of these cases makes them appropriate to being assigned after a class discussion on the related topics. Educators can also incorporate a research element into the case study by having students research related topics (e.g., wash-sale rules and permissible accounting methods for mutual fund basis calculations). Lastly, educators can modify any of the elements of the case (e.g., making the taxpayer a corporation rather than an individual, changing tax rates, modifying holding periods, modifying basis, etc.) to incorporate further learning objectives.

Microsoft Excel is currently rolling out a new feature that allows the Excel file to pull real-world data from stock prices and funds (see Microsoft’s “Get a Stock Quote” page at support.office.com. It may be an interesting learning experience for students to use this feature by, for example, requiring them to use it to find the FMV of stock on a specified date rather than providing them with this information in the case materials.

Excel tutorials

While most students will have some experience with Excel before taking an introductory tax class, some may need help with the Excel portion of these cases. Free, easy-to-follow tutorials are available online at Microsoft Office support or other sites, such as GCF LearnFree.org (edu.gcfglobal.org/en/excel2016). A full Excel course that covers both beginner and advanced level topics is also available for free at Excel Exposure (excelexposure.com). In addition, various online certifications are available for students seeking accreditation in Microsoft Excel. This type of accreditation may be viewed favorably by potential employers.

Building essential skills

The above cases are designed to enhance students’ understanding of how technology, specifically Excel, can efficiently and effectively assist them in analyzing tax issues. The cases require students to perform various analyses related to stock/mutual fund investments and to use their analyses to make decisions and/or provide recommendations to a client. By incorporating Excel into each case, students get practical experience using an important tool. Because the CPA Exam is now emphasizing these types of higher-order skills, these cases should also provide benefits beyond enhancing students’ Excel skills.

The cases offer the opportunity to involve local practitioners in the classroom or at extracurricular accounting events to further discuss or illustrate the use of Excel and similar tools, such as Alteryx, as well as data visualization software, in tax practice. Practitioner involvement reinforces students’ appreciation for Excel and related tools as essential tax planning and compliance resources.