calculate-loan

01 Loan payments

AER Loan Budget Sheet: How to Use It

Calculator No. 01-25468 · 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 (AER)
8 %
Input
You enter
Loan amount ($), Interest rate (AER) (%), 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.

An AER loan budget sheet is a planning tool that uses the Annual Equivalent Rate (AER) to estimate the true yearly cost of a loan, including the effect of compounding interest. You enter the loan amount, the AER, and the term, and the sheet shows your monthly payment and total interest, helping you compare loan offers on a like-for-like basis. This article explains how to build and read such a sheet, what AER means, and how it differs from APR.

Formula

The calculator uses the standard level-payment formula for a fixed-rate loan. The monthly payment M is given by:

M = P × (r / (1 − (1 + r)^(−n)))

where:

  • P = loan amount (principal) in dollars
  • r = monthly interest rate = AER / 12 / 100 (as a decimal)
  • n = total number of monthly payments = term in years × 12

Total interest is simply the monthly payment times the number of payments, minus the principal: Total Interest = M × n − P.

This formula assumes a fixed interest rate for the entire term and that payments are made at the end of each month. It is the same formula used for most auto loans, personal loans, and fixed-rate mortgages.

Worked Example

Using the default values from the calculator above: loan amount $20,000, AER 8%, term 5 years.

  1. Convert AER to monthly rate: r = 8 / 100 / 12 = 0.0066667
  2. Number of payments: n = 5 × 12 = 60
  3. Compute (1 + r)^(−n) = (1.0066667)^(−60) ≈ 0.67121
  4. Denominator: 1 − 0.67121 = 0.32879
  5. Monthly payment: M = 20000 × (0.0066667 / 0.32879) = 20000 × 0.020276 ≈ $405.53
  6. Total paid: 405.53 × 60 = $24,331.80
  7. Total interest: 24,331.80 − 20,000 = $4,331.80

The calculator shows a monthly payment of $405.53 and total interest of $4,331.80, matching the manual calculation.

Assumptions

  • The interest rate is fixed for the entire loan term.
  • Payments are made monthly at the end of each period.
  • Interest compounds monthly at AER/12.
  • No fees, taxes, insurance, or other charges are included.
  • The AER equals the nominal annual rate; if the lender quotes a different rate, use that instead.

What Is AER and How Does It Differ from APR?

AER (Annual Equivalent Rate) shows the annual interest rate that accounts for compounding within the year. For example, if a loan charges 1% interest monthly, the AER is about 12.68% because each month’s interest is added to the balance and then earns interest itself. APR (Annual Percentage Rate) is a broader measure that includes certain fees and costs, expressed as a yearly rate. For loans, APR is often higher than the nominal rate because it includes origination fees or other charges. When comparing loans, APR is usually the more relevant figure because it reflects the true cost including fees, but AER is useful for understanding the pure interest compounding effect.

How to Build a Budget Sheet

A loan budget sheet typically includes the following rows:

  • Loan amount: the principal you borrow.
  • AER: the annual rate quoted by the lender.
  • Term: the number of years or months.
  • Monthly payment: calculated using the formula above.
  • Total interest: the extra amount you pay over the principal.
  • Total cost: principal + total interest.

You can create this in a spreadsheet by entering the formula in a cell, or use the calculator on this page. The key is to use the same AER for all loans you compare, so the comparison is fair.

How to Choose a Loan Using the Sheet

To compare loan offers, enter the details of each offer into the sheet and look at the monthly payment and total interest. A lower AER generally means lower total cost, but also check the term: a longer term reduces the monthly payment but increases total interest. For example, a $20,000 loan at 8% for 5 years costs $4,331.80 in interest, while the same loan for 10 years would cost about $9,299.60 in interest (monthly payment $244.16). The sheet helps you see the trade-off between affordable payments and long-term cost. Always consider your budget: the monthly payment must fit within your income after other expenses.

Common Mistakes and Misconceptions

One common mistake is using the nominal annual rate instead of the AER. If a lender quotes 8% nominal compounded monthly, the AER is about 8.30%, and using 8% will understate the interest. Another mistake is forgetting to convert the percentage to a decimal in the formula. Also, many people confuse AER with APR: AER ignores fees, while APR includes them. When comparing loans, use APR if fees are significant. Finally, rounding the monthly payment too early can cause small errors in the total interest; keep full precision until the final result.

Limitations of the Formula

The level-payment formula assumes a fixed rate and constant payments. Real loans may have variable rates, payment holidays, or balloon payments, which require more complex calculations. The formula also does not account for fees, early repayment penalties, or changes in the payment schedule. For adjustable-rate loans, the AER is only an estimate based on the current rate. Always read the loan agreement and consult the lender for exact figures.

Frequently Asked Questions

What is the difference between AER and APR?

AER focuses only on the interest rate and compounding, while APR includes fees and other costs. APR is generally higher than AER for loans with fees. When comparing loans, APR gives a more complete picture of the total cost.

Can I use this calculator for a mortgage?

Yes, the formula works for any fixed-rate loan, including mortgages, but it does not include property taxes, insurance, or PMI. For a mortgage, you would need to add those costs separately.

What if the loan has a variable rate?

The calculator assumes a fixed rate. For a variable-rate loan, you can use the current rate as an estimate, but the actual payment may change. Check with your lender for how often the rate adjusts.

How do I account for fees in the budget sheet?

Add the fees to the loan amount or adjust the AER to an APR that includes fees. The simplest method is to add the fee to the principal, but that assumes the fee is financed. Alternatively, use the APR in the AER field to get a more accurate cost.

Why does the total interest seem so high?

Interest accumulates over the life of the loan. A longer term or higher rate will increase total interest. For example, a $20,000 loan at 8% for 5 years costs about $4,332 in interest, but for 10 years it costs about $9,300. The monthly payment is lower with a longer term, but you pay more overall.

The result from this calculator is an estimate; the lender’s disclosure will provide the exact figures.

Related

Formula used
in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (AER) | % | 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 →