Spreadsheet

richtips86

Hi, I’ve just started reading this board. Somebody somewhere wrote about a really good s’sheet by someone along the lines of solcoblades. I can’t reme the name or which thread. Any pointers would be appreciated! Thanks

louloubelle79

Hi I found a lot of the spreadsheets too fiddly; The app Mortgage Calculator + works for me

muddywhitechicken

Hello

I use financialbliss's spreadsheet - described below in a couple of posts I've quoted from his diary.

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 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.

financialbliss wrote: »

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.