How to Use Excel to Calculate APR
APR is the abbreviation for annual percentage rate. It represents the cost of credit in the form of a yearly rate. The APR is a good measure to compare the cost of loans--the lower the APR the better the rate for the loan. When applying for mortgages, credit cards, car loans or any other type of credit, the issuing lender, by law, must disclose the APR. Whether borrowing money or lending, it helps to know how to calculate the APR, which can be done with Excel.
Things You'll Need
- Microsoft Excel
Enter the number of periods you will be borrowing the money in cell A1 of your Excel spreadsheet. For this example, enter 360, which will be the number of periods in a house note. In this instance, 360 periods is 360 months (30 years * 12 months/year). Excel's abbreviation for this part of the formula is nper (number of periods).
Enter the amount that will be paid in a month. The house note costs $1,600/month. Enter "-$1,600" in cell A2. This is the amount of the monthly note. Each month removes $1,600 from the payment owed. Excel's abbreviation for this is pmt (payment).
Enter the amount owed in cell A3. The amount owed in this example is $250,000, which is the original value of the loan amount. Enter $250,000 without the dollar sign and the comma. Excel's abbreviation for this part of the formula is pv (present value). Present value is the total amount that a series of future payments is worth now. It is the amount of money that is being financed.
Type "0" in cell A4. This is because at the end of the loan the desired balance is zero. The goal is to pay off the home loan. For Excel, this part of the formula is fv (future value). The future value is the balance that is desired after the payments are made.
Type "=Rate(A1,A2,A3,A4)*12" in cell A5. The resulting rate is 7%. This means that the annual percentage rate (APR) is 7% for a $250,000 house for which the payment is $1,600/month. The formula is multiplied by 12 in order to get the Annual Percentage Rate. If 12 were excluded, the rate returned would be the monthly APR.
Tips & Warnings
- Review the full terms of any finance agreement before signing. Pay for items with cash as often as possible.
- Do not overextend your finances when purchasing.