How to Calculate a Loan in Microsoft Excel (Templates and Formulas)

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.

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.

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.

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.

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.

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.

When it’s time to start paying off your college loan(s), this template forMicrosoft 365is 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.

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.

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 eachfunction 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.

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’lluse the PMT function in Excel.

The syntax for the formula isPMT(rate, num_pay, principal, future_value, type)with the first three arguments required.

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.

If you have a current loan and are unsure ofyour 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 isRATE(term, pmt, principal, future_value, type, guess_rate)with the first three arguments required.

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*12from theendof the formula.

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 isNPER(rate, pmt, principal, future_value, type)with the first three arguments required.

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.

Sandy Writtenhouse is a freelance technology writer and former Project, Department, and Program Manager. She turned her education, job experience, and love of technology into a full-time writing career. With all sorts of gadgets in her home and her hands, she seeks to help others make life easier through technology.Read Sandy’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved