My Excel mortgage spreadsheet

Yorkie1

If you go onto the Mortgage 1 monthly table tab at the bottom, you can put in overpayments ad hoc for each or any month.

Perhaps put in 12 years for the initial term on the main page, and adjust the overpayments column on the monthly tab so it comes in at your precise monthly amount?

If you then made actual overpayments, you then adjust further accordingly?

mealmond

Thanks for the tip, I will try that later on.

Martin

mealmond

Hi

I have just tried to put in an overpayment but I cannot get the figures to work out.

I will try a few work rounds and see what I can do.

Other than that I will keep looking on the internet, I may try looking for an excel sheet for a loan, I only want to track the payments and see where I am up to and where I will be in the future.


Martin

yt83

Great idea, well done

Locoblade

Just put the following in the mortgage length box, including the equals sign

=11+(11/12)

You're then telling excel to make the length 11 years and 11/12ths of a year (11 months). The spreadsheet will display 11.9 as the display in that box rounds to one decimal but it does actually retain the full number (11.91666666.......) and so correctly zero the mortgage on the 11th month of the 11th year.

The other option is to set the mortgage over 12 years, then go into the monthly page and set the first monthly payment in "Basic Monthly Payment" as the interest only payment value (taken from the "Min Monthly Interest Only" column), that way you effectively put in a dummy month 1 and start month 2 with the same initial amount borrowed and it should all calculate from there.

Cheers

mealmond

Thanks Locoblade, perfect, it makes all the difference when you know what you doing.

Just what I needed to see the mortgage going down and I can add overpayments if ever I am able to do so.

Thanks again

Martin :beer:

shortperson

Thanks so much - this is exactly what I was looking for to manage our loan from other sources than a bank! :j

xyz123

hi one question. If i make manual overpayments i can insert them into the detailed table. By default all of the overpayment is assinged towards paying off the capital rather than interest. I am not sure if this is the case in all mortgages. i will check with my lander and find out. But until i find that info any thoughts on allocation of overpayments towards capital or repayment or combination of both?

Locoblade

At the end of a particular month there's only a set amount of interest owed which is covered as part of your normal payment, so any additional payment can only ever be to reduce the capital. You cant direct it to one or the other, its always money off this months capital which in turn reduces next months (and subsequent months) interest. The only time an "overpayment" would cover interest is if you'd underpayed to start with, not paying enough regular payment to cover the interest owed that month.

Soulistic

Mate, by far the best tool I have used for comparisons. It's because of this spreadsheet that I am going to be going through a re-mortgage. Thanks so much for the time and effort on this!