My Excel mortgage spreadsheet

Locoblade

Hi Greg

Can you give me a bit more info on what cells etc arent giving you the information you're expecting? V1.10 has been available for over a year and I haven't heard of anyone else having any problems, but happy to look into it.

thanks

coldcazzie

Hi, I just downloaded this and filled in my details, it's very good and much better than me trying to create one myself!

I like it particularly because I have a spreadsheet for all our accounts etc and therefore I can link the overpayments to my current account sheet, meaning as I adjust those on a monthly basis it automatically updates itself.

My question: we have the option to make capital payments, providing they are a minimum of £500, and I'd like to know how to add those into the calculations? Thus far I cannot tell where I would add such a payment...

Any help you could give me would be very much appreciated! Thanks!

Locoblade

Hi

You can add additional payments each month into the "Monthly Overpayment" column in the "Mortgage 1 Monthly Table" tab.

cheers

unhappy_shopper

Hi Locoblade,

This is a fantastic spreadsheet you have developed.
Thanks for uploading it for MSE use.

I have a doubt though.
If I have a monthly overpayment with Early Repayment Charge, how do I add that into the spreadsheet. I added the OP into the monthly OP column, but where do I add the ERC associated with it?

Thanks in advance for your kind reply.

Locoblade

Hi

Sorry I'm not entirely clear on how you're getting charged, do you mean that you're overpaying each month but getting a monthly charge to do that because the overpayment is over your limit? If so, can you not put the overpayment in the overpayment column as you are now and the charge in the "additional borrowing" column, because the charge is in effect cancelling out a small part of your overpayment, just like a bit of additional borrowing would do? Either that or take the charge from the overpayment and put the one slightly smaller figure into the overpayment column?

Sorry if thats not what you're trying to do, maybe post up an example if not so I can see what you're trying to do

unhappy_shopper

Locoblade wrote: »

Hi

Sorry I'm not entirely clear on how you're getting charged, do you mean that you're overpaying each month but getting a monthly charge to do that because the overpayment is over your limit? If so, can you not put the overpayment in the overpayment column as you are now and the charge in the "additional borrowing" column, because the charge is in effect cancelling out a small part of your overpayment, just like a bit of additional borrowing would do? Either that or take the charge from the overpayment and put the one slightly smaller figure into the overpayment column?

Sorry if thats not what you're trying to do, maybe post up an example if not so I can see what you're trying to do

Thanks a lot for your reply.

I got the spreadsheet to reflect my ERC by including it in the 'additional borrowing' column as per your suggestion.

Just to clarify, yes, we have already surpassed our 10% OP without penalty. But, we still make OP (with ERC) as it still works out profitable as we have a 5.55% interest rate fixed until Mar.2012.

Thanks again.:D

coldcazzie

Locoblade wrote: »

Hi

You can add additional payments each month into the "Monthly Overpayment" column in the "Mortgage 1 Monthly Table" tab.

cheers

Hi, thanks for your reply: I have our regular overpayments linked to my monthly budget as I said. However, this isn't the question I asked...

Basically, for a capital payment, we have to ring them and send them a cheque and then tell them whether we want to use it to decrease the term of the mortgage or the remaining monthly payments.

So, for example, on a £100,000 mortgage, if you made a capital payment of £1000, it would decrease the capital owed at £99,000, and thus the interest charged on the remaining capital would change, and either be reflected by reducing the remaining payments or by reducing the number of payments left to make (ie, the term).

This is not the same as making an overpayment which pays off both capital and interest in the proportions as laid out in the spread sheet.

Hence my question: if we made a capital payment, where would I put that in the spreadsheet, or is there not facility for that, because I can't see immediately where it would go.

Do you see what I'm saying?! :laugh:

Thanks, Caz

foreversummer

coldcazzie. You would put it in the overpayment column as locoblade says.

An overpayment is an overpayment surely!

Foreversummer

Locoblade

Hi Caz

An overpayment is an overpayment, once you've paid your regular amount each month anything over and above that is automatically a wholly capital repayment because there's no more interest to pay. If you don't believe me, take a note of an End Balance one month and then put an overpayment of say £1000 in, the end balance will reduce by that same £1000, i.e £1000 of capital has been paid off

If you want your overpayments to reduce your mortgage term to pay it off quicker you select "Keep Same" in option 18 on the main info and key figures page, if you want it to reduce your monthly whilst keeping the same mortgage length then select "Reduce Monthly". What you can't do within the spreadsheet I'm afraid is mix and match so some reduces term, some reduces monthly payment, so if you've done some and some then it's not going to be that accurate for you

cheers

coldcazzie

Locoblade wrote: »

Hi Caz

An overpayment is an overpayment, once you've paid your regular amount each month anything over and above that is automatically a wholly capital repayment because there's no more interest to pay. If you don't believe me, take a note of an End Balance one month and then put an overpayment of say £1000 in, the end balance will reduce by that same £1000, i.e £1000 of capital has been paid off

If you want your overpayments to reduce your mortgage term to pay it off quicker you select "Keep Same" in option 18 on the main info and key figures page, if you want it to reduce your monthly whilst keeping the same mortgage length then select "Reduce Monthly". What you can't do within the spreadsheet I'm afraid is mix and match so some reduces term, some reduces monthly payment, so if you've done some and some then it's not going to be that accurate for you

cheers

Hi, thank you that's great!

I didn't realise that it worked like that - it is not made clear in our mortgage docs at all (or maybe I'm not reading them right!). So thanks for making that clear sorry if I sounded dim, but it appeared to be different to me! I shall carry on as I was before!

Thanks again!