Lesson 168: Loans with different terms in Excel

This example shows you how to compare loans of different maturities in Excel.

Loans with different terms in Excel

1. First, we calculate the monthly payment for a loan with an annual interest rate of 6%, a term of 20 years, and a present value (borrowed amount) of $150,000.

Note: we make monthly payments, so we use 6%/12 = 0.5% for Rate and 20 * 12 = 240 for Nper (total period).

2. Next, select the range A2:D2 and drag it down two rows.

3. Change the term of the remaining two loans to 25 and 30 years.

Result:

4. Now we will calculate the Total Paid for each loan.

Monthly payments for over 30 years ($899.33) are suddenly not attractive anymore. Conclusion: the longer the loan period, the more interest you have to pay.

In addition, you can also view many other excel articles here.