My Excel mortgage spreadsheet

1222325272848

Comments

  • joolley
    joolley Posts: 100 Forumite
    Hi Loco,

    For some reason, I seem unable to use the mac compatible form of the spreadsheet on my mac. I keep getting 'this file is encrypted' message. Am I doing something wrong?

    Ta
    joolley
    Keep it simple and you will find the middle way.
  • Locoblade
    Locoblade Posts: 795 Forumite
    Part of the Furniture 500 Posts Name Dropper
    joolley wrote: »
    Hi Loco,

    For some reason, I seem unable to use the mac compatible form of the spreadsheet on my mac. I keep getting 'this file is encrypted' message. Am I doing something wrong?

    Ta
    joolley



    Can anyone with a Mac advise what might be happening here as I don't have one myself?

    The file isn't encrypted it's the same XLS format as normal. Do you have Microsoft Office installed on your Mac do you, as I'm guessing the Mac doesn't come pre-installed with anything that will open an Excel spreadsheet?

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Riq
    Riq Posts: 10,430 Forumite
    Bookmark for when I get home and I can download this. :)
    "I'm not from around here, I have my own customs"
    For confirmation: No, I'm not a 40 year old woman, I'm a 26 year old bloke!
  • Hi there

    I've downloaded this is a library and will take it home and have a look later. Many thanks for all the time and efforts - it looks great at first glance.

    What I couldn't tell is how the spreadsheet will deal with mid monthly overpayments? Does it allow for the extra days in the month at which the capital remaining is lower and the interest accruing is lower as a result - due to the overpayment?

    Thanks again

    Max
  • Locoblade
    Locoblade Posts: 795 Forumite
    Part of the Furniture 500 Posts Name Dropper
    edited 18 August 2010 at 10:16AM
    Hi Max

    It calculates monthly so won't take into account differences in mid monthly payments I'm afraid, doing daily calculations in excel adds a whole order of magnitude of additional complexity to the spreadsheet due to differing number of days in the month, leap years and the physical amount of calculations required, so I doubt anyone has had the knowledge/patience to write an accurate excel sheet with daily calculation.

    To be honest though the sheet is more useful as a prediction tool than a monthly log of actual transactions because even calculating daily interest would see anomalies against actual lender calculations due to lenders all using slight variations with how they calculate, some calculate daily then add the interest as one monthly lump at the end of the current month, others add that lump a month in arrears etc.

    Calculating monthly is still pretty accurate though when compared to daily interest loans, its only a matter of pence or a couple of pounds out per month at most, so still gives a good prediction of how the loan will pan out, and as you made payments you can always amend the monthly subtotal manually to accurately reflect your actual figures once your statements come through.

    Cheers

    Edit to delete some duplicated text, why dont Iphone users get the scroll bar on the right when writing / editing a post, if you have a long post you cant edit all of it!
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • gmm30
    gmm30 Posts: 101 Forumite
    Many thanks for this Locoblade, the spreadsheet seems to work very well for my Nationwide mortgage - only a few pounds out after 4.5 years and various overpayments etc. An excellent resource for budget planning and looking at the impact of interest rate changes.
  • Good to hear that it's keeping track with your mortgage fairly accurately :)
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Locoblade,

    I really wanted to make some additions to the spreadsheet - such as adding a 'with overpayments' and 'without overpayments' line to the repayment chart, except it's passworded as I'm sure you're aware. Any chance of giving it out so I can make these changes?

    Thanks
  • Is it possible to use this to work out a 90%ltv with 75% interest only and 15% repayment?
    I assumed that i could just use two speadsheets, but my maths is so poor, i don't know if it will give a true reflection.
    Was a 40 a day smoker for 20 years.
    Decided to give up, and haven't had a fag for 12 years.
    Halfway through losing six stone.

    Looking forward to early retirement.
  • Is it possible to use this to work out a 90%ltv with 75% interest only and 15% repayment?
    I assumed that i could just use two speadsheets, but my maths is so poor, i don't know if it will give a true reflection.

    Yes you can do it by splitting it out into two spreadsheets. Your figures are slightly confusing though, from the above it seems what you're doing is buying the house for say £200k, paying £20k (10%) deposit and then want to mortgage the remaining £180k split into part repayment and part interest only, and you want to have an interest only mortgage of 75% of the whole cost (75% of £200k is £150k), and the rest (£30k) repayment.

    Are you sure this is what you're after though, because normally you'd agree with your lender to split the loan amount figure ignoring the LTV and deposit paid, which simplifies it. In the example £180k loan, I want to pay interest only on 75% of it (180 x 0.75 = £135k), and the rest (£45k which is 25% of £180k not 15%) repayment.

    HTH
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 350.3K Banking & Borrowing
  • 252.9K Reduce Debt & Boost Income
  • 453.2K Spending & Discounts
  • 243.3K Work, Benefits & Business
  • 597.9K Mortgages, Homes & Bills
  • 176.6K Life & Family
  • 256.4K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.1K Discuss & Feedback
  • 37.6K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.