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.
- Convert AER to monthly rate: r = 8 / 100 / 12 = 0.0066667
- Number of payments: n = 5 × 12 = 60
- Compute (1 + r)^(−n) = (1.0066667)^(−60) ≈ 0.67121
- Denominator: 1 − 0.67121 = 0.32879
- Monthly payment: M = 20000 × (0.0066667 / 0.32879) = 20000 × 0.020276 ≈ $405.53
- Total paid: 405.53 × 60 = $24,331.80
- 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
- QNB Car Loan Payment Calculator and Guide$405.53
- VA Down Payment Assistance Calculator and Guide$15,000.00
- Car Loan Payment Calculator and Formula$405.53
- Low Down Payment Home Loans: Costs and Trade-Offs$405.53
- Down Payment vs Advance Payment: Key Differences
- DIB Bank Car Loan Calculator and Payment Guide$405.53