APR Calculation in Excel

Annual Percentage Rate (APR) is widely quoted by lenders and credit card companies. APR is expected to be an indicator of effective interest rate or effective cost to the borrower when dealing with loans from lenders. APR computation factors in the up front costs (like processing fee ) that the lender may charge for their loans and accordingly the total cost to the borrower is built into it. In some countries (including UK and US), lenders are required by law to publish this figure along with their stated interest rate with a view to making it easy for borrowers to compare loans from various lenders on overall cost basis. Though APR is reasonably achieving this purpose, it is still not a perfect situation, since lenders charge different fees and charges and all these may not be reckoned in APR calculation.

You can calculate the APR for your loan yourself using an excel spreadsheet as long as the loan carries fixed interest rate and all upfront charges payable to the lender are known. All you need to do is to build the loan cash flows in the spreadsheet and calculate IRR for these cashflows; All front-end charges - by whatever the name the lender may collect (points, legal fee, admin charges, insurance etc) - have to be summed up and this value has to be deducted from the loan amount and the net figure is to be given in the first cell (which is a negative value) followed by gross interest plus principal repayment cashflows which are positive values - and calculate IRR of the cashflows using Excel's IRR function. If the payments are monthly, IRR has to be annualized. (Please see IRR calculation with monthly cashflows) The result is the Effective Annual Interest Rate, which is what the official APR in UK and EU is expected to indicate and is quoted by lenders.

In US, the practice is to show APR in Annual Equivalent Rate terms rather than Effective Annual Interest Rate i.e., without considering compounding effect as in the UK equivalent APR. If you need to calculate the US equivalent of APR, simply use the following formula to convert Effective Interest Rate derived from IRR calculation mentioned above into US equivalent APR - "=((1+R)^(1/12)-1)*12" - where R is the IRR value calculated above (monthly payments assumed).

Ready to use APR Calculator

If you want to quickly calculate APR for your loan, you may use this Online APR calculator; Simply enter loan amount, loan term, nominal interest rate and up front loan closing costs and get true APR for your loan. The calculator gives both the UK/EU and US variants of APR. Use this indicator to compare the lenders in terms of total effective cost of the loan. The Excel version of this calculator can be found at APR calculation spreadsheet.