Loans Spreadsheet Loan Calculation Tips for Excel and Others Model loan amortization with easy-to-use templates By Justin Pritchard Justin Pritchard Facebook Twitter Website Justin Pritchard, CFP, is a fee-only advisor and an expert on personal finance. He covers banking, loans, investing, mortgages, and more for The Balance. He has an MBA from the University of Colorado, and has worked for credit unions and large financial firms, in addition to writing about personal finance for more than two decades. learn about our editorial policies Updated on November 11, 2021 Reviewed by Cierra Murry Reviewed by Cierra Murry Cierra Murry is an expert in banking, credit cards, investing, loans, mortgages, and real estate. She is a banking consultant, loan signing agent, and arbitrator with more than 15 years of experience in financial analysis, underwriting, loan documentation, loan review, banking compliance, and credit risk management. learn about our financial review board In This Article View All In This Article DIY vs. Calculator Templates How to Build an Excel Loan Model Run Your Calculations What You Can Do With Your Spreadsheet Photo: Roberto Westbrook / Getty Images Spreadsheets are powerful tools that help you understand how a loan works. They make it easy to see important details about your loan, and the calculations are more or less automated. You can even use pre-built loan amortization templates that allow you to simply enter a few details about your loan. Spreadsheets are available from several popular providers, and the instructions here will work with Microsoft Excel, Google Sheets, Apple Numbers, Apache OpenOffice, and many others. DIY vs. Calculator Templates There’s no need to do it all yourself—unless you really want to. Templates allow you to plug in a few details about your loan and be done with it, and those pre-built templates are easy to use. To use a loan template in Excel, open the program and if "Loan Amortization" is not an immediate option, type those words into the search box. Once you’re in the template, fill in information related to your loan: Loan amount: Enter the amount you are borrowing. Annual interest rate: Use the interest rate on your loan (you can either use APR or a stated interest rate, if available). You should not need to convert to decimal format, but make sure the rate is displayed correctly. The number of payments per year: How frequently do you pay? For monthly payments, for example, enter 12. Start date of loan: This might be helpful for planning, but is not essential for accurate calculations. Optional extra payments: If you’ll pay extra—or if you want to know how helpful it would be to pay extra—use this field. Pay attention to how much you save on cumulative interest, and how much more quickly the loan gets paid off when you pay extra. How to Build an Excel Loan Model Doing it yourself takes more time, but you’ll develop financial knowledge and spreadsheet skills you can’t get from a template. Plus, you can customize to your heart’s content. That said, after you’ve done this a few times, you might find it faster to use a template to start the process, then “unprotect” the template and make your modifications. Let’s say you want a model that shows you every yearly or monthly payment. Start at the top row of your spreadsheet and add the following section titles to each row (where you’ll enter information about your loan): Loan amount Interest rate Number of periods (or loan term) Give those cells a green fill color, which tells you that you can change those values as you compare loans and run what-if scenarios. Now you’re ready to make your data table. Create a row with these column names: Period numberStarting loan balancePaymentAmount applied to interestAmount applied to the principalRemaining loan balance You can also add additional columns (such as cumulative interest paid). It's up to you. Run Your Calculations Next, you’ll need a row for each payment as part of your data table. In the far left column of your spreadsheet (below the “Period number” column described above), add one number on each row: The first row is “1,” then move down a row for “2,” and so on. Each row is one payment. For a 30-year loan, you’d have 360 monthly payments—for large numbers like that it’s easiest to fill in the first few periods and use Excel’s “fill handle” to fill in all of the remaining rows. Now, have Excel fill in and calculate values for you. Remember to use the "$" before any row number in a formula in your calculations except the Period—otherwise, Excel will look in the wrong row. Use the PMT function to calculate your monthly payment (using information from your “input area”). This payment generally does not change over the life of the loan, so this function would be the same all the way down. (The Excel function is: "=PMT('Loan amount,' 'Interest Rate,' 'Periods')") Use the IPMT function to show the amount of each payment that goes to interest. (Same formula as above, just with IPMT at the beginning) Subtract the interest amount from the total payment to calculate how much the principal you paid in that month. Subtract the principal you paid from your loan balance to arrive at your new loan balance. Repeat for each period (or month). Note that after the first row of your data table, you’ll refer to the previous row to get your loan balance. If your loan uses monthly payments, make sure you set up each period correctly in the formulas. For example, a 30-year loan has 360 total periods (or monthly payments). Likewise, if you’re paying an annual rate of 6% (0.06), you should make the periodic interest rate of 0.5% (or 6% divided by 12 months). If you don't want to do all the work of working in spreadsheets, there's an easier way. Use an online Loan Amortization Calculator. These are also helpful for double-checking your spreadsheet’s output. What You Can Do With Your Spreadsheet Once you’ve got your loan modeled, you can learn a lot about your loan: Amortization table: Your spreadsheet shows an amortization table, which you can use to create a variety of line charts. See how your loan gets paid off over time, or how much you’ll owe on your loan at any given date in the future. Principal and interest: The spreadsheet also shows how each payment is broken into principal and interest. You’ll understand how much it costs to borrow, and how those costs change over time. Your payment stays the same, but you’ll pay less interest with each monthly payment. Monthly payment: Your spreadsheet will perform simple calculations as well. For example, you’ll need to calculate the monthly payment. Changing the loan amount (if you consider buying something less expensive, for example) will affect your required monthly payment. “What if” scenarios: The benefit of using spreadsheets is that you have the computing power to make as many changes to the model as you want. Check to see what would happen if you make additional payments on your loan. Then see what happens if you borrow less (or more). With a spreadsheet, you can update the inputs and get instant answers. Was this page helpful? Thanks for your feedback! Tell us why! Other Submit Sources The Balance uses only high-quality sources, including peer-reviewed studies, to support the facts within our articles. Read our editorial process to learn more about how we fact-check and keep our content accurate, reliable, and trustworthy. Microsoft. "Loan Amortization Schedule."