Calculating APR on an amortization

I’m trying to find a process for this but most seem to treat it as a simple interest problem e.g $100000 loan at 8% interest per annum + 2.5% fees => 8.5% APR. But as we know since amortization is like compounding in reverse that the real interest rate paid is not the same as the nominal rate.

My solution has always been to take the total interest paid plus the fees and divide by the term but I am pretty sure that is wrong and I get absurdly low APRs.

I saw this technique today: Find the payment on the loan including fees and points using the amortization formula. Now with just the principal, find the interest rate that gives you that same payment. Seems right to me.

So what is the right way to calculate the APR on an amortized loan?

To calculate the APR for a loan with points, go through the following steps:

Add any up-front payment to the loan amount to get an Adjusted Balance.
Find the monthly payment on the Adjusted Balance.
Return to the original loan amount, and find the interest rate that would result in the monthly payment found in step 2. This is the APR.

A proper amortization table will look something like

Prinicpal Balance before payment | Payment | Interest | Principal | Principal Balance after payment

The first entry of next line equals the last entry of the current line. The second entry is always the same except for usually the very last one. The third entry should be a constant* percentage (the interest rate) of the first entry. The fourth entry is the second minus the third. The fifth entry is the first minus the fourth. Repeat until the last entry is zero.

If you get charged fees in addition to the money that you receive, just treat it as additional principal. You’re effectively borrowing $250,000 and paying $5,000 in fees out of what you borrowed, not borrowing $245,000 and then paying $5,000, even if that’s how the interest is officially calculated. If you want the real interest rate, you need to set up a table like this with the real original principal, not what the bank says you’re borrowing.

If you’re asking how you calculate the rate as in what sort of tool you use, there are a number of them out there. There are plenty of functions available in Excel that let you skip forming the whole table and extract specific values, or you can just use Excel to set up an amortization table as described using formulas, and use Goal Seek to set the interest rate such that the ending balance is equal to 0, which is what I do when I set up a spreadsheet that I’ll be pulling numbers automatically from just by advancing the date. If I just need an amortization table and will pull the numbers off manually, I use one of the tools we pay for as part of our virtual office suite that will calculate amortization tables for you on whatever sort of basis you want. I’m sure there are online calculators out there that do the same thing for free (but they aren’t integrated into our record-keeping system).

If I didn’t answer your question, please be more specific about what you’re asking for.

*In reality, most loans are paid once a month, but not all months have the same number of days. For some loans, they don’t care, and charge you 1/12 of the yearly interest rate a month. This is usually called 30/360 for the hypothetical number of days in a month and year used to calculate how much interest there is during the month. But there’s also 30/365 (which underpays interest slightly), Actual/360 (which overpays), Actual/365 (which overpays slightly on a 4-year cycle), and Actual/Actual.

The APR is not based on simple interest.

Its based on interest calculated daily.

Some might say that they charge a lower interest rate, but with fees and so many variations of accounting practices eg only allow the repayments to be considered at the end of the quarter, and so on, its hard to compare interest rates…

So you work of the longest period you can be sure of, that gives you the amount owing, the monthly (or weekly ,etc) repayment, and the amount you’d owe at the end.
So then you use an amortization calculator to find the interest rate given that info using interest compounded daily… thats the APR.

If you get a high fee loan, with fixed interest for the first N years, its not going to give the accurate APR for the tail end repayments, because you don’t yet know the interest rate. If the interest rates are lower at the end, the gap, the APR - advertisied % , is larger because the high fees will be more signficant compared to interest charges. But without actual numbers , clairevoyance, who can say.

This is why short-term ‘payday’ loans show a ridiculous APR like 2600%.