This example shows you how to compare loans of different maturities in Excel.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
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.
