I had a case where I wanted to subtract the corporate tax from a gross profit. If you are familiar with taxes, you might realize that it is not as simple as taking a percentage and calling it a day. Instead, you must pay a certain percentage of tax on profit up to a certain threshold. Then, as that threshold increases, the percentage changes (normally upwards). To give you some idea, here is what I’m recognizing:
$0-$50,000 (15%) $50,001-$75,000 (25%) $75,001-$100,000 (34%) $100,001-$335,000 (39%) $335,001-$10,000,000 (34%) $10,000,001 - $15,000,000 (35%) $15,000,001 - $18,333,333 (38%) $18,333,334+ (35%)
So I tried writing a formula directly into a cell and found that it would have been ridiculously long – maintenance would have been near impossible. Instead, I came across how to write scripts in Google Sheets:
I learned that instead of the Excel/VisualBasic combo, it is now a Sheets/Javascript combo – not bad. At this point in my career, I now prefer Javascript over VisualBasic. Google provides how to get started writing a function:
Custom functions in Google Sheets
Taking this information, I turned that tax schedule into a function:
//
// Given a principal, this function will calculate the owed taxes
// based on the sliding schedule scale for United States 2015.
//
function TAXCORP15( taxable ) {
// People who make too much money can do their own taxes (thus, ignoring the final threshold of 35%).
if ( taxable >= 18333333 ) {
return 'TOO MUCH';
}
// This is the 2015 tax schedule.
schedule = [ [0,0.0], [50000,0.15], [75000,0.25], [100000,0.35], [335000,0.39], [10000000,0.34], [15000000,0.35], [18333333,0.38] ];
// Run through the schedule figuring out how each percentage applies, if at all.
owed = 0.0;
for ( i = 0; i < schedule.length-1; i++ ) {
// Only process if there is taxable money left to process.
if ( taxable <= 0 ) {
break;
}
// Grab the extremes. lo = schedule[i]; hi = schedule[i+1]; // Figure out the next taxable chunk. range = hi[0]-lo[0]+1; taxable_chunk = ( taxable < range ) ? taxable : range; // Increment the owed amount. owed += taxable_chunk * hi[1]; // Decrement the taxable amount. taxable -= taxable_chunk; } // Send back the total taxes owed. return Number( owed ); }
To call it, I simply go into a Sheet cell and type:
=TAXCORP(70000)