Excel (and Other Spreadsheet) Loan Calculation Tips

Build a Spreadsheet for Loan Amortization

Calculations
Andrey Prokhorov/E+/Getty Images

It’s wise to learn how your loans work using spreadsheets like Microsoft Excel. Instead of taking your lender’s word for it, you can calculate important details for yourself and get a clear view of why you pay what you pay.

Note: the information on this page can be used for other spreadsheets like Google Sheets or OpenOffice.

Spreadsheets tell you exactly how much you owe at any given time, how much of each monthly payment goes towards your loan balance, and how much you’re paying in interest.

It’s the easiest way to create an amortization table (which you can even turn into a line chart) so that you can see how the loan gets paid down over time.

Of course, spreadsheets do simple calculations as well – like telling you what the monthly payment would be on any given loan. Then, you can run “what-if” scenarios to see how a lower rate or a different repayment term changes things.

DIY vs. Calculator Templates

We describe how to build your own spreadsheet from scratch on this page, and you can use these instructions with slight modifications on other software programs. But there’s no need to do it all yourself unless you really want to. If you’d rather use a template, which allows you to plug in a few details about your loan and be done with it, Excel has those pre-built templates for calculating loans.

To use a loan template in Excel:

  1. Click File, and select New on the left-hand menu
  2. You'll see a list of available templates
  1. Look through the templates (under Sample Templates) or search Office.com
  2. Double-click the "Loan Amortization" template
  3. Fill in the information about your loan

An alternative method to get the Loan Amortization template is:

  1. Right-click on the tab of sheets at the bottom ('Sheet1, Sheet2, etc')
  2. Select Insert...
  1. Choose Spreadsheet Solutions from the pop-up menu
  2. Double-click the 'Loan Amortization' option

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 get things started, then “unprotect” the template and make your modifications.

How to Build an Excel Loan Model

Let’s say you want a model that shows you every yearly or monthly payment. Start at the top row your spreadsheet and add the following sections (where you’ll enter information about your loan):

Give those cells a green 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.  Make a row with the following column names:

  • Period number
  • Starting loan balance
  • Payment
  • Amount applied to interest
  • Amount applied to principal
  • Remaining loan balance

If you wanted, you could also add additional rows (such as cumulative interest paid, for example).

Next, you’ll need a row for each payment as part of your data table. In the far left column of your spreadsheet (below your “Period” column described above), put 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 "$" when you refer to any row number 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 in your “input area”) - this payment generally does not change over the life of the loan
  • Use the IPMT function to show the amount of each payment that goes to interest
  • Subtract the interest amount from the total payment to calculate how much 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.

To see an example of how the math works and how your table might look, scroll to the bottom of our Amortization Calculation page.

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%, you should make the periodic interest rate 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. It’s also helpful for double-checking your spreadsheet’s output.

Continue Reading...