Annual Loan Payment Overview
The annual loan payment is the total principal amount and interest amount that is paid in a year. To calculate it, you need to have the loan amount, interest rate, and periods. The formula to calculate the annual loan payment is:
R = Interest Rate
P = Principal
n = Number of Periods
Method 1 – Creating an Annual Loan Payment Calculator with a Direct Method
The dataset showcases loan amount, interest rate, and periods.
- Select C8.
- Enter the following formula.
=(C5*C4)/(1-(1+C5)^-C6)
- Press Enter.
- Change the loan amount, interest rate, and periods to see the output.
Read More: Create Progressive Payment Calculator in Excel
Method 2 – Creating an Annual Loan Payment Calculator Using the Monthly Payment
To calculate the monthly payment:
- Select C8.
- Enter the following formula.
=(C5/12*C4)/(1-(1+C5/12)^-C6)
- Press Enter.
To calculate the annual loan payment:
- Select C9.
- Enter the following formula.
=C8*12
- Press Enter.
- Change the loan amount, interest rate, and periods to see the output.
Method 3 – Annual Loan Payment Calculator Based on the EMI
To calculate the EMI.
- Select C8.
- Enter the following formula with the PMT function.
=-PMT(C5/12,C6,C4)
- Press Enter.
To calculate the annual loan payment:
- Select C9.
- Enter the following formula.
=C8*12
- Press Enter.
- Change the loan amount, interest rate, and periods to see the output.
Read More: How to Make HELOC Payment Calculator Using Principal and Interest in Excel
How is the Monthly Loan Payment Calculated?
Use the formula below:
R = Annual Interest Rate
P = Principal
n = Number of years
N = Number of Payments per year
For a $80000 loan at the interest rate of 8% paid in 3 years:
- Enter the following formula in the formula box:
- Press Enter.
This is the output.
Download Practice Workbook
Download the practice workbook.
Related Articles
- How to Make HELOC Payment Calculator in Excel
- How to Create Snowball Payment Calculator in Excel
- How to Create Line of Credit Payment Calculator in Excel
<< Go Back to Payment Calculator | Finance Template | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!
