Lois_E begins a long MFW journey

gallygirl

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!

Aw, you're dead clever you are . Spreadsheets amended

Lois_E wrote: »

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

Em, yes please (although another spreadsheet ritual is amending it every month when I get the latest balance through

Lois_E

gallygirl wrote: »

Aw, you're read clever you are . Spreadsheets amended

Thanks

Lois_E wrote: »

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

gallygirl wrote: »

Em, yes please (although another spreadsheet ritual is amending it every month when I get the latest balance through

Suppose your interest rate is 5%. This means every year, if you paid off nothing, then your balance would go up by being multiplied by 1.05. If it was 6.2% it would be 1.062. If it was 15% it would be 1.15 etc etc. This is the number you need to start with. So, to take the 5% example, you take your 1.05 number and put it into this formula

=(1.05^(1/12)-1)/(365/12)

That gives you your daily interest rate.

Alternatively, you can put your 5% (including its % sign) into a cell, for example K1, and then refer to the cell in your formula. Then it would be

=((1+K1)^(1/12)-1)/(365/12)

I don't know if they change it for leap years, but it won't make much difference if they do. And I don't know how they round the pennies, so you'll still have to check your statements or you may be a penny or two out.

Hope that's useful.

gallygirl

Lois_E wrote: »

I don't know if they change it for leap years, but it won't make much difference if they do. And I don't know how they round the pennies, so you'll still have to check your statements or you may be a penny or two out.

Oh for goodness sake, do I have to do everything myself :whistle::rotfl:

Lois_E wrote: »

Hope that's useful.

Well it's knocked a month off one mortgage so I'm happy with that .

I think that's all my requirements for now, unless you know something I don't know I don't know

pammyj74

wow you are very clever on the old formulas. I try my best with them and google is my best friend there lol. If there is something I want to do but cant figure it out Lois will be new best friend

Lois_E

Always happy to help with Excel problems. I enjoy the challenge.

Froggy-G

Lois_E wrote: »

Always happy to help with Excel problems. I enjoy the challenge.

Hi Lois!

Hope you're ok? Would you fancy a challenge?? Could you help me create a spreadsheet for me, so I can follow the impact of my overpayments?

Thanks
Froggy

Lois_E

Yes of course. What would you like to have on it? Have you got a spreadsheet already or are we starting from scratch?

Froggy-G

Lois_E wrote: »

Yes of course. What would you like to have on it? Have you got a spreadsheet already or are we starting from scratch?

Starting from scratch Sorry..
I'd like to see potential pay off date, interest saved, and anything else you think might motivate me to get this paid off sooner...

Let me know what info you need..

Thanks muchly in advance

Froggy

Lois_E

OK, I need current balances for your mortgage (and unsecured loan if you want that included) but I can get those from your sig, if it's up to date. I need interest rates for both, and the amount(s) and day(s) of the month for your regular payments to both.

Froggy-G

Lois_E wrote: »

OK, I need current balances for your mortgage (and unsecured loan if you want that included) but I can get those from your sig, if it's up to date. I need interest rates for both, and the amount(s) and day(s) of the month for your regular payments to both.

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