Best way to generate subtotal array from total

Question

This is a bit of a contrived problem, but a real one for our industry.

Our customer's rent hotel rooms and for hotel accounting reasons, room charges are added to the folio each night, not all at once. I've been working on fixing our flat rate calculation module, which is completely broken.

An example of the problem is - Customer agrees to pay $376 for a 30 night stay. However, $376 is not put on the books 1 time, it's ($376/nights) put on the folio for each night's stay.

So, given a desired Total for the entire stay, a number of nights, and a tax rate - how would we get a list of subtotals, when taxed and rolled up, that will equal the Total. Our existing solution has been to require a manual adjustment by hotel staff, which is error prone and burdensome. We'd like to have the prices distributed as evenly as possible, to keep accounting reports as close to accurate as possible.

It's kind of messy right now, and I can't get it to get the Total with the correct number of nights. I feel like there should be an algorithm for this, and I'm just not aware of it.

What I'm currently working on involves this tail-recursive function,

function iterationRate($amount, $days, $tax, $ary=array()){
    $amount_left = $amount;

    // we have subtotals already, subtract these from the desired total
    if(count($ary) > 0) {
        $amount_left = bcsub($amount, sumWithTaxes($ary, $tax));
    }

    // make sure it's a valid currency amount
    $amount_left = bcround($amount_left, 2);

    $tonights_rate = bcdiv($amount_left/$tax, $days);

    // prevent negative charges / credit
    if ($tonights_rate >= 0) {
        array_push($ary, bcround($tonights_rate, 2));
    }
    else {
        // remove an item from the array to give us more space
        array_shift($ary);
        $days = $days + 1;
    }

    if($days > 1) {
        return iterationRate($amount, $days - 1, $tax, $ary);
    }

    $test_subtotals = sumWithTaxes($ary, $tax);
    $diff = bcsub($test_subtotals, $amount);

    // if we don't have our amount, remove the diff from an item and try again
    if(abs($diff) > 0) {
        $firstnight = array_shift($ary);
        array_push($ary, bcsub($firstnight, $diff));
        return iterationRate($amount, $days, $tax, $ary);
    }

    return $ary;
}

entire test here: http://sandbox.onlinephpfunctions.com/code/9eb43e95fad866ef7198a0e2bfa778edf326e091

Example

$300 for 3 nights, tax rate of 16.25%

Finding a subtotal from the total and dividing by nights won't work: 300/(1 + taxrate) = $258.0645, rounded to $258.06 $258.06 / 3 = 86.02

86.02 * taxrate = 99.9983, rounded to 99.99. 99.99 * 3 != 300.

Subtotal array that does work, [86.02, 86.02, 86.04] 2 * (86.02 * taxrate) + (86.04 * taxrate) = total

el3ien · Answer 1 · 2017-02-28T10:47:18.347

I would do something more simple.
The calculate function does the job. The rest is just a test.

function calculate($amount, $days, $taxes){
    $per_day = round($amount/$days,2);
    $last_day = $amount-$per_day*($days-1);

    $total_taxes = $amount-$amount/$taxes;

    $per_day_taxes = round($per_day-$per_day/$taxes,2);
    $last_day_taxes = $total_taxes-$per_day_taxes*($days-1);

    return (object) array("per_day"=>$per_day,
                 "last_day"=>$last_day,
                 "per_day_taxes"=>$per_day_taxes,
                 "last_day_taxes"=>$last_day_taxes,
                 "total_taxes"=>$total_taxes);
}


function runTests($tests) {

    foreach($tests as $idx => $test) {
        $values = calculate($test->amount,$test->days,$test->taxes);
        printf("Test %d: Amount(%.02f) Days(%d) Tax(%.02f)\n", $idx, $test->amount, $test->days, $test->taxes);
        printf("Rates the first %d days (incl. taxes):| %.02f, where taxes are:| %.02f\n", $test->days-1, $values->per_day, $values->per_day_taxes);
        printf("Rate the last day (incl. taxes):      | %.02f, where taxes is :| %.02f\n", $values->last_day, $values->last_day_taxes);
        printf("Rates the first %d days (excl. taxes):| %.02f\n", $test->days-1, $values->per_day-$values->per_day_taxes);
        printf("Rate the last day (excl. taxes):      | %.02f\n", $values->last_day-$values->last_day_taxes);        
        printf("Total (excl. without) taxes:          | %.02f\n", $test->amount-$values->total_taxes);
        printf("Total taxes:                          | %.02f\n", $values->total_taxes);
        echo "=======================\n";
        echo "\n";
    }
}


$tests = [
    (object) array("amount"=>376, "days"=>22, "taxes"=>1),
    (object) array("amount"=>376, "days"=>22, "taxes"=>1.1625),
    (object) array("amount"=>1505, "days"=>25, "taxes"=>1.09),
    (object) array("amount"=>380, "days"=>22, "taxes"=>1.1),   
];

runTests($tests);

Result:

Test 0: Amount(376.00) Days(22) Tax(1.00)
Rates the first 21 days (incl. taxes):| 17.09, where taxes are:| 0.00
Rate the last day (incl. taxes):      | 17.11, where taxes is :| 0.00
Rates the first 21 days (excl. taxes):| 17.09
Rate the last day (excl. taxes):      | 17.11
Total (excl. without) taxes:          | 376.00
Total taxes:                          | 0.00
=======================

Test 1: Amount(376.00) Days(22) Tax(1.16)
Rates the first 21 days (incl. taxes):| 17.09, where taxes are:| 2.39
Rate the last day (incl. taxes):      | 17.11, where taxes is :| 2.37
Rates the first 21 days (excl. taxes):| 14.70
Rate the last day (excl. taxes):      | 14.74
Total (excl. without) taxes:          | 323.44
Total taxes:                          | 52.56
=======================

Test 2: Amount(1505.00) Days(25) Tax(1.09)
Rates the first 24 days (incl. taxes):| 60.20, where taxes are:| 4.97
Rate the last day (incl. taxes):      | 60.20, where taxes is :| 4.99
Rates the first 24 days (excl. taxes):| 55.23
Rate the last day (excl. taxes):      | 55.21
Total (excl. without) taxes:          | 1380.73
Total taxes:                          | 124.27
=======================

Test 3: Amount(380.00) Days(22) Tax(1.10)
Rates the first 21 days (incl. taxes):| 17.27, where taxes are:| 1.57
Rate the last day (incl. taxes):      | 17.33, where taxes is :| 1.58
Rates the first 21 days (excl. taxes):| 15.70
Rate the last day (excl. taxes):      | 15.75
Total (excl. without) taxes:          | 345.45
Total taxes:                          | 34.55
=======================

example on sandbox

The Totals != the desired total though. right? The Totals should be 376, 376, 1505, 380. — camflan, Feb 28 '17 at 00:18
@camflan oh sry for my ignorance. Is the desired total, with taxes? — el3ien, Feb 28 '17 at 00:21
yeah, the desired total should equal the subtotals, each plus tax, summed. Which, as it turns out - is far different than the sum of the subtotals + tax. I have been surprised by the difficulty of this one — camflan, Feb 28 '17 at 00:30
lol. I've been tweaking and rewriting this over and over again. No worries, I hope you have a simple solution to this! — camflan, Feb 28 '17 at 00:37
I don't think so, your rate per day w/o tax is adding up to ~ the desired total — camflan, Feb 28 '17 at 00:44
The last test should equal 380 but, round(15.70*1.1, 2) * 22=> 379.94 :) — camflan, Feb 28 '17 at 03:11
@camflam dont round the numbers. use as many decimals as possible when calculating. And then printf will round it — el3ien, Feb 28 '17 at 03:22
@camflam so use %.09f or something in printf, if you want to doublecheck like that :) — el3ien, Feb 28 '17 at 03:27
@camflam ok, as said before, my ignorance might have oversimplified the problem. But I will fix this tomorrow then, when I get how creditcard charges work :) — el3ien, Feb 28 '17 at 03:33
haha, sorry - wasn't trying to be dismissive, though I think it can read that way. I just meant that I can't charge a card for less than a cent, so I have to have values that are .02f at most. I think I have another idea, I'm going to try it and report back! thanks :) — camflan, Feb 28 '17 at 03:37
@camflam that said, you shall never round untill the end result — el3ien, Feb 28 '17 at 03:37
@camflam actually I get what you mean now after looking at your example. You need 2 decimal payments, and in some cases its impossible to have all charges to be the same. :D I will fix it after sleep :) — el3ien, Feb 28 '17 at 03:46
@camflan finally after I understood the issue :) I think we are there. So the calculate function I made, does the work. — el3ien, Feb 28 '17 at 10:33

score 0 · Answer 2 · answered Feb 28 '17 at 03:31

Difference between tax of sum and sum of taxes is inevitable. Don't even it up with other inconsistencies (price).

There are rules for order of tax calculation depending on country. I think that in most countries both methods are allowed and rounding diffrences are tolerated both ways - you need to check anyway. In this case it should be decided by your client or even better his accounting department (you might ask about the rules).

If you want to be precise in total tax then calculate it using sum as base (also there are two methods possible since you can use gross or net sum for it) - only calculation base will have accurate sum of its column.

If you want to be precise in sum of all columns then you'll lose accuracy of total tax rate.

Tips:

if you don't use any "money format" library then internally represent every money/price value as an integer (typecast instead of rounding) and format it for display only.
Don't calculate with tax rate both gross/net and tax - make second value derive as subtraction/addition of base and calculated value.

Thanks! How would you suggest handling partial cents after using a tax rate such as .1625 (16.25%)? I ended up having to round these off anyways, so I stopped working with integers exclusively because it seemed like I was fighting it anyways. Our calculations follow tax standard, but I'll investigate that further, it might give us some wiggle room that will help out. — camflan, Feb 28 '17 at 03:43

score 0 · Answer 3 · answered Feb 28 '17 at 04:11

Ok, so I was making this far too complicated. I believe this is now the correct way to do it, just a simple loop counting up each night until we hit our amount.

function iterationRate($amount, $days, $tax, $ary=array())
{
    $nightly_base = floor(bcdiv(bcdiv($amount, $tax), $days));

    $rates = [];

    for ($i = 0; $i < $days; $i++) {
        array_push($rates, $nightly_base);
    }

    $idx = 0;
    do {
        $rates[$idx] = $rates[$idx] + 0.01;
        $idx = ( ($idx >= count($rates) - 1) ? 0 : ( $idx + 1 ) );
    } while ($amount > sumWithTaxes($rates, $tax));

    return $rates;
}

http://sandbox.onlinephpfunctions.com/code/735f05c2fa0cfa416533f674bee1b02b247f9dd6

Best way to generate subtotal array from total

Example

3 Answers3