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