Loan Calculator & Amortization Schedule in Excel: Flat vs Reducing Rate and APR

When a bank quotes 4.5%, the key question is whether it is a flat rate on the full amount or a reducing rate on the balance. The difference can nearly double the interest you pay.

This sheet calculates the payment and interest both ways by changing one cell, and works out the effective annual rate (APR) that reveals the real cost, with a dated repayment schedule of up to 30 years.

Who is it for?

• Anyone comparing personal or car finance offers
• Employees checking how the term affects interest
• Accountants and financial planners

What is inside the sheet?

• Simple inputs: amount, rate, term, interest method, first payment date
• Monthly payment, total paid and total interest
• Effective annual rate (APR)
• A schedule of up to 360 payments: balance, interest, principal
• A note on the gap between flat and effective rates

How to use it

1. Enter the loan amount, annual rate and term in years.
2. Pick the interest method: flat or reducing.
3. Enter the first payment date.
4. Switch between methods to see the difference in interest and APR.

Formulas used

• Flat: interest = amount × rate × years, payment = (amount + interest) ÷ months
• Reducing: payment = PMT(rate ÷ 12, months, amount)
• APR = RATE(months, payment, amount) × 12

Worked example from the sheet itself

A 150,000 loan at 4.5% for 5 years:

• Flat rate: payment 3,062.50, total interest 33,750, effective rate about 8.29%.
• Reducing rate: payment 2,796.45, total interest about 17,787. So a 4.5% flat rate is roughly an 8.3% reducing rate.

Tips for best results

• Compare bank offers by APR, not by the quoted rate.
• Add admin fees to the cost when comparing.
• Try a shorter term to see how much interest you save.

Frequently asked questions

What is the difference between flat and reducing rates?

A flat rate charges interest on the original amount for the whole term; a reducing rate charges only on the remaining balance, so interest falls with each payment.

Does it handle early settlement?

No, but the schedule shows the remaining balance after each payment, which is the basis for early settlement.

Will it match the bank schedule?

Very closely; small differences can come from rounding or fees.

Download the sheet

Press the download button at the top of the page to get the Excel file. It includes a full guide sheet, yellow input cells and formulas protected from accidental edits. The sheet is also available in Arabic from the language switcher.

For comparison and planning only; the financing contract and bank schedule are the final reference.