How to Use Excel to Calculate APR

Finding the annual percentage rate (APR) of a loan is crucial when it comes to comparing different loan offers and working out how much you pay for the loan each year. To calculate APR in Microsoft Excel, use the "RATE" function and insert the required values to get the result. The process is straightforward if you know your monthly payment amount and some other key facts about the loan, but you can work it out based on some basic information.

In the U.S., lenders have to quote your APR to you when you take out a loan. APR refers to the annual percentage rate, and it tells you how much extra you pay over the course of a year for borrowing the money, including both the interest rate and any associated fees. If you take out a loan with a 10 percent APR, you pay $110 for every $100 you borrow for a year. In other words, you'll be paying back the $100 plus the APR of $10, which is 10 percent of the $100. If you're just quoted an interest rate, it might not include any fees, and the loan may cost you more. This is why APR is mandated by law in some countries: It makes your actual repayment amount clear and easier to compare multiple loan offers.

The APR formula in Excel depends on your monthly payment amount. If you already know your payment, you can skip this section. If you don't know the payment, calculate it using the Excel function "PMT."

Type "=PMT(" (without quotation marks) into a blank cell and fill in the information required. The format is "=PMT(interest rate/number of months, number of months you repay for, amount of the loan plus fees, final value)". The final value is always zero because you've paid off the loan when you're done. If you have an annual interest rate, which is standard, type the interest rate as a decimal, so for example, 15 percent = 0.15 and 10 percent = 0.10, divided by 12 into the first section. Add a comma, type the total number of months you're paying (two years is 48 months, for example), add another comma followed by the total value of the loan plus fees, then another comma, and finally add a zero and close the parentheses. This returns your monthly payment amount.

For example, for $2,400 borrowed over two years, with a 20 percent interest rate (0.2 in decimal form) and $100 in fees, you type "=PMT(0.2/12, 48, 2500, 0)" and the result is "−$76.08."

To calculate the APR in Excel, use the "RATE" function. Choose a blank cell, and type "=RATE(" into it. The format for this is "=RATE(number of repayments, payment amount, value of loan minus any fees required to get the loan, final value)." Again, the final value is always zero. You have to use the value of the loan minus any fees because this is the actual amount you're borrowing. For example, when you borrow $2,400 with $100 of fees, you're only borrowing $2,300.

Continuing the same example, you type "=RATE(48, -76.08, 2300, 0)" to get the rate. You can click the cell you used for the "PMT" function for the second part instead of manually copying the figure. Multiply this by 12 to get the actual APR, so type "=RATE(48, -76.08, 2300, 0)*12" to get the answer you're looking for. In the APR example, this works out to 25 percent APR.