Loans of different duration in Excel

This example will show you how to compare loans with different durations.

1. First, let’s calculate the monthly payment on a loan with an annual interest rate of 6%, 20-year duration and a present value (loan amount) of $150000.

   =PMT(A2/12,B2*12,C2)

   =ПЛТ(A2/12;B2*12;C2)

Note: We contribute monthly payments, so we use the formula 6/12 = 0,5% for the rate and 20*12 = 240 for the total number of periods.

2. Select a range A2:D2 and stretch it down two lines.
3. Change the duration of the other two loans to 25 and 30 years. Result:

   A monthly payment for 30 years ($899,33) looks much better than $966,45 and $1074,65. Truth?

4. But let’s calculate the total amount of payments for each loan using the following formula:

   =B4*12*D4

   Monthly payment for 30 years ($899,33) no longer looks so attractive.

   Conclusion: The longer the loan term, the more you overpay.