My Excel mortgage spreadsheet

Locoblade

benjdr wrote: »

I've encountered a situation I cannot replicate in the spreadsheet.

Lets say I start a mortgage for 100k on Janurary 15th, 2009.
For easy of numbers, the monthly payment will be £500, and are made on the first of each month.
However, the first months payment will always be higher, because of the interest accrued from Jan 15th to Feb. i.e. Feb Feb 1st's payment will be 800, or whatever, including the £300 interest from Jan 15th to Feb.

How do I replicate this in the spreadsheet?

Hi Ben

The only way to do this is to put the payments in as £500 a month and then put an overpayment in the first month only of £300. The spreadsheet works on monthly interest rate calculations not daily though, so you cant put in a mid-month starting point, and because its only monthly calculation it will never be 100% accurate to the nearest few pounds and pence unfortunately.

Locoblade

Kalimero70 wrote: »

Great spreadsheet :j .

However, I've noticed that if you set the interest rate after intro period to a lower value than the initial interest rate, mortgage repayment time goes down. Also monthly repayment period starts decreasing, so overall repayment period is less than specified.

In your example I have selected 5.5% interest rate after 2 years - this gives mortgage repayment period of 23.1 years.

Can you please check this?

Yep, its because you've chosen to keep the payments the same following minimum payment drops. For example if you're on an initial rate of 6% giving a monthly payment of £800, then after 2 years it drops to 5% giving a payment of £700, when keep-same is selected you'll continue paying £800 a month, i.e a £100 a month overpayment hence why it pays it off quicker

Kalimero70

Thanks a lot :T

dazzaofdagenham

a excellent spreadsheet...many many thanks

darren

deefadog

Again, thanks very detailed spread sheet!

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?

TrikerAndBiker

Fantastic spreadsheet, thank you :T

Palm_Centro

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

hpuse

very simple, yet elegant and easy to use spreadsheet. Appreciate your humble and helpful support as well. cheers.
HL

hpuse

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 !

dazzaofdagenham

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



Thanks again