This example teaches you how to compare loans with different durations in Excel.

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

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

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

3. Change the duration of the other two loans to 25 and 30 years.

Result:

The monthly payment over 30 years ($899,33) looks good in contrast to the $966,45 and $1,074.65. Right?

4. But now we calculate the Total Paid for each loan.

The monthly payment over 30 years ($899,33) suddenly does not look so attractive anymore. Conclusion: the longer the duration of the loan, the more interest you pay.