My Excel mortgage spreadsheet

anniecave

Thanks for the spreadsheet - I was trying to compare 2 mortgages with different fees and rates to work out which would be best. The answer wasn't what I thought it would be to start off with....

nxf

loco - you must have had a head the size of a planet to do this spreadsheet and as this thread is nearly 5 years old, I am sure you are NOW able to do daily interest with one arm tied behind your back
As so many people are now benefiting from your awsomeness and using it for more than just a comparison tool - do you think this may happen in the future?

Many thanks

Paully232000

This is brilliant, thanks

Locoblade

nxf wrote: »

loco - you must have had a head the size of a planet to do this spreadsheet and as this thread is nearly 5 years old, I am sure you are NOW able to do daily interest with one arm tied behind your back
As so many people are now benefiting from your awsomeness and using it for more than just a comparison tool - do you think this may happen in the future?

Many thanks

Hi

Sorry been on my hols for a couple of weeks.

I'm afraid it's not going to evolve much more, to be honest I've forgotten how some of the spreadsheet works now so would take a lot of time digging through it re-learning to even get back to where I was with it 4-5 years ago, with family and kids now that's just not going to happen. The fundamental thing though is that Excel has an inbuilt function to calculate interest etc on a monthly basis but not on a daily basis, so until Microsoft add that functionality it's way too much work to contemplate doing daily interest calculations manually.

Monthly is (relatively) easy as there's always 12 months in a year, it never changes and the payment/interest calculations are performed on every line. Daily gets very complicated because not only are you dealing with around 30 times more information (365 lines of calculations per year instead of 12), to calculate interest daily but add payments monthly on a certain day then display that in a monthly format, you end up with a far more complicated spreadsheet having to deal with months that have different number of days plus leap years that further complicate it, so without the inbuilt function to handle all that, it's bordering on impossible, certainly for me

cheers

grifferz

What's the best way to represent the larger first payment due to interest from the partial month before?

e.g. completion mid-August, normal monthly payment £400, interest for August £160 so first payment in September 400+160=£560, subsequent payments from October onwards £400 again.

Set start month of August with a payment of £0, then £560 for September, £400 after that?

Locoblade

Hi

Obviously the spreadsheet assumes you borrow money at the start of the month and pay back at the end of each month. If you borrow in the middle of the month but don't start paying until 6 weeks later the end of the following month, you'll need to manually edit the first payments to get it matching as close as possible so in your case I'd just start the mortgage in September and put in an additional payment of £160 in that first month to get it roughly right. Then when you get your first statement through you could tweak that £160 figure so the end of September figure mimics your end of September balance as accurately as possible.

Locoblade

I've uploaded v1.12 which fixes a bug in the calculations when using the offset calculations to predict how much interest you save. A bit of an oversight on my part so apologies for that, but something that took over two years for someone to spot!
If you're not using the offset feature then no need to update as there's no other changes, but if you are I would suggest downloading the new version and copy/pasting your numbers over.

Downloads as usual from
http://www.locostfireblade.co.uk/spreadsheet/Index.html

dimwit.

what if the ERC differs each year of the fixed year period how can that be reflected in the spreadsheet please?

JD2112

Hi,
I am currently a third year maths student, and am studying the cost of money for my dissertation.
Having struggled to find any formula for calculating the cost of a loan or mortgage for two different interest rates i have found this spreadsheet.
The speadsheet is truly brilliant, the time and effort must have been extraordinary, and the output is exact with the online mortgage calculators.
Having read some of your recent responses I appreciate this may be a long shot, but was there anyway I could have a copy of your formulas if you have them anywhere?
You would of course be credited in my work for the formulas and information, and I am more than happy to pay a contribution into your paypal account.
If you could do this it would be most appreciated as I need to show how the mortgages are calculated, and not just the output. I also understand if you would rather not disclose this information.

Thanks.

planningforthefuture

Is anyone smart enough to demonstrate how to change the spreadsheet to accommodate:

2.58% with 17 years and 4 months left with a balance of £83,150?

Thanks in advance