Royalties calculator for pyramidal income

Hello i need to do a simple., i think, calculation on royalties credit for agents
We have some agent, and each agent has its own subagents..total 3 levels of agent
We need to calculate the royalties for each agent considering that of course if the dependancies. So when the agent in the bottomline (the third) will make a sell, the uppe rline eill ge some royalties. When the agent on second line will make a sell, he and the first line willget royalties, when the top line make a sell only he will get royalties
Every month we have a report and we insert income generated for each agent, and we need to calculte the amount of royalties for each agent in th esystem.
With possibilities to query the db to see how much an agent has gained within a certain interval of time
To me it looks quite easy to do, but it is long time i don't use acces and start gain will be hard, same for excel
Do you know of any templates available (may be to be customizable with some edit) or software ready (online or not) that does this?

edit
i add more details
the core is to estabilish the relation within the agents
So we have 3 level of agent, that can be identified with a single letter code..A, B, C ...ecc
The the second level, they are subagent of the 1st level, that can be identified with 2 letter codes (the forst being their "pather agent" so AA, AB, AC..or BA, BB, BC..or CA, CB, CC..and so on
then the third level that as well as the second will be like AAA, AAB, AAC or ABA; ABB or ACC...and more BAA, BAB, BAC.
and so on..i think this is clear
Now at the end of the month we recevi a report for all the income generated from each agent
The 1st level agent wil take the whole royalties, the 2nd level willshare with the 1st, and the 3rd level will share with 2nd and 1st
The amonut of percentage is to be defined



thank you

Thread started in wrong section - continue here: https://www.excelforum.com/excel-gen...al-income.html