By JLP | March 16, 2008
How to calculate APR using Microsoft Excel or a financial calculator.
I recently heard about a loan company that specializes in short-term loans. Here’s a typical example of a loan that this company offers:
Loan Amount: $1,250
Loan Term: 36 bi-weekly payments (72 weeks)
Payment Amount: $91.66
Total Payments: $3,299.76 ($91.66 × 36 = $3,299.76)
Total Interest: $2,049.76 ($3,299.76 – $1,250 = $2,049.76)
What’s the APR?
Now I’ll show you two ways to calculate the APR on this loan.
Here’s how the APR is calculated using a financial calculator:
N = 36
I/Y = ? (remember that you will need to change the P/Y to 26 since they are bi-weekly payments)
PV = $1,250
PMT = $91.66 (entered as a negative number)
FV = 0
Then you simply solve for I/Y by pressing the CPT button followed by the I/Y button. Your answer should be 171.54.
You can also calculate the APR with Excel, using the RATE formula and inputting the numbers like this:
You then need to multiply the answer (0.065977) by 26 to get the APR, which should be 1.7154 or 171.54%.
Now, here’s what an amortization schedule looks like for this loan:
It’s amazing to think that 90% of the first payment goes to pay interest!
One would have to either be desperate or uneducated to agree to these loan terms. Needless to say I won’t be advertising for this company.