How to Calculate the True Total Cost of Credit in Excel

The APR (Annual Percentage Rate) indicates the actual interest rate on a credit loan. Previously, this criterion was called the effective interest rate. The parameter takes into account not only the principal amount of the debt and interest but also almost all additional payments made by the borrower according to the terms of the loan agreement (commissions, credit card fees, insurance contributions, and premiums if insurance affects the terms of the loan). Registration fees, penalties, fines, and other payments not affecting the size and conditions of obtaining a loan are not considered. Let's see how to calculate the APR using Excel tools.

 

Formula for Calculating the Total Cost of Credit

For the calculation of the Total Cost of Credit, legislators have established a formula that is used in several countries to find the Annual Percentage Rate (APR).

The formula itself:

APR = i * CBP * 100.

  • APR - total cost of credit.
  • CBP - the number of base periods in a calendar year. The duration of a calendar year is assumed to be 365 days. With a standard payment schedule with monthly payments on the annuity system, CBP = 12. For quarterly payments, this indicator will be 4. For annual payments - 1.
  • i - the interest rate of the base period in decimal form. It is found by trial and error as the smallest positive value of the following equation:
Equation of the formula

Let's break down the components:

  • Cash Flowk - the amount of the k-th cash flow under the loan agreement. The amount provided by the bank to the borrower is included in the cash flow with a "minus" sign. Regular payments under the credit agreement are with a "plus" sign.
  • m - the number of payments (the number of amounts in the cash flow).
  • ek - the period expressed in parts of the established base period, calculated from the end of the qk period to the date of the k-th cash payment;
  • qk - the number of base periods from the date of loan disbursement to the k-th cash payment;
  • i - the interest rate of the base period in decimal form.

Let's demonstrate the calculation with an example.

How to made a Credit Calculator in Excel

The borrower takes $100,000 on 7/1/2025 at an annual interest rate of 19%. The loan term is 1 year (12 months). The payment method is annuity. The monthly payment is $9216.

Enter the input data into the Excel table:

Loan conditions Function for calculating the loan principal Function for calculating monthly interest

First, we need to calculate the interest rate of the established base period (i). In Excel, this can be done using the PV function. Represent the loan payments as a cash flow:

Annuity payments on the loan.

In our example, it turned out that i = 1.58% (0.01584). This is the monthly amount of the APR. Now we can calculate the annual total cost of the loan.

The formula for calculating the APR in Excel is simple:

Example.

For the cell with the value, a percentage format is set, so there is no need to multiply by 100%. We simply found the product of the loan term and the interest rate of the base period.

Calculation by the new formula showed the APR equal to the contractual interest rate. However, in this example, the borrower does not pay additional amounts to the lender (commissions, fees). Only interest.

Let's consider another example with additional expenses.

Additional Commission Expenses

The cash flow will change accordingly. Now the borrower will receive $99,000 in hand due to the fee, and the monthly payment will increase by $50 due to the fee.

Increase in the total cost in percentages

download file Download credit calculator for calculating true total cost of credit in Excel

The interest rate of the base period and the total cost of the loan have significantly increased to 22% - cell B10!

This is understandable because the borrower, in addition to interest, pays a one-time commission to the lender and a service fee for the loan. Moreover, the fee is monthly. Therefore, there is such a noticeable increase in the APR. Accordingly, the cost of the credit product will be more expensive. Additional bank commissions may include other expenses, such as:

  • loan insurance;
  • additional account servicing fee;
  • transactions;
  • costs for early repayment;
  • recalculation of the payment schedule, etc.

Therefore, it is always essential to consider the total cost of the loan in business planning expense items. After all, if taking a loan to increase business productivity, it is crucial to maintain the level of debt burden. And for other purposes, a loan may not be necessary at all.


en ru