Lois_E begins a long MFW journey

Lois_E

I've managed to get DS and DD into a competitive "I've got more in my piggy bank than you" thing. DS wants to earn money and is offering to do extra housework if I will pay him, so I've taken him up on the offer. Not very MSE for me, maybe, but if it results in kids who understand the value of money and look after it wisely, then it's worth it. I've made it very clear I've no intention of paying for the stuff he's supposed to help with anyway.

Froggy-G wrote: »

Ok, balances are fully up to date in my signature.. Interest rate is 4.77% for both accounts, and payments are taken on the first of each month.. total monthly payment is £734.20

Ps: term was 35 years starting November 2006

Is the interest also added to the balance on the 1st? I am working on it for you, but am not sure how to send it to you once I've created it. Would you be OK to PM me an email address?

Froggy-G

Not sure about the interest, I would think they add it daily.. Will PM my email address

Thanks
Froggy :cool:

Lois_E

It's usually calculated daily but compounded (ie added to the balance) monthly. When you get your statements, when does it say "interest" and increase the balance?

Froggy-G

Lois_E wrote: »

It's usually calculated daily but compounded (ie added to the balance) monthly. When you get your statements, when does it say "interest" and increase the balance?

On the statement, it shows one lump sum for interest on a monthly basis, on the day of the payment (1st of the month)..:cool:

Peonie

Lois_E wrote: »

In your box, type =INDEX(D:D,MATCH(0,J:J,-1)+1,1)

If you (or anyone else) end up with a spreadsheet with different columns, just change those letters D and J in the formula to whatever your date and closing balance columns are. Let me know if it works!

My version of this has another cell next to the MF date where it calculates how old I will be, so I will be able to celebrate if I get that number down!

If anyone wants to know how to calculate their daily interest so that it corrects for compounding monthly, let me know!

Thanks for sharing this formula, I like learning how to do something new and I wish I understood what the -1, +1,1 is for.

In my spreadsheet I have 2 sets of calculations, one for our normal payments and the second with overpayments, so when overpaying I can calculate how much interest I will save and see the term reducing.

I used the formula on both sets of calculations. As we have not started our mortgage (and therefore cannot yet overpay) the end date is the same, but when using the formula each set of calculations shows a different end date. I have checked the formula and it looks right. Do you have any idea why it's different?

My spreadsheet is based on one someone shared on here, and it had the a similar if not the same end date calculation, and the same problem occurred. I resolved the problem by deleting the formula!

Lois_E

INDEX(range,rownumber,columnnumber) gives the value in the relevant row and column of the range. D:D is a range with only one column, so if we put =INDEX(D:D,rownumber,1) then we get the value in the relevant row of column D.

To work out the row number we use MATCH, which gives the position of a value in a range. MATCH(0,J:J,-1) means look for 0 in column J, and the -1 bit means that the values in J are in decreasing order. This gives the position of the smallest number in J that's bigger than 0. We don't want that, we want the first number that's negative, which is the next row down. So we take the MATCH answer and add one to it to get the next row. That means that the row number we want to put in our INDEX function is MATCH(0,J:J,-1)+1.

I'm not sure what you mean by your two versions giving different end dates. When you generate your two sets of values with dates, are they identical? Which columns have you got them in? Where are you putting the date matching formulae, and what exactly do they say?

Hope that helps.

Lois_E

Expensive day today. Another £9.50 on PE kit for the constantly growing DD, but I think that's the last of it now. Then I popped into Mr T for lunch box stuff and a few vegetables, and accidentally bought some jeans, two kitchen knives and a potato masher. At least it wasn't a house. I've needed another kitchen knife for a while, and my masher broke recently, and utensils were on 3 for 2....

So then I got the veg and lunch box stuff, and realised I'd got about £40 total, so if I got it up to £50 I'd get my 5p/litre petrol coupon. I'd nearly filled the car with petrol before going into the shop, but the queue was huge, so I hadn't and it was sitting out there in the car park nearly empty. So I was very good, and didn't get anything "extra". I got something I've been thinking "I must get one of those" for ages, and then stuff like mayo and ketchup that will sit in the fridge until needed. I got my coupon and filled my car up.

Got to dash off to collect kids. Will update spending diary and sig later. Froggy, I am working on your spreadsheet but it may be a day or two - I have marking to do tonight for school.

Froggy-G

No worries Lois :j Hope you "have fun" with the marking tonight...:o

Froggy-G

Lois

Just got a letter from mortgage company saying that monthly payment will be 722.10 from June 1st onwards.. Hope this helps..

Froggy

GeorgieFTB

Lois

I think you should start charging froggy

You could always pay it to your mortgage

Gx