Rate of Return Calculator

I am an investment consultant who is using the services of a group of mathematicians who have developed an algorithmic trading system that I will call “Pro-Traders”. The Pro-Trader service has for the most part been able to successfully predict small movements (either up or down) in the price of gold contracts on a daily business day basis. Most days, the Pro-Traders only execute one or two trades which usually results in a small percent of profit from the sale or the short sale of gold contracts. They usually only hold a contract for a matter of a few seconds to a few minutes and they usually only buy and sell one or two contracts per day, when trading opens on the European markets. They normally make money if the price goes up or the price of gold goes down. I am interested in having a Pivot Table template program developed that will be able to analyse the last 14 month-end investment results incl May 2022. I would also like to have the capability of projecting future estimated results based on being able to enter and or change some of the assumptions. The Pivot Table logic would be based on but not limited to the following explanations: 1. Each investor opens their own Pro-Trade account and deposits funds into their account. A percentage of each deposit is deducted to pay for the set-up fees which are usually in the 4% range with the fees generally covering the cost of converting their deposit(s) into USDTs (also called Tether currency). The remaining approximately 96% is deposited into the investor’s Pro-Trade account which is used by the Pro-Traders to make small daily profits from buying and selling contracts. 2. On the last day of each month, the total gross value of the investor’s Pro-Trade account is determined with the Pro-Traders then taking their share of the profits. The amount of the month’s increase in value over the previous month’s value is determined with the Pro-Trade group keeping 50%. The remaining 50% is split according to the agreed upon terms set out below. However, the program must have the ability to change the percentages that determine each party’s share as they may change in the future. There are two pools of funds that need to be accounted for on an ongoing end of the month basis. The first pool is owned by the investor and the second pool is owned by me, Ted. 3. There are potentially two amounts that the investor’s pool earns at the end of each month. The first amount (the “Priority Income”) is an amount equal to up to the first 5% earned on the end of the month account value, excluding the amount kept by the Pro-Traders. The amount earned above the 5% Priority Income is called the “Excess Income”. The Excess Income for the time being will credit the investor’s account equal to 65% of the amount exceeding the 5% Priority return with the other 35% deposited into Ted’s account. These percentages may change in the future. For example, if the investor’s month-end value before splitting the month-end earned income is $100,000 and the Pro-Traders earned 20%, the investor would earn the first 5% and the remaining 15% would be shared on the 65%/35% basis. The tricky part is that each month, both accounts are compounding so the income amounts are earned based on the increased month over month amounts. The attached excel file has the month-end gains or losses and attempts to allocate the investor’s income amounts where the month end earnings need to be split based on the above. However, I have not been able to create a spreadsheet that properly splits the monthly account value increases and then allocates the splits and then compounds the ongoing month over month increases for both accounts. An accountant friend of mine suggested that the best way to track and show the allocated returns was to use Pivot Tables. The selected Pivot Table programmer has to agree and insure that the Pivot Table template to be developed will remain my confidential and proprietary software program.