Loans with Different Durations in Excel

 Loans with Different Durations in Excel

 

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.