calculate-loan

01 Loan payments

Projection Report for Bank Loan in Excel

Calculator No. 01-23251 · Updated · Formula and assumptions shown below

Monthly payment $405.53
Total interest
$4,331.67
= Monthly payment × loan term × 12 − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Monthly payment ($), Total interest ($)
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

A bank loan projection report in Excel is a financial model that estimates future loan payments, interest costs, and outstanding balances over the life of the loan. You build it by entering the loan amount, interest rate, and term, then use Excel formulas to calculate monthly payments and track how each payment splits between principal and interest. The result is a schedule (often called an amortization table) that shows the loan balance declining month by month, which banks and borrowers use for budgeting, forecasting, and comparing loan offers.

Formula

The core of any loan projection is the standard amortization formula for a fixed-rate, level-payment loan:

Monthly Payment (PMT) = P × [r × (1 + r)^n] / [(1 + r)^n − 1]

Where:

  • P = principal loan amount (in dollars)
  • r = monthly interest rate = annual rate / 12 (as a decimal, e.g., 8% / 12 = 0.006667)
  • n = total number of monthly payments = term in years × 12

This formula gives the constant payment that fully repays the loan by the end of the term. Each month, the interest portion is the current balance times r; the rest of the payment reduces the principal. The balance after k payments is: B(k) = P × (1 + r)^k − PMT × [((1 + r)^k − 1) / r].

Worked Example

Using the calculator defaults: loan amount $20,000, APR 8%, term 5 years.

  1. Convert APR to monthly rate: r = 8 / 1200 = 0.0066667
  2. Total payments: n = 5 × 12 = 60
  3. Plug into the formula: PMT = 20000 × [0.0066667 × (1.0066667)^60] / [(1.0066667)^60 − 1]
  4. Calculate (1.0066667)^60 ≈ 1.48985
  5. Numerator: 20000 × 0.0066667 × 1.48985 ≈ 198.646
  6. Denominator: 1.48985 − 1 = 0.48985
  7. PMT = 198.646 / 0.48985 ≈ 405.53

So the monthly payment is $405.53. Total paid over 60 months = 405.53 × 60 = $24,331.80. Total interest = $24,331.80 − $20,000 = $4,331.80, matching the calculator output.

Assumptions

  • Fixed annual interest rate (APR) that does not change over the loan term.
  • Payments are made at the end of each month (ordinary annuity).
  • Interest compounds monthly at APR / 12.
  • The payment is rounded to the cent; the schedule uses that rounded payment.
  • Fees, taxes, insurance, and prepayment penalties are not included.

How to Build a Projection Report in Excel

Creating a projection report in Excel is straightforward once you know the formulas. Here are the steps:

  1. Set up inputs: In cells, enter the loan amount, annual interest rate (as a percentage), and term in years. Label them clearly.
  2. Calculate monthly payment: Use the built-in PMT function: =PMT(rate/12, term*12, -loan_amount). The negative sign makes the payment a positive number.
  3. Build the amortization schedule: Create columns for Payment Number, Beginning Balance, Payment, Interest, Principal, and Ending Balance.
  4. Fill the first row: For month 1, Interest = Beginning Balance × (rate/12), Principal = Payment − Interest, Ending Balance = Beginning Balance − Principal.
  5. Drag down: For each subsequent row, Beginning Balance equals the previous Ending Balance. Repeat the formulas for the full term.
  6. Add summary rows: Sum the total interest and total payments to see the full cost of the loan.

You can also add a chart of the balance declining over time to visualize the projection.

Why Use a Projection Report?

A projection report is useful for several reasons:

  • Budgeting: Knowing the exact monthly payment helps you plan cash flow.
  • Comparing loans: You can change the rate or term to see how the payment and total interest change.
  • Early payoff planning: You can add extra payments to see how much time and interest you save.
  • Business forecasting: Companies use loan projections to predict future debt obligations and interest expenses.

Banks also use similar models to assess a borrower’s ability to repay, but they typically include more factors like income and other debts.

Common Mistakes to Avoid

  • Using the annual rate instead of monthly: Always divide by 12. Forgetting this makes the payment wildly wrong.
  • Rounding too early: Keep full precision in intermediate steps; round only the final payment to cents.
  • Ignoring payment frequency: If payments are bi-weekly or quarterly, adjust the rate and number of periods accordingly.
  • Forgetting fees: A projection based only on principal and interest misses origination fees or insurance, which can be significant.

Limitations of the Model

The standard amortization formula assumes a fixed rate and constant payments. It does not handle:

  • Variable or adjustable rates that change over time.
  • Balloon payments or interest-only periods.
  • Prepayment penalties or tax deductions.
  • Fees, insurance, or escrow amounts.

For those situations, you need a more complex model or a specialized calculator. Also, the formula gives the exact payment for a fully amortizing loan; if you make extra payments, the schedule changes.

Frequently Asked Questions

What is the difference between a projection report and an amortization schedule?

They are essentially the same thing. An amortization schedule is the detailed table of payments, and a projection report often includes that schedule plus summary totals and possibly charts. The terms are used interchangeably in most contexts.

Can I use Excel’s PMT function for any loan?

Yes, as long as the loan has a fixed rate and equal payments. For variable-rate loans, you would need to recalculate each period. The PMT function also assumes payments are made at the end of each period; use the ‘type’ argument for beginning-of-period payments.

How do I add extra payments to my projection?

In the amortization schedule, add a column for extra payment. Subtract the extra amount from the ending balance each month. You’ll need to adjust the number of rows because the loan will be paid off earlier. Excel’s goal seek can help find the new payoff date.

What if my interest rate is not annual?

Convert it to an annual rate first. For example, if you have a quarterly rate, multiply by 4 to get the annual rate, then divide by 12 for the monthly rate. Be consistent with the compounding period.

Why does my total interest in Excel differ from the bank’s quote?

Banks may use a different rounding method, include fees, or calculate interest based on a 360-day year. Also, if your payment is rounded to the cent, the final payment may be slightly different. Always check the lender’s disclosure for exact figures.

This calculator provides an estimate based on the inputs you enter. The lender’s official disclosure will give you the exact payment and total cost.

Related

Formula used
in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.

More in this section

The whole Loan payments section →