Basic Theory

When adding credit card fees and taxes to an invoice, the goal is to ensure that the total amount paid by the customer covers the original invoice amount, credit card processing fees, and any applicable taxes on those fees. This often requires a bit of reverse calculation.

Procedures

1. Determine the original invoice amount (A).

2. Identify the credit card processing fee rate (F) as a percentage.

3. Identify the tax rate (T) on the credit card fee as a percentage.

4. Use the following formula to calculate the total amount (X) to include on the invoice:

X = A / (1 - F - (F * T))

Scenario with Real Data

Let’s assume the original invoice amount is $1,000, the credit card processing fee is 3%, and the tax rate on the credit card fee is 5%.

    A = $1,000
    F = 3% (or 0.03)
    T = 5% (or 0.05)

The calculation would be:

X = 1000 / (1 - 0.03 - (0.03 * 0.05))
     = 1000 / (1 - 0.03 - 0.0015)
     = 1000 / 0.9685
     = $1,032.47

The total amount to include on the invoice to cover the original amount, credit card fee, and tax on the fee is $1,032.47.

Excel Table Explanation

You can set up an Excel table to perform these calculations as follows:

The formula in the Total Amount cell (D2) calculates the amount to include on the invoice.

Alternative Approaches

1. Flat Fee Addition: Instead of calculating the fee based on a percentage, add a flat fee to cover processing and tax costs.

2. Customer Absorbs Fees: State in your terms that the customer pays any additional processing fees separately from the invoice.

Perpetuity Calculation in Excel

Basic Theory

A perpetuity is a type of annuity that pays a constant amount indefinitely. The formula to calculate the present value (PV) of a perpetuity is:

PV = C / r

Where:

• C = Cash flow per period
• r = Discount rate (interest rate per period)

Procedures

1. Determine the annual cash flow (C).

2. Identify the discount rate (r).

3. Apply the perpetuity formula to find the present value.

Scenario with Real Data

Let’s consider a perpetuity that pays $1,000 annually with a discount rate of 5%.

    C = $1,000
    r = 5% (or 0.05)

The calculation would be:

PV = 1000 / 0.05
     = $20,000

The present value of this perpetuity is $20,000.

Excel Table Explanation

You can set up an Excel table as follows:

The formula in the Present Value cell (C2) calculates the present value of the perpetuity.

Alternative Approaches

1. Growing Perpetuity: If the cash flow grows at a constant rate (g), the formula becomes:

PV = C / (r - g)

2. Variable Discount Rate: Adjust the discount rate periodically based on market conditions.