My Excel mortgage spreadsheet

dazzaofdagenham

can you do another calculation for me ?

The initial loan was for 155K over 20 years.
18 Payments @ 6.59% from Nov 2007 giving payment @ £1169


Then 221 payments @ Bank of england base rate + 1.99%

Interested in what the new lower payment will be from april this year

Thanks

Locoblade

Palm_Centro wrote: »

Is it right that if I have a 25 year mortgage I cannot enter 25 years into the initial rate field?

Yep afraid so, because its a requirement of the calculations to have an into period smaller than the total period, but as the help bubble on that cell says, if you have a straight through mortgage with no interest variation or intro period it doesnt matter what the intro period is set to, just put the introductory interest rate and the "after" interest rate the same, then the intro period can be any number you like because it won't make a difference to the calculations.

For example, if you pay 6% interest for the life of the mortgage, just put 6% into box 3 and box 5, then leave the intro period (box 4) as 1, or 5, or whatever, as long as its under 25.

cheers

Locoblade

hpuse wrote: »

May I suggest, for the ease of offset mortgage account holders, as well as to make the best use of savings on interest payable to taxman (basic and high rate tax payers)
It will make it easier if you could please incorporate the following information in some way in the spreadsheet without affecting the calculations.

a) recommended yearly offset saving balance (% total of the outstanding mortg balance)
b) recommended overpayment ( I personally prefer amounts in their nearest hundreds, since it makes it easy to plan rather than a percentage )

I'm not a technical person, so I have no idea how to suggest a solution !

Hiya

Im probably misunderstanding but wouldn't those figures be subjective rather than numbers that could definitively be calculated? From a mortgage point of view the more you offset and the more you overpay the better, so there is no recommended figures other than offset/overpay the whole lot!

Taking savings into account, the only rule you can really apply is if your savings rate is lower than your mortgage rate its always going to be better to offset all the money rather than save it. If the savings (after tax) is higher than the mortgage rate then its going to be better to save it all rather than offset. The spreadsheet already does this though, telling you how much you're saving/losing compared to a savings account you input.

cheers

Locoblade

dazzaofdagenham wrote: »

My initial period of loan was for 18 months....how can i input that to show an accuratE repayment ?

Thanks again

Hi

If you set the into period to 1 year then you can go into the mortgage monthly table tab and enter the intro interest rate manually for the 6 months following the first year so its calculating the intoductory rate for 18 months in total.

HTH

formulaonefan

Absolutely brilliant spreadsheet. well done and thanks for this great tool

_dave__4

dazzaofdagenham wrote: »

... The initial loan was for 155K over 20 years.
18 Payments @ 6.59% ... Then 221 payments @ Bank of england base rate + 1.99%
Interested in what the new lower payment will be from april this year...

Looks like it might drop to around £920, if base stays at 1.5% :-

http://calc-calc-calc.net/get/calc/Mortgage-Comparison/v1/?L=155650&I1=6.59&Yrs1=1.5&SVR1=3.49&Term1=20&Yrs2=19.99999&SVR2=6.59

[I entered 155,650 to get your monthly payment of 1169, for better comparison...]

HTH!

hpuse

Good day Locoblade!
Yes, I didn't put my point across clearly, which I think is better demonstrated with an example.

Assume the scenario of 100K outstanding balance on an offset mortgage for 20 year term, and the current offset interest rate is 3.5% (assume this is fixed for the sake of simplicity). Also assume that there is 50K in savings and 4.5% is the best interest rate for N years locked in a fixed deposit by a different non-offset provider (which of course is taxable for both low and high rate payers)

after year1 --> Saving of 20K offsetted saves you £X giving to the taxman
after year2 --> Saving of 30K offsetted saves you £XX giving to the taxman
after year3 --> Saving of 40K offsetted saves you £XXX <-- this could be the recommended one since year 3 is greater than or equal to N year fixed rate yeild.

after year4 --> Saving of 50K offsetted saves you £YYY in tax ( this may not be the best for the saver since 4 could be greater than N !).

You could run the above computation iteratively for different amounts and years till best figures for both saving and no: of years that beats the taxman in the shortest possible term (for 50K for N year fixed in the example). I think you can easily generalise this comparison since you know your savings amount and the number of year - both are fixed. Atleast offsetters would be expected to know.

Its a Monday morning and thanks for adjusting with the situation of my brains !

Locoblade wrote: »

Hiya

Im probably misunderstanding but wouldn't those figures be subjective rather than numbers that could definitively be calculated? From a mortgage point of view the more you offset and the more you overpay the better, so there is no recommended figures other than offset/overpay the whole lot!

Taking savings into account, the only rule you can really apply is if your savings rate is lower than your mortgage rate its always going to be better to offset all the money rather than save it. If the savings (after tax) is higher than the mortgage rate then its going to be better to save it all rather than offset. The spreadsheet already does this though, telling you how much you're saving/losing compared to a savings account you input.

cheers

Locoblade

Hi hpuse

Its now Friday and I've read the above a couple of times but I still don't fully understand the objective.

Surely regardless of what you pay to the tax man, if you earn more after tax in your savings account than you save by offsetting on the mortgage, then the money is best off saved.

In your example above with £50k savings at 4.5% taxable, if you're a basic rate tax payer you'll actually get 3.6% return, so you're slightly better off having the entire £50k in the savings account. If you're a higher rate tax payer though, you'll only get 2.7% return, so the entire £50k is better off in the offset.

It seems what you're actually trying to achieve is to calculate some kind of minimum contribution to the tax man whilst still saving as much money overall, but Im not sure why you'd want to do that if you can actually earn more overall by saving the whole lot, despite paying more tax?

deefadog

Hi mate - One question, and i may have overlooked this but is there anyway i can take a lump sum off the mortgage in say year 3 of a 25 year mortgage?

As i have done this and want this to reflect in the spread sheet?

Thanks

Locoblade

Yup you can do that, if you go into the detailed monthly tab, scroll down to the month you paid the lump sum, and put the lump sum figure in as an overpayment for that month