I want to calculate a percentage of profit where the percentage is an expense and therefore affects the profit.
Let's assume:
Revenue 100,000
Expenses 50,000
Profit Percentage 50%
Normally the Profit Percentage would be equal to 25,000, however this would result in expenses rising from 50,000 to 75,000, which in turn lowers the profit percentage to 12,500. This seems circular but there is a midpoint value where this equals out. In the above example it is 16,666.67. In other words this is 50% of profits when this amount is also an expense.
How can I calculate this using a spreadsheet as opposed to trial and error?
Best Answer
Google Sheets now support calculations based on circular references, if the users allows them via the menu "Settings > Calculation". These are resolved by an iterative computation, which is limited by the number of steps and desired precision.
Source: New iterative calculation settings and more in Google Sheets