Description: If mileage expenses are tax-free up to a certain cumulative distance per year and taxable beyond that threshold, you can manage this by creating a sub-mileage rate with a specific code. This means you keep the same rate for the sub rate, but assign a specific code to expenses that exceed the tax-free limit. This code makes it easy to identify and separate taxable mileage expenses from tax-free ones in the exports.

Example: considering a mileage rate of 0,2 euros per km. All reimbursements above 10000 km this year are taxable.

Description: For mileage rates of which x% is non-taxable and y% is taxable based only on the branch currency and branch amount of the expense, we can provide a custom export. In this custom export, there will be two important columns:

• Column non-taxable where the total mileage amount per expense is multiplied by x%
• Column taxable where the total mileage amount per expense is multiplied by y%

Example: Reimbursement is based on a mileage rate of 4,65 SEK per km, of which 2,5 SEK (54%) is non-taxable and 2,15 SEK (46%) is taxable. If a user creates a mileage expense for a journey of 100km:

• Total reimbursable amount: 465 SEK
• Non-taxable amount: 251,1 SEK
• Taxable amount: 213,9 SEK

Download the file example here

❗Please note that the calculation is based on the branch currency/amount of the expense. Different rates and currencies can’t be accommodated, and no extra customisations based on these factors will be available. 

It's possible to either handle this directly in your ERP or accounting tool or apply an Excel automation.

• More information about Excel automation

  To help you identify the non-taxable and taxable portions of your mileage expenses, you can set up an automated process in Excel. Here's how we can achieve this:

  Step 1: Data Overview:

  • Your Excel sheet contains columns for the date, total expense amount, mileage rate name, and distance driven for each expense entry.
  • You also have a separate table that lists each mileage rate name along with the maximum non-taxable rate per kilometre.

  Step 2: Setting Up Calculations:

  • Add two new columns to your expense sheet:
    • Column for Non-Taxable Amount.
    • Column for Taxable Amount.

  Step 3: Calculating the Non-Taxable Amount:

  • In the Non-Taxable Amount column, use a VLOOKUP formula to find the maximum non-taxable rate per kilometre for the mileage rate in the current expense entry, and then multiply it by the distance travelled.

  • Formula:

    =VLOOKUP(C2; 'Maximum Non-Taxable Rates'!A:B; 2; FALSE) * D2
    

  • This will look up the mileage rate from the current expense, find the corresponding maximum non-taxable rate from the provided table, and calculate the non-taxable amount.
  • Calculating the Taxable Amount:
    • In the Taxable Amount column, use an IF formula to determine if there is any excess over the non-taxable amount.

    • Formula:

      =IF(B2 <= E2; 0; B2 - E2)
      

    • This checks if the total expense amount is less than or equal to the non-taxable amount. If it is, the taxable amount is 0. Otherwise, it calculates the excess as the taxable amount.

  • Error Handling (Optional):

    • To ensure there are no errors if a mileage rate name is not found in the non-taxable rates table, we can wrap the formulas in IFERROR.

    • Updated Formula for Non-Taxable Amount:

      =IFERROR(VLOOKUP(C2; 'Maximum Non-Taxable Rates'!A:B; 2; FALSE) * D2; 0)
      

    • Updated Formula for Taxable Amount:

      =IFERROR(IF(B2 <= E2; 0; B2 - E2); 0)
      

    Optional: Automating the process with macros

    If you frequently receive this data and it follows the same format, you can automate these steps using a macro. A macro will calculate taxable amounts in one click. Here’s how:

    1. Enable macros in Excel.
    2. Add a VBA script to your workbook.
    3. Run the macro, and it will automatically populate the taxable amounts.

Description: Create two different per diem lists, one with the non-taxable amount and another with the taxable amount. Users will then get two per diem expenses for one day.

Example:

Reimbursement of per diems:

• Netherlands: 40 euro/day (35 NT + 5 T), 50% for arrival and departure day, overnight supplement of 100 euro/night (90 NT + 10 T), breakfast deduction of 20%

• Belgium: 50 euro/day (40 NT + 10 T), 50% for arrival and departure day, overnight supplement of 125 euro/night (100 NT + 25 T), breakfast deduction of 20%

   

• Per Diem List Setup

- Non-taxable per diem list:

- Taxable per diem list:

- How it looks in the expenses:

Description: For per diem rates of which x% is non-taxable and y% is non-taxable, we can provide a custom export based only on the per diem amount and currency of the expense. This is only possible when there’s a per diem rate without deductions or supplements. In this custom export, there will be two important columns:

• Column taxable where the total per diem amount per expense is multiplied by x %
• Column non-taxable where the total per diem amount per expense is multiplied by y %

Example: If you travel 3 days, you get 60 euros in total (= 3 days x 20 euros/day). The tax-free threshold, however, is 15 euros a day (75%), and the additional 5 euros a day (25%) is thus taxable.

❗Please note that deductions, supplements, or different rates can’t be accommodated, and no extra customisations based on these factors will be available. Calculation is based on the per diem currency/amount of the expense.

Download the example file here

It's possible to either handle this directly in your ERP or accounting tool or apply an Excel automation. 
 

• More information about excel automation 
  
  You can set up a process in Excel to automatically calculate the taxable portion of per diem expenses. This involves using a lookup table for maximum non-taxable amounts for each destination and applying formulas to determine the excess taxable amounts. Here's how you can do it:
   
• Step 1: Create a Table of Maximum Non-Taxable Amounts

1. Open your Excel file and create a new sheet called NonTaxableRates.

2. In this sheet, create two columns:

   • Column A: Destination Country/Region (e.g., "USA", "Germany"). Make sure these values match with the per diem region in your per diem list in Rydoo.
   • Column B: Maximum Non-Taxable Amount per Day for each destination.

   This table will serve as the reference for your calculations.

    

• Step 2: Add a New Column to Reflect the Non-Taxable Amount

1. Add your exported expenses in the workbook (let’s call this Expenses). Via Templates you can choose the format of the export.
2. Insert a new column in the Expenses sheet (e.g., "Non-Taxable Amount").
3. Use a formula to determine how much of each expense exceeds the maximum non-taxable amount:

   • In the first cell of the "Taxable Amount" column, enter the following formula:

     =IF(Expenses!B2 <= I2; 0; Expenses!B2 - I2)
     

   • Explanation of the formula:
     • Expenses!B2: The expense amount. This refers to the cell containing the total logged expense in the current row on the Expenses sheet.
     • I2: The non-taxable amount. This cell contains the maximum non-taxable amount for the expense's destination, which is retrieved or calculated separately.
     • IF:
       • The IF function checks a condition and returns one value if it is TRUE and another value if it is FALSE.
       • Logical test (Expenses!B2 <= I2):
         • This checks whether the expense amount in Expenses!B2 is less than or equal to the non-taxable amount in I2.
       • Value if TRUE (0):
         • If the expense amount is less than or equal to the non-taxable amount, the formula returns 0, indicating that there is no taxable amount.
       • Value if FALSE (Expenses!B2 - I2):
         • If the expense amount is greater than the non-taxable amount, the formula calculates the taxable amount by subtracting the non-taxable amount (I2) from the expense amount (Expenses!B2). This shows the excess amount that is taxable.
   • Drag this formula down the column to apply it to all rows.
      

• Step 3: Add a New Column to Calculate the Taxable Amount

1. Insert a new column in the Expenses sheet (e.g., "Taxable Amount").
2. Use a formula to determine how much of each expense exceeds the maximum non-taxable amount:

   • In the first cell of the "Taxable Amount" column, enter the following formula:

     =VLOOKUP(Expenses!G2; NontaxableRates!A$2:B$5; 2; FALSE)
     

   • Explanation of the formula:
     • Expenses!G2: The 'Per diem region' or 'destination' for which you want to find the non-taxable rate. This is the value in the current row of the Expenses sheet, indicating where the expense was incurred.
     • NontaxableRates!A$2:B$5:
       • The range where the VLOOKUP function searches for the value in Expenses!G2. This range is in the NontaxableRates sheet.
       • Column A contains the destination names or regions.
       • Column B contains the corresponding non-taxable amounts for those destinations.
       • The $ signs before the row numbers (A$2:B$5) make the range reference absolute, meaning it won't change when you copy the formula down to other rows.
     • 2:
       • The column index number that tells VLOOKUP to return the value from the second column of the specified range (NontaxableRates!A$2:B$5), which is column B (the non-taxable amount).
     • FALSE:
       • Specifies that VLOOKUP should find an exact match for the value in Expenses!G2. If it doesn't find an exact match, it will return #N/A.
   • Drag this formula down the column to apply it to all rows.
      

• Step 4: Verify the Results

1. Check the "Taxable Amount" column to ensure the calculation is correct:
   • If an expense exceeds the maximum non-taxable amount, the excess is shown as taxable.
   • If an expense is within the non-taxable limit, the taxable amount will be zero.

Optional: Automating the Process with Macros

If you frequently receive this data and it follows the same format, you can automate these steps using a macro. A macro will calculate taxable amounts in one click. Here’s how:

1. Enable macros in Excel.
2. Add a VBA script to your workbook.
3. Run the macro, and it will automatically populate the taxable amounts.

You can set up two separate categories and let the end user/approver/controller/finance split the expense into the deductible and non-deductible part. As a reminder, an expense rule and itemised split categories can be configured.

If splitting into different categories is not an option, you can also explore the same principle with a custom field ‘VAT deductible’ and list items ‘yes’ and ‘no’, for example. This custom field would also be limited to the relevant category or categories.

For further details, reach out to your CSM or at support@rydoo.com

It's possible to either handle this directly in your ERP or accounting tool or apply an Excel automation.