How to Calculate Monthly Payment with APR in Excel

This article illustrates how to calculate monthly payment with APR in excel. Annual Percentage Rate or APR is a indicator for the costs the borrower must pay on the loan. Often the borrower need to pay some extra fees and charges along with interest which are not included in the annual interest rates. You should compare loan offers based on APR instead of interest rates. Excel has inbuilt functions to calculate monthly payments and APR. Follow the article to learn how to do that in excel.


Download Sample Workbook

You can download the free excel template for monthly payment calculation with APR from the download button below.


What Is Annual Percentage Rate (APR)?

The Annual Percentage Rate, commonly knows as APR, is the rate of cost that the borrower must pay on the loan. It includes interest and all other cost or charges related to the loan. The cost of loan, except interest, may include upfront fees, processing charges, inspection fees, preclosure charges, legal and valuation charges, etc. APR can be calculated using the formula given below.

Assume you have taken out a $500 loan at a 10% yearly interest rate. So, you need to pay extra $50 each year for taking the loan. But, the lender may charge you more fees associated with the loan making the APR, for say, 12%. Then you need to pay $60 instead of $50.

APR calculation is crucial to compare between different loan offers. Because it gives the real cost the borrower need to pay on the loan. In many countries like the USA, it is mandatory for the lender to quote the APR to it’s borrower. It gives the borrower a clear idea about how much extra money s/he need to pay each year over the loan.


How to Calculate Monthly Payment with APR in Excel

Assume you want to take a loan of $7500 at a 14.5% annual interest rate. You need to pay additional $120 as closing costs. You can repay the loan with monthly payments in 2 years.

📌 Steps:

  • Apply the following formula in cell C10 to calculate the monthly payment using the PMT function in excel.
=PMT(C6/12,C7,C5+C8)
  • Don’t forget to add all the costs and charges with the principal loan amount in the formula.

calculate monthly payment with apr

  • After that, enter the following formula in cell C11 to calculate the APR using the Excel RATE function.
=RATE(C7,C10,C5-C8)*12
  • Don’t forget to subtract all the costs from the principal loan amount in the formula.

Read More: How to Calculate Monthly Payment on a Loan in Excel (2 Ways)


Things to Remember

  • Your annual percentage rate or APR must be greater than the annual interest rate.
  • Don’t forget to add the fees with the loan amount when calculating the monthly payment and subtract them when calculating the APR.

Conclusion

Now you know how to calculate monthly payment with APR in excel. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo