Split mortgage calculator

I put together this spreadsheet during my afternoon coffee break. All you have to do is play around with the yellow boxes and try different scenarios and you’ll see the difference between a split mortgage and interest only.

Before getting started, it’s important to note that a split mortgage is split in two ways, firstly it is divided by a certain percentage, but secondly it is split in terms of structure, the non warehoused portion must be a repayment loan and the warehoused bit is interest only where the interest is compounding against the borrower.

Something interesting jumped out at me, because of how amortization works, and due to low margin trackers, at least half of the loans in existence won’t be any better off on a split mortgage, they’d be better off on interest only in terms of cash-flow, and depending on your view of inflation, being on interest only long term might be about the same as a debt write down.

You’ll see this in the present value cells which are also calculated. There are obvious issues with this calculation, first off is that there are straight line assumptions in what is a dynamic world, but to give a starting comparison it is fit for demonstrating some basics.

We’ll take two people with a €300,000 mortgage over 30 years, in this case one is on a tracker at 2% the other is on a variable rate at 4.4%.

For the first person you’d need to warehouse 55% or more of the loan to have a repayment that is better than interest only on the whole amount. And on the warehoused portion, if it was attracting a tracker rate of interest (against the borrower) the difference today in the future value is almost negligible.

Due to compounding the warehoused portion builds back up to €300,000. A rate of 0-1% as AIB and PTsb are going with will mean this person is better off once 55% or more is warehoused and given that rate, but many banks are not going to do that so it eradicates much of the benefit this idea has.

The person on the variable rate at 4.4% needs at least 27% of the loan warehoused to be better off than on interest only, and unless the warehoused rate is less than 4% they won’t be better off in the future because compounding will be against them.

The idea that split mortgages are a good idea is logically attractive at first pass, but when you break it down into how it will actually work two things jump out, first is that for people on trackers they probably won’t make much of a difference, and secondly that he warehoused rate determines whether this is a ‘better deal’ or merely a way of getting a different pound of flesh at a different point in time.

1. Your mortgage calculator is very difficult to understand, what if you have mortgage and 3 top ups the main mortgage is tracker and other three on variable and fixed how does one calculate that , my tracker is 147,000 the balance of 208000 is on variable and fixed so I’m expecting a similar letter from the ebs as the one you have printed above, I was wondering how would I be fixed to offer to come off the tracker for a write down . Thank you for your informative page I heard you also on pat Kenny today,the whole thing doesn’t sound very hopefull Please don’t print my name

(posted by karl to keep posters anonymity)

2. @ Anon poster:

The way to do that is to do the calculator based on all of the different mortgages and terms and then add up the costs. As for the write down – that is probably something that won’t be happening except for where there are massive arrears.

3. Pat Desmond

AIB EXAMPLE OF Split / warehousing

Loan Mortgage 300,000
Value – Current 200,000
80% of value 160,000 Repaid monthly Cap. & Int.
Tranche B 140,000
Write off – immediate 40,000
Residue 100,000 warehoused
5 year write down 5% 95,000
10 year write down 5% 90,250
Payment within 5 years 70,000 all debt cleared
Payment within 10 years 80,000 all debt cleared

4. David dunne

Hi been told by bank we have to go on to split mortgage and and the end we will have to sell house we are 25k in arrears