SPREADSHEETS - 28.02.2020

Creating a loan amortisation schedule

Setting up the input area

Let’s say you want a model that shows you every monthly payment of the loan. On the top row of a new worksheet add the following section titles (where you’ll enter information about your loan): loan amount; interest rate; and number of periods (or loan term). Give those cells a green fill colour to indicate that you can change those values as you compare loans and run what-if scenarios.

Making the data table

Step 1. Create a row with these column names: period number; starting loan balance; payment; amount applied to interest; amount applied to principal; and remaining loan balance. Tip. You can also add additional rows (such as cumulative interest paid). It’s up to you.

Step 2. 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” column described above), in the first row enter the formula =ROW(A1) which will result in the number “1”. Copy this formula down for the number of periods needed.

Calculating the monthly payment

Step 1. 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. Tip. Remember to use the “$” when you refer to any cell numbers from the input area in your calculations. This allows you to copy the formulas to the other rows.

Step 2. To calculate the amount of each payment that goes to interest, multiply the loan balance at the start of the period by the monthly interest rate.

Step 3. Subtract the interest amount from the total payment to calculate how much principal you paid in that month.

Step 4. Subtract the principal you paid from your loan balance to arrive at your new loan balance.

Step 5. 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 starting loan balance.

What-if scenarios

Once you’ve designed the worksheet, you can plug in new numbers for the loan amount or interest rate in the input area and the loan amortisation schedule will automatically recalculate. To change the length of the loan or number of periods, however, you’ll have to adjust the number of rows in the amortisation table.

Tip. Rather than changing the periods manually, you can adjust the formulas to do this automatically. To do this, change the formula in the period 1 cell to: =IF(ROW(A1)>$B$5,””,ROW(A1)) where cell B5 is the loan term in the input area. Then edit the other formulas in the first row to add =IF(ISNUMBER(A8), at the beginning of each formula and ,””) at the end. Copy these formulas down the worksheet - make sure to copy at least as far down as the longest term you anticipate testing. As you change the term in cell B5, the results beyond the end of the loan will change to blank cells (see The next step ).

For an example loan amortisation schedule, visit http://tipsandadvice-financialcontroller.co.uk/download (FC 12.06.11).