The Challenge - Debt free in two years!

Ceniza

Woowoo wrote: »

I know you will, you've already cleared as much as me more or less!

I'm coming for you

**Kat** wrote: »

Hey, just followed everyone over and subscribed. One day I will have the time to read over your diary! How did you do your graph? I am good on excel so can say formulae etc, wouldn't mind something similar....

The graph was done using data from other pages but the main one runs all sorts of calculations for me. Below is what I use for the cacluation. The following formula are in the cells:

L3: =Data!C41 ~ This just links to another sheet which has my current amount outstanding
L4: =INDEX(B:B,MATCH(H2,F:F,-1)) ~ This will look down the dates for me and return the date when the balance (column F) is in minus, thus showing the date the debt is cleared.
L5: blank ~ I just add in here, what my target overpayments are.
L6: blank ~ this is another field I manually add a number too, it's what I expect to be able to pay off as a lump sum from October bonus.
L7: =SUMIF(E:E,">0") ~ This is the total interest I will pay over the term of the loan at the current rate of overpayment.
L8: =F99 ~ This is just the total left in December 2011, just so I know what my total will be by the end of the year (not really needed).
L9: =SUM(19137.26)-L8 ~ Again this isn't really needed but it's the extra cleared by end of 2011 with the overpayments compared to what it would have been if I only made minimal payments.
L10: =SUM(9587.13)-L7 ~ This is the interest I've saved by overpaying my debt, rather than making minimal payments.

H9: =SUM($G$9/365) ~ This is to calculate my daily interest rate over the year (for correct calculation purposes).

Okay, this is the interesting bit (for me as I'm a geek).

G63: =F74*$H$9 ~ This takes the total outstanding (F74) x daily interest rate (H9) to work out what the current daily rate is based on the balance.
H63: =G63*31 ~ This is the monthly interest amount to be applied next month, based on the number of days in the month.
D64: 383.87 ~ This is my mandatory minimum payment
F64: =G63*31 ~ Balance after minimum payment
E65: =H63 ~ This is the interest to be added to the balance
D66: =$L$5 ~ On a usual month, this is the 'expected' overpayment to work out my DFD (so will usually show £400). However, each month I will overtype this if I don't make the expected overpayment.
G67: =F66*$H$9 ~ And so the cycle continues of working out the interest on the balance from the previous month.



So, pretty simple really. I just change L5 (overpayment) to what ever I think I can do each month. And this updates the current DFD field for me. Also adjusting the bonus amount has the same affect. If I don't make an overpayment amount, or if it's different, I just change that specific one to what ever I managed. Too many missed ones though and the DFD automatically updates.

In terms of the graph, it's the same. I have three columns. Snowball | actual balance | anticipated balance.

Snowball was my orignal figures to be DF. The actual balance is from this page, what ever my actual balance is for that month, and the anticipated comes from here too, that's the dotted line, it shows where I will be, based on this if I repay using the parameters above.

Give me a shout if you need anymore. Hope this makes sense

Woowoo

Way over my head, but then you already did some nice graphs for me

**Kat**_2

Ceniza wrote: »

Thanks mrsb83 - I'd let you use a copy of mine, but unfortunately it only works with on debt

I've done one which is for multiple debts. It's cheating really (as in I haven't made it that complex) but can show you (sorry still working my way through your diary lol)

Ceniza

Yeah sure, but I'm down to my last debt now so this one works perfectly for me

**Kat**_2

Lol - I basically have one table for each of my debts.

To work out the monthly APR (as fixed %) I use =IPMT(interest rate per month which is the annual/12, period the interest is for (i.e. I want one month or "period's" worth of interest), number of periods (months) the debt is over, present value).

For example on my loan it's:

=IPMT(0.019,1,48,E3 (the cell the current value is in - this moves then to E4, E5 etc as it's dragged down each line))

Drag it down.

I have my payment which I manually put in (on minimums to show DFD with current snowball but will change as and when overpayments are made) in the next column and then the balance which is the previous balance (i.e if on line 4 would be E3) minus the interest plus payment (as the interest calculates as a minus figure).

I do this for each of the debts. Then have a totals table which adds it all up. I have 2 columns for totals, where I should be from the snowball I originally did when setting the spreadsheet up (did the totals then copy and pasted special to get the values) and the total which still contains the sum formula to show where I am. My graph then shows where I calculated I was and where I am now.

As I only did this last week, the graph lines are in the same place, however, once I make my overpayments they will shift apart.

Hope that makes sense.

Ceniza

Makes sense for me. I thought about doing it this way, but it makes forecasting very difficult, unless you do it manually, because when tracking the payments monthly, naturally you concentrate on one debt at a time with overpayments, however, the other debts are kept to minimum payments.

The problem comes in the timings to switch the debts around. For example, if you have debt 1 ending in december, then how do you tell debt two that it's overpayments start in January - and if you miss December and you don't clear debt 1 till February, how do you make the adjustments.

I wanted an automated table, where I don't change anything, unless my overpayment changes.

Still, sounds like you got something that works for you, and it really does look better when you have it all in one graph to view and track your progress against

mrsb83_2

Thank you Kat - I think I'm going to stick with my snowball calculator for now. It's been really helpful so far, although I would love to be tracking my overpayments...perhaps I will give it a go!

Ceniza

I've just factored in the below just to actually see the difference this site, and all of you that motivate me make. The purple line is just my original loan by making just the mormal monthly payments, it doesn't include my credit cards either, that I was making minimum payments on anyway. It's amazing to think that I'd still be on the purple line, or even worse as I started on over £23k debt including credit cards and overdrafts. I could not sure I'd have cleared the credit cards by now. In fact I'm certain I wouldn't have.

So this is more a comparison as to where my original loan would be now, minus the credit cards. Now everything is cleared, I'm at the same amount roughly. But from here on there's such a difference.

**Kat**_2

OMG! What a difference it makes!! Scary stuff really. I'm so glad I found this site.

Ceniza

I need some advice.

My application to MBNA was accepted, but they're only willing to give me £8k at 0%

Is it worth me getting this, clearing £8k of the Lloyds loan and paying back both? Considering I still have to make a mandatory £400 a month payment to Lloyds? I'd then surely be tied to make another £400 to MBNA? It's all so confusing...