Calculating Extra Loan Repayments in Excel Part 3

This is Part Three of a three part series on using excel for loan calculations. If you haven’t already, check out Part One  and Part Two of the tutorial.

In Part One we set up a loan amortisation table, and Part Two we made some formatting adjustments and pulled out some results in a mini report. Today, we will dynamically link these results to a cash flow budget so that we can run different scenarios and see how this is going to affect our cash flow.

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. Banks often have their own confusing ways of calculating interest, so your results may differ from theirs.

Below is an example of a cash flow budget. You will notice it’s incomplete but it gives us an idea of how this works. I’ve previously written a post on how to set a cash flow budget, but not specifically how to do it in excel, so if you would like me to write a tutorial on that, let me know.

I just pulled the numbers in the cash flow budget off the top of my head and they don’t reflect any real life information.

You will notice that I’ve left the mortgage field in the spreadsheet blank, this is where we will put in our linking formula.

Start by renaming Sheet 1 to Cash Flow Budget by right clicking on the tab and selecting Rename. If you have already created a loan calculator and amortisation table, copy and paste the entire sheet into Sheet 2 and rename Sheet 2 Loan Calc.

Now in the first mortgage cell under January in our cash flow budget (B13) we are going to enter a reference formula:

1. Type = (equals)

2. Click on the Loan Calc tab

3. Click on the total payment amount in our left hand data table (B11 on my sheet)

4. Press F4 on the keyboard to make the reference absolute (2 dollar signs will appear in the formula).

5. Press Enter

The formula will look like this:

This will bring across our monthly mortgage payment amount from our loan calculations spreadsheet. Now just grab the fill handle at the bottom and drag to fill across to December. If you double click on any of the cells along this row, you will notice that the formula points back to the monthly payment cell on the Loan Calculations page.

Adding the mortgage repayment into you Cash Flow Budget will automatically change the totals for Total Expenses and Net Cash Flow. In the spreadsheet that I created, once we put in the mortgage repayments, we end up with a negative cash flow balance, so it would seem that we can’t afford a $420,000 loan.

So I’m going to go back to our loan calculation sheet and change the extra repayment amount to $0 and have a look at the effect on our cash flow. You will notice that the amortisation table and the results data change automatically and we will now not be saving any money on interest or reducing the loan term.

My cash flow budget will also automatically update just by changing the data in this one cell, but it looks like I still can’t afford the loan repayment.

So back on my loan calc sheet, I’ll change the principle (amount we are thinking of borrowing) to $400,000 and make extra repayments of $50 per month.

By changing the principle amount I can instantly see that I can afford to make the loan repayments. I can also see that I’m saving around $34,000 in interest and shaving 1.67 years off the loan by making extra repayments.

You will also see the affects on your cash flow if you change the interest rate or reduce the term of the loan making your spreadsheet a pretty powerful personal financial forecaster! Huge companies like BHP use similar techniques (just more sophisticated) for their financial modelling with exactly the same software that we use at home.

We created this loan calculator just for a mortgage, but you can copy and paste your loan calculator onto as many sheets as you need and just change the variables for car repayments or credit card repayments. Use the same formula as above to link these repayments into your cash flow budget.

I hope that you found this series helpful and interesting. If there are other personal finance excel tutorials that you would like me to add, don’t hesitate to drop me an email. If you would like a copy of the spreadsheet to play around with you can download it here: Cash Flow Budget and Loan Calculator Spreadsheet. It is 97 – 2003 compatible but the conditional formatting may not work.

Go to Part One

Go to Part Two