Are you in the market to compare loan offers? If yes, then you will need to have an idea about how you can find the annual percentage rate (APR) of a loan. This is very important when it comes to comparing different loan options and working out how much you have to pay for the loan each year.
In order to calculate APR in Microsoft Excel, you have to use the “RATE” function. Then insert the required values to get the result. This is quite a straightforward process if you know your monthly payment and some other key facts about the loan. However, you can work it out based on some basic information.
What is APR?
APR is the annual percentage rate which tells you how much extra you have to pay over the course of a year for borrowing the money. It includes both the interest rate and any associated fees. For example, if you happen to take out a loan with a 10 percent APR, you will be paying $110 for every $100 you borrow for a year.
This implies that you will be paying back $100 plus the APR of $10, which is $100 plus the APR of $10, which is 10 percent of the $100. In case you are just quoted an interest rate, it might not include any fees, and the loan may cost you more. As such, APR is mandated by law in some countries. On the whole, APR makes your actual repayment amount clear and easier to compare multiple loan offers.
Calculating Your Monthly Payment
In Excel, the APR formula depends on your monthly payment amount. So if you already know your payment, you can just skip this section. On the other hand, if you don’t know the payment, you can calculate it using the Excel function “PMT”.
- Simply type “=PMT” (without quotation marks) into a blank cell.
- 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. This is because you’ve paid off the loan when you’re done.
- In case you have an annual interest rate, which is standard, you are to type the interest rate as a decimal.
- For instance 15% = 0.15 and 10% =0.10, divided by 12 into the first section. Add a comma, type the total number of months you are paying (two years is 48 months for example). Then add another comma followed by the total value of the loan plus fees, then another comma, and finally add a zero and close the parenthesis. This will return your monthly payment amount.
How to Calculate APR in Excel
This is how to calculate APR in Excel:
- Use the “RATE” function.
- Select a blank cell and type in “=RATE(“ into it. The format for this is “RATE(number of repayments, payment amount, value of loan minus any fees needed to get the loan, final value)”.
- The final value is zero. You are to use the value of the loan minus any fees because this is the actual amount you are borrowing.
- Example – if you happen to borrow $2,400 with $100 of fees, you are only borrowing $2,300. Type in “=RATE(48, – 76.08, 2300, 0)” in order to get the rate. You can click on the cell you use for the “PMT” function for the second part instead of copying the figure manually. Then multiply this by 12 in order to get the actual APR. Type “=RATE(48, – 08, 2300, 0)*12” to get the answer you are looking for. For the APR example, it works out 25% APR.