Helps keep the loan sharks at bay
Are you considering buying a house? Did you just purchase a car? Are you close to college graduation? Real estate, vehicles, college, and other major items like these normally require borrowing money from a lender. Using Microsoft Excel, you can calculate and track your loan.
From figuring out a payment schedule to seeing how much you can afford, take advantage of loan calculator templates or use Excel’s built-in financial functions.
Table of Contents
Loan Calculator Templates for Excel
One of the best ways to keep up with your loan, payments, and interest is with a handy tracking tool and loan calculator for Excel. These templates cover the most common types of loans and are all available for free.
Simple Loan Calculator
For personal loans, you can see a helpful chart and amortization schedule until your loan is paid off with this simple loan calculator template.
Enter the loan details including amount, interest rate, term, and start date. You’ll then see the summary table, amortization schedule, and column chart update automatically.
The summary table is useful for seeing your total interest and the total cost of the loan, for a nice overview.
Auto Loan Calculator
Whether you already have a car loan or are considering one, this template has you covered. The workbook contains three tabs for auto loan details, payment calculating, and loan comparisons, so you can use the template before and during your repayment.
Start with the Loan Comparisons tab when deciding the best loan for you. You can see the payment amounts for various interest rates. You can also compare amounts based on the total number of payments, payment frequency, and different down payment amounts.
Next, enter your loan details on the Auto Loan Calculator tab including purchase price, loan amount, down payment, trade-in price, transfer fees, sales tax, and miscellaneous fees. You’ll then see your payment schedule on the Payment Calculator tab with a table showing dates, interest, principal, and balance.
When you’re ready to purchase the car of your dreams, keep track of it all with this template.
Mortgage Loan Calculator
If you’re trying to figure out how much you can afford or have recently been approved for a fixed mortgage loan, this Excel template shows you everything you need.
Enter the loan amount, interest rate, term, number of payments per term, and start date. You can then review the scheduled payment amount, number of payments, and total payments and interest, along with the date of your last payment.
You’ll also see a helpful table with all dates and amounts so you can track the interest paid and your remaining balance as time passes.
Balloon Loan Calculator
Maybe you have a balloon loan instead of a fixed loan for your mortgage or vehicle. With this Excel calculator template, you can view a summary and amortization schedule with the final payment for your loan.
Enter the loan details including starting amount and date, interest rate, amortization period, and number of payments per term. Then, review the summary showing your monthly payment, balloon payment, total interest, and total payments.
The amortization schedule keeps you on top of your loan from start to finish.
College Loan Calculator
When it’s time to start paying off your college loan(s), this template for Microsoft 365 is ideal. You can see the percentage of your salary required and estimated monthly income.
Enter your annual salary, loan payback start date, and general details for each student loan. You’ll then see payback information including the length in years and ending date for each loan with payment details as well as total interest.
For a helpful college loan repayment plan, this template can keep you on track.
Line of Credit Calculator
For your line of credit, this calculator shows you every piece of information you need about your loan. You’ll never wonder about the accrued interest or the number of payments you’ve made with this convenient calculator for Excel.
Enter the credit limit, starting interest rate, draw period, first day of interest accrual, payment frequency, and number of days. You can also include rate change details and include additional payments.
For reviewing your line of credit details, use this simple tool for calculations and tracking.
Loan Amortization Schedule
If you use Microsoft 365, you can take advantage of this loan amortization schedule. With it, you can calculate your total interest and payments with the option for extra payments.
Enter your basic loan details including the loan amount, annual interest rate, term, number of payments per term, and the start date of the loan. Then, enter your payment information and optionally extra payments.
You’ll immediately see the amortization table update with all the details you need to keep up with your loan payments, principal, balance, interest, and cumulative interest.
Loan Payment, Interest, and Term Functions in Excel
If you want to figure out the payment amounts, interest rate, or term for a loan, you can use a few handy Excel functions. These let you see how much you can afford based on different amounts, rates, and timeframes.
With each function and its formula, you enter a few pieces of information to see the results. You can then adjust those details to obtain different results and perform simple comparisons.
Note: The formulas you see below do not take into account any additional fees or costs charged by your lender.
Calculate Loan Payments
If you’ve been approved for a specific loan amount and interest rate, you can figure out your payments easily. You can then see if you should shop around for a better rate, need to reduce your loan amount, or should increase the number of payments. For this, you’ll use the PMT function in Excel.
The syntax for the formula is PMT(rate, num_pay, principal, future_value, type) with the first three arguments required.
- Rate: The annual interest rate (divided by 12 in the formula)
- Num_pay: The number of payments for the loan
- Principal: The total loan amount
- For an annuity, you can use future_value for the value after the last payment is made and type for when the payment is due.
Here, we have our annual interest rate in cell B2, number of payments in cell B3, and loan amount in cell B4. You would enter the following formula in cell B5 to determine your payments:
=PMT(B2/12,B3,B4)
With the formula in place, you can then change the amounts you’ve entered to see how this affects the payment. You can enter a lower interest rate or higher number of payments to get your payment amount where it needs to be. The formula result updates automatically.
Calculate an Interest Rate
If you have a current loan and are unsure of your annual interest rate, you can calculate it in just minutes. All you need is your loan term, payment amount, principal, and Excel’s RATE function.
The syntax for the formula is RATE(term, pmt, principal, future_value, type, guess_rate) with the first three arguments required.
- Term: The term in years (multiplied by 12 in the formula) or the total number of payments
- Pmt: The monthly payment entered as a negative number
- Principal: The total loan amount
- For an annuity, you can use the future_value and type arguments as described above and guess_rate for your estimated rate.
Here, we have our loan term in years in cell B2, monthly payment in cell B3 (entered as a negative number), and loan amount in cell B4. You would enter the following formula in cell B5 to calculate your interest rate:
=RATE(B2*12,E3,E4)*12
Now you can see the interest rate you’re paying on the loan with a simple formula.
Tip: To calculate a monthly interest rate rather than yearly, remove the *12 from the end of the formula.
Calculate a Payment Term
Maybe you’re trying to decide the best loan term for your situation. With the interest rate, payment amount, and loan amount, you can see the term in years. This lets you then adjust the rate or payment to increase or decrease the number of years for repayment. Here, we’ll use the NPER (number of periods) function in Excel.
The syntax for the formula is NPER(rate, pmt, principal, future_value, type) with the first three arguments required.
- Rate: The annual interest rate (divided by 12 in the formula) or the flat rate
- Pmt: The monthly payment entered as a negative number.
- Principal: The total loan amount
- For an annuity, you can use the future_value and type arguments as described earlier.
Here, we have the annual interest rate in cell B2, monthly payment in cell B3 (entered as a negative number), and loan amount in cell B4. You would enter the following formula in cell B5 to calculate your loan term:
=NPER(B2/12,B3,B4)
Again, you can then adjust the basic loan details and see the term update automatically for the timeframe and amounts that work for you.
Mind Your Money
If you’re on the fence about taking out a loan or already have one, keep these templates and functions in mind or give one a try. Being conscious of your loan details, from the interest rate to the term, is as important as making the payments.
For more, look at these online calculators which include those for loans.
