This question has a programming application, but I thought it would be more appropriate (and educational) to ask here first and get some basic understanding. So my use-case will refer to some software functionality, but I think it should all be more fundamentally mathematically. Let me know if SO is the better place if I'm mistaken...
So the software has calculated fields that allow admins to provide the formula. Generally the formulas point to another field, for instance:
Product Price Field = User input (validated to be numeric)
Commission Field = Product Price * .15
Or it can be more complex. Using the above example:
Units Sold = User input (validated to be a natural number)
Commission Field = Product Price * ifElse((Units Sold >= 5), (Units Sold * .3), .17) (meaning they get 3% on each unit per sale, tapping out at 15, but with an extra 2% so they don't feel like there is no incentive to sell 6 units, etc)
And calculated fields can be combined:
Discount = User input (validated to be within a range)
Sale Price = Base Price * Discount
Commission = Sale Price * ifElse((Units Sold >= 5), (Units Sold * .3), .17)
The one thing that the software doesn't tolerate (yet, I'm sure it will be offered eventually), is circular logic. So if you have something like:
Monthly Payment = Total Cost / Number of Months
Number of Months = Total Cost / Monthly Payment
Rather than allowing for either the payment or months to be entered to find the other value, it just rejects both as invalid.
Excel, I discovered, does the same thing unless you give it a "number of iterations" value, but, again, not yet offered in my scenario.
So instead, I set up all of the calculated fields to be either directly or eventually based on the number of months, and then wrote my own--external from the calculation engine--formula that allowed for them to input the total payment. In order to minimize the "external-ness" of this calculation, what I did was wrote a formula that takes the input monthly payment, derive the number of months (which is not calculated, as mentioned) and then plug in the number of months found so that all of the calculated fields fired on their own. So it looks kind of like:
monthly_payment = amount_entered; number_of_months = total_amount / monthly_payment; number_of_months_field = number_of_months; fire_calculations();
If it isn't clear already:
- The formula is not actually this simple (it uses several numbers)
- number of months is not the only constant in this equation, it's just the only one that isn't derived from other calculated fields.
This works really well, except for one problem... The admin has no control over my inverse function, and they plan to change how the monthly payment is calculated several times until they find one that they can stick with.
Okay, long-winded backstory now laid out, here's the fundamental question: Is there a basic formula / technique / principle for taking all of the constants I do have and taking the calculated value of the monthly payment (which is what is ultimately changing as they tinker with the various formulas), and from those numbers derive the number of months like I showed above without having any idea what the formula to get the monthly value is?
Something to keep in mind (if it's relevant) is that there is an overhead price (let's call it units_sold * unit_price) as well as a monthly fee (something like monthly_service_charge + monthly_processing_fee), so the formula is never going to be a simple
a * b = c so therefore
a = c/b type scenario.
Much shorter version of the question: is it possible to find
x if I know
y simply by knowing both
x is given? If that's not possible, then would having a constant
z make a difference?
// Extenally calculated from units * unit_price total_due; // Extenally calculated from discount_rate * calculated_total_due total_discounted; // Extenally calculated from various factors such as total_due, custom location, etc close_out_fee; // Extenally calculated from various factors such as total_due, custom location, etc setup_fee; // Externally calculated based on vendor chosen vendor_monthly; // Extenally calculated from various factors such as total_due, custom location, etc monthly_fee; // Calculated below or manually entered number_of_months; /* Calculated from above variables automatically or manually entered and then recalculated from derived number_of_months */ monthly_entered; // This could change but may be unnecessary to calculate. See next calculation for its use monthly_base = monthly_fee + vendor_monthly; /* Basically the monthly base is the "nut". If number less than base is entered, it would be impossible to ever pay down amount_due. Figured this out when I got a negative number of months while testing. */ monthly_payment = ifElse((monthly_entered <= (monthly_base)), monthly_base + 1 , monthly_payment); /* Finally, the number of months are calculated using the inverse of how the monthly payment is calculated when done by software (ie not entered by user) */ number_of_months = round((calculated_total_discounted + close_out_fee + setup_fee) / (monthly_payment - monthly_base)); /* If it's not obvious from above, the monthly payment formula used by software (and changeable for admin, unlike code above) is: */ monthly_payment = ( (calculated_total_discounted + close_out_fee + setup_fee) / number_of_months) + monthly_fee + vendor_monthly // Or, to use the monthly_base shorthand: monthly_payment = ( (calculated_total_discounted + close_out_fee + setup_fee) / number_of_months) + monthly_base
So I'm not sure if my anxiety/block is because of the monthly base (which would be impossible to know ahead of time which numbers count toward that) or if it's the fact that you add the monthly base to the overhead base after dividing by the overhead base (which I did not make a neat shorthand for).
If it's just an issue of not knowing what is overhead and what is monthly, I can make two calculated fields that figure both of those out ahead of time and train the admins that if they want the right numbers, they have to funnel their final calculations through those fields. This would, I think, simplify the equations down to:
monthly_payment_calculated = (overhead_amount/number_of_months) + monthly_base number_of_months = round ( overhead_amount / (monthly_payment - monthly_base) )
But it would be more-reliable and way cooler if I could just "know" the equation from knowing that given the numbers I do have and an arbitrary number of months that the monthly payment comes out to
x and therefor derive the number of months from any non-calculated
If it's the issue of the divide then add, then I could use some advice on either how to make that work or just knowing that it's not possible.
If it's just me overcomplicating things because I tend to do that when I understand just enough math to get confused, then by all means, the obvious solution is welcome along with mocking comments on how my liberal arts degree wasn't worth the paper it was printed on.