Loans with Different Durations


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.

Monthly Payment

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.


Loans with Different Durations Example

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.

Loans with Different Durations in Excel

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.