My Excel mortgage spreadsheet

Cariad71

Ok thanks, here goes:
1=172000
2=IO
3= 3.29 (but the spreadsheet automatically changes this to 329.00?)
4=2 yrs
5=3.29 (I know rates will change, I just want to get a picture of what things would be like assuming rate stayed the same).
6=20
7=2012
8=oct
9=calculated
17=760

Ive left everything else blank as it doesn't apply.
I also notice that in the table, the decimal points are all wrong i.e minimum monthly payment reads as 47,156.67 (I'd better get a new job ha ha)
Thanks again!

Cariad71

Inputs Highlight Cells for more Info. 1 Mortgage amount £172,000.00 2 Mortgage Type Interest Only 3 Introductory/Initial Interest Rate 329.00% 4 Period of Introductory Rate (years) 2.0 5 Interest Rate after Intro Period 329.00% 6 Loan period (years) 20.0 7 Starting Year of Loan 2012 8 Starting Month of Loan Oct 10 Payment Calculated / Manually added? Calculated 11 Manual Input Initial Monthly Payment N/A 12 Upfront Fees 13 Fees added to the loan 14 Early Repayment Charge Rate (%) 15 Early Repayment Charge Period (years) 16 Closure / Completion Fee Overpayments (optional) 17 Monthly Overpayment Amount £760.00 18 Overpayment/Offset effect on Payments Offset Mortgage (optional) 19 Average Offset Current Account Balance 20 Initial Offset Savings Balance 21 Monthly Payment into Offset Savings

Locoblade

Cariad71 wrote: »

Ok thanks, here goes:
1=172000
2=IO
3= 3.29 (but the spreadsheet automatically changes this to 329.00?)
4=2 yrs
5=3.29 (I know rates will change, I just want to get a picture of what things would be like assuming rate stayed the same).
6=20
7=2012
8=oct
9=calculated
17=760

Ive left everything else blank as it doesn't apply.
I also notice that in the table, the decimal points are all wrong i.e minimum monthly payment reads as 47,156.67 (I'd better get a new job ha ha)
Thanks again!

Sorry been busy at work for the last couple of days, what version of Excel are you using and what operating system on your computer? Of the several thousand downloads this is the first time I've heard anybody say the decimal places etc are wrong so it seems to be something specific to your computer/Excel setup, if I put your figures into the spreadsheet running on Excel 2010 (and 2003/2007) on a Windows machine it comes out at 14.8 years repayment time which sounds right.

cheers

Locoblade

getmore4less wrote: »

Do you have a dump/import feature to make transfer of data easy?

a tabulated format of name value pairs is probably easiest that way order does not matter

Given how the spreadsheet works I've never really seen a need for it to be honest, what type of data are you looking to import that needs that type of tool?

cheers

Cariad71

Hi,It's excel 2010 and windows 7. It's a new laptop. Must be my laptop.

Locoblade

Cariad71 wrote: »

Hi,It's excel 2010 and windows 7. It's a new laptop. Must be my laptop.

That's what I have, drop me an email using the address in the spreadsheet and I'll email you a copy with the details already entered to see if that works.

digp

this should be a sticky

bestest spreadsheet ever

kewwl

Not sure if this was covered but what needs to be changed in order for this to work with USD? Thanks!

Locoblade

kewwl wrote: »

Not sure if this was covered but what needs to be changed in order for this to work with USD? Thanks!

The easiest way is simply to read £ as $, there's 100 cents in a dollar the same as pence in a pound so everything other than the symbol will be identical and calculate correctly.

If you do want to change the symbol though, you could download the compatibility version and change the formatting on the cells.

janineh1982

Hi Loco,

Firstly a HUGE HUGE thank you for doing this s/sheet - i could hug you! :grouphug:

I am a MW (mortgage wannabe) and hope to have one in place next spring, i am already planning to OP and be MF in approx 5 years (after taking the mortgage out).

My challenge is this, the mortgage i 'think' we are going to get has a ER cap of 10% PA. Because of the short time frame we are looking to pay the mortgage back, i don't want to OP the mortgage by anymore than the allowed amount because the ER charges will be significant.

Hence the £000's that we will have saved each year to 'OP' (but can't without incurring a 3% charge) will need to go into a savings pot to be paid at the end of the fixed term.

Is there any possible way for the s/sheet to incorporate my challenge and calculate the savings element also?

Once again, MANY thanks for the s/sheet you have completed to date, it's bloody fantastic :T

Janine x