How to solve this tax problem with Excel?

Hi all

I would like to create an Excel file to solve this problem but I really suck at it and have no idea how to...

Any kindred spirits out there to help me with that? Any help would really be highly appreciated!

For information, I tried with ChatGPT but it is not helpful. It generates every time different answers so it is not reliable at all.


I am really not sure about the tax calculations for Scenario 1.
---

Starting capital: $100k
Annual growth rate of stocks: 10%
Annual growth rate of bonds: 4%
Bank annual fees: 0.20%
Tax on realized capital gains: 34%
Tax on unrealized capital gains: 0%

Asset allocation:

Scenario 1:
Stocks ETF: 80% / Annual fees: 0.14%
Bonds ETF: 20% / Annual fees: 0.10%
In this scenario, since stocks perform better than bonds, I will need once a year to sell stocks to buy bonds in order to rebalance the portfolio to the original allocation (80% stocks and 20% bonds). Each rebalancing will trigger taxes on the portion of stocks sold based on their average cost (not FIFO).

Scenario 2:
All-in-one ETF (80/20 - auto-rebalancing): 0.25%
No need to rebalance annually so no taxes triggered.

Question:
At the end of the 25th year, I sell all my positions. What would the final capital be, net of taxes, in both of these scenarios?


Here is my try: Calculation.xlsx

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.