This is Part Two of a three part series on using excel for loan calculations. If you haven’t already, check out Part One of the tutorial.
All results are based on the assumption that the interest rate remains fixed throughout the entire loan term and that interest is calculated monthly and payments made monthly. It does not take into account bank fees. Note that banks can often have their own confusing way of calculating interest!
Continuing on from Part One, let’s start by putting in our extra repayment amount. We will put this in the field under our PMT data, and make it $80 per month. Because we’ve already formatted our table, the extra repayment column updates automatically as well as all the other cells in the table.
Of course, when we make extra repayments, we’re paying our loan off quicker, so if you scroll to the bottom of the table you will notice that there are negative numbers for the rest of the loan term after the ending balance equals $0. We are going to edit the formulae that we created in Part One to include if statements, so that when our loan balance comes to $0, the calculations automatically end. That way we will be able to summarise the information in our results table.
First, in order to stop the period counting down once the balance has reached $0, we will adjust the formula from period 2 down to the following:
The calculation basically says that if the ending balance of my last payment period equals $0, then the number in the period column equals zero, otherwise, take the above period number and add one. Double click on the fill handle to fill down the entire table. Nothing will change just yet, the period should still count from 1 to 360, but once we start changing the other formulae, you will see this column change.
Next we will add an if statement to the payment column starting at row 2 as per the following:
This calculation says that if the monthly payment amount (at B6) is more than the beginning balance for the period (F4) plus the interest amount (J4), then show the monthly payment amount, otherwise (if the the beginning balance plus the interest is less than the monthly payment amount) calculate the monthly payment amount as the balance plus interest. Once you’ve typed the formula, use the fill handle to fill down the column.
Finally we will add a similar if statement to the extra repayment starting at row 2 as follows and fill down as before:
So, if the balance at the beginning of the month is less than the monthly payment amount (B6) then make $0 extra repayment, otherwise use the extra repayment amount as we set up in B11. You will notice that for these two formulae, we have used the dollar signs for absolute references to lock in the numbers from our data table for when we fill down.
Have a look towards the bottom of the spreadsheet and you will notice that with the extra repayment of $80 per month, we will have paid off our loan 30 months early (period 331). Everything after that has been changed from negative numbers to zeros.
Now we can leave our amortisation table as is and it is just fine, or we can use some conditional formatting to hide any 0 amounts and make it look pretty. The conditional formatting will mean that any cell that has a 0 in it will be “hidden” and the hidden cells will change automatically if we change our extra repayment amount.
Select the entire table range from E3 to L362, open up the conditional formatting dialogue from the Home ribbon and select New Rule. Click on the option use formula to determine which cells to format and enter the following formula:
What this formula is saying is that if there is a 0 in the cells in column E, then format the text to white so we can’t see it. It’s still there, just not visible. Have a look at the bottom of the table. It should now look like it ends at period 331 with the final ending balance being $0.
Over to the left of our table, well put in some fields to summarise some key results. Add a heading to show total interest paid (as per the table once extra repayments are taken into account), and use the auto sum formula to add up all of the monthly interest amounts. Make sure that you select all the way down to row 362 to include all of the hidden cells.
Subtract the total interest after extra pmts from the total interest as per our CUMIPMT calculation in B8 to find out how much interest you save by making extra repayments.
Beneath this we will use the max function divided by 12 to work out the time in years that it will take to pay off the loan when making extra repayments and how many years and months we shaved off the loan term. Remember to select the range E3 all the way to E362 to include the hidden data.
If necessary, format these cells as ‘number’ and reduce the number of decimal places.
Add a heading, pretty it up with some formatting to make it easier to read. I’ve rearranged some of the data in the tables to organise it a bit better, your final product might look something like this:
That’s it! Now we can run some scenarios by changing the variables under the monthly repayment calculations such as the interest rate, the extra repayment amount or the principle amount. Because everything is formulated, changing one variable will mean that the entire amortisation table will adjust automatically and the interest saved and loan period will also be adjusted automatically and that’s the exciting part about Excel! And once you have this spreadsheet set up, you can copy and paste it to multiple sheets and use it to calculate other loans like a car loan.
Have a go changing the extra repayment amount to $120. How much interest do you save? How much time? What about if the interest rate is 7%? What is the repayment amount?
Tomorrow in Part Three we will link the total monthly payment amount into a cash flow budget so that changes to payment amounts, interest rates, term or principle will automatically adjust our cash flow budget and we can see immediately how our monthly cash flow will be affected by changes in loan repayments.
Go to Part One