We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
FinancialBliss: My mortgage free journey…
Comments
-
Recently joined the site and I've used the mortgage calculator (egg one) on this board and am thinking of joining you lot on here!
We live in a small 2 bed though so not sure we'll stay here forever as thinking of having some kids in the next couple of years so will need somewhere bigger eventually. We overpay every month and I was really impressed by the figures on the mortgage overpayment calculator!0 -
p.s. I have an early January birthday too and it's rubbish!0
-
Squirreler wrote: »Recently joined the site and I've used the mortgage calculator (egg one) on this board and am thinking of joining you lot on here!
Hey, not just a newbie to my diary, but a newbie to MSE too. A very warm welcome from me. Hope you can learn a thing or two from the mortgage forum to help you in your mortgage reduction quest.
As you've mentioned a mortgage calculator, I've just drafted this up in word as I was creating the sheet in Excel.
Using this, you should be able to see how much monthly and daily interest and your net reduction each month.
I’ve never attempted to describe a spreadsheet before, but let’s have a try and see how many people get a useful calculator / analysis tool out of this…
Note: This sheet isn’t going to be 100% accurate – but, and it’s a big but, you can alter the rate, standard payments and overpayments on a month by month basis, so it’s flexible in use – much more so than the web based calculators
Fire up Excel or your favourite spreadsheet tool.
In cell A2 and continuing along the rows, ie A2, B2, C2, etc enter the following descriptions:
Year, Month, Days, Starting balance, Rate, Annual Interest, Monthly Interest, Daily Interest, Standard Payment, Overpayment, Total Payments, Net Reduction, Ending Balance.
For this sheet, I’m presuming you’re working from the start of the year, so…
In cell A3, enter 2008
In cell B3 enter 1, ie January
In cell C3, enter 31, ie number of days in January
In cell D3, enter the starting balance for the month
In cell E3, enter your mortgage interest rate, eg 5.49% should be entered as 5.49, not 0.0549
In cell F3, calculate the annual interest by entering =D3*(E3/100)
In cell G3, calculate the monthly interest by entering =F3/365*C3
In cell H3, calculate the daily interest by entering =F3/365
In cell I3, enter your standard monthly payment
In cell J3, enter any overpayments you’re making for that month
In cell K3, sum standard and overpayment to give total payment for month =I3+J3
In cell L3, determine your net monthly reduction (payment – interest) using =K3-G3
In cell M3, determine month end balance by using =D3+G3-K3
Ok, if I’ve not lost you yet, it’s simply a case of repeating the remainder of the year. This can be quickly be done by taking cells B3 thru M3, copying and pasting starting at B4. Once you’ve done this:
Change cell B4 to be =B3+1, 1 becomes 2, ie February.
Change cell C4 to be 28, or 29 if it’s a leap year!
Change starting balance to be =M3
Now copy cells B4 thru M4 again, and paste into B5, B6, B7 etc. Change the days in the month to suit until you get to cell B15, where you need to reset this to 1, plus put a 2009 in cell A15. Repeat until the point your mortgage balance in column M is zero!
There, it you followed my logic, you’ve got the beginnings of a sheet to do “what if’s” for mortgage payments.
If you do try this, or you have any problems with the above, please post back or PM me and I’ll try and help out…
Thanks,
Financial Bliss.Mortgage and debt free. Building up savings...0 -
Woah! I'll have go with that. Thank you. I've just worked out some figures on the egg website. Bit gutted about the £25k inheritence we put into a buy to let this time last year but hey ho :rotfl:financialbliss wrote: »Hey, not just a newbie to my diary, but a newbie to MSE too. A very warm welcome from me. Hope you can learn a thing or two from the mortgage forum to help you in your mortgage reduction quest.
As you've mentioned a mortgage calculator, I've just drafted this up in word as I was creating the sheet in Excel.
Using this, you should be able to see how much monthly and daily interest and your net reduction each month.
I’ve never attempted to describe a spreadsheet before, but let’s have a try and see how many people get a useful calculator / analysis tool out of this…
Note: This sheet isn’t going to be 100% accurate – but, and it’s a big but, you can alter the rate, standard payments and overpayments on a month by month basis, so it’s flexible in use – much more so than the web based calculators
Fire up Excel or your favourite spreadsheet tool.
In cell A2 and continuing along the rows, ie A2, B2, C2, etc enter the following descriptions:
Year, Month, Days, Starting balance, Rate, Annual Interest, Monthly Interest, Daily Interest, Standard Payment, Overpayment, Total Payments, Net Reduction, Ending Balance.
For this sheet, I’m presuming you’re working from the start of the year, so…
In cell A3, enter 2008
In cell B3 enter 1, ie January
In cell C3, enter 31, ie number of days in January
In cell D3, enter the starting balance for the month
In cell E3, enter your mortgage interest rate, eg 5.49% should be entered as 5.49, not 0.0549
In cell F3, calculate the annual interest by entering =D3*(E3/100)
In cell G3, calculate the monthly interest by entering =F3/365*C3
In cell H3, calculate the daily interest by entering =F3/365
In cell I3, enter your standard monthly payment
In cell J3, enter any overpayments you’re making for that month
In cell K3, sum standard and overpayment to give total payment for month =I3+J3
In cell L3, determine your net monthly reduction (payment – interest) using =K3-G3
In cell M3, determine month end balance by using =D3+G3-K3
Ok, if I’ve not lost you yet, it’s simply a case of repeating the remainder of the year. This can be quickly be done by taking cells B3 thru M3, copying and pasting starting at B4. Once you’ve done this:
Change cell B4 to be =B3+1, 1 becomes 2, ie February.
Change cell C4 to be 28, or 29 if it’s a leap year!
Change starting balance to be =M3
Now copy cells B4 thru M4 again, and paste into B5, B6, B7 etc. Change the days in the month to suit until you get to cell B15, where you need to reset this to 1, plus put a 2009 in cell A1. Repeat until the point your mortgage balance in column M is zero!
There, it you followed my logic, you’ve got the beginnings of a sheet to do “what if’s” for mortgage payments.
If you do try this, or you have any problems with the above, please post back or PM me and I’ll try and help out…
Thanks,
Financial Bliss.0 -
Squirreler wrote: »Woah! I'll have go with that.
OK, Here's my quick effort visually, albeit tidied up a bit - I've moved the descriptions on to two lines, plus I've formatted the numbers to two decimal places etc.
What you can also do, it set the overpayment columns, eg start at J5 to always link to cell J1, ie $J$1, then set a value in J1 to see that amount as an overpayment each month.
You can (in Excel) also do Tools, Goal Seek... - this time tell Excel to set an ending balance to zero at a specific date, say December 2012, by altering cell J1 and it then determines the monthly amount required to attain your goal.
FB.Mortgage and debt free. Building up savings...0 -
FB
I think all the formulae will work in OpenOffice too without a problem; I'll need to see if it has the [STRIKE]gaol [/STRIKE]goal seek function though.0 -
Thanks! I need to get some figures off the mortgage co so I can work this out properly.0
-
financialbliss wrote: »OK, I should be doing the dishes right now, but I told Mrs Bliss that I was just going to, ahem, quickly check my e-mail and that was 45 minutes ago :rotfl:
This is 'Essential Liaison' so it's ok to sneak away.....well that's what I tell OH! :rotfl:
LM:jMFWin3T2 No 20 - aim £94.9K to £65K:j
0 -
So far over my head it's untrue!
I might have a go and see if I can make sense of it though, at least you've put in step by step instructions for dummies like me!Debt: 16/04/2007:TOTAL DEBT [strike]£92727.75[/strike] £49395.47:eek: :eek: :eek: £43332.28 repaid 100.77% of £43000 target.MFiT T2: Debt [STRIKE]£52856.59[/STRIKE] £6316.14 £46540.45 repaid 101.17% of £46000 target.2013 Target: completely clear my [STRIKE]£6316.14[/STRIKE] £0 mortgage debt. £6316.14 100% repaid.0 -
So far over my head it's untrue!
I might have a go and see if I can make sense of it though, at least you've put in step by step instructions for dummies like me!
Hi Kaz,
Once you get your head into what I'm trying to calculate, it should be reasonably easy to follow. Hopefully a picture of the output helps too.
I've a slightly more complicated version for my own use, but I saved the sheet I did last night, so if you're stuck, I could e-mail you a copy. Hey, Financial Bliss eve has his own e-mail address
One problem with the sheet is that it typically overstates the mortgage interest each month. This is because it's taking the starting balance and determining the interest on the debt from that This is no bad thing as it overstates slightly the amount required to achieve your goal.
In the version I use, once I know the actual interest for the month, I replace the estimated interest with the actual value.
Anyone else tried the sheet yet?
FB.Mortgage and debt free. Building up savings...0
This discussion has been closed.
Confirm your email address to Create Threads and Reply
Categories
- All Categories
- 350.6K Banking & Borrowing
- 252.9K Reduce Debt & Boost Income
- 453.3K Spending & Discounts
- 243.5K Work, Benefits & Business
- 598.3K Mortgages, Homes & Bills
- 176.7K Life & Family
- 256.7K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards