I’m looking for some assistance on a brain teaser for the uninitiated. I’m currently struggling with an error in an Excel spreadsheet I’m using to pay off a personal loan/mortgage. I thought I set it up perfect, until today. It’s giving me dicky results and I can’t figure out what I’m doing wrong. Where would be the best place to seek advice on such a subject? I’m sure anyone with half a brain will see my mistake straight away.
always comes down to cell reference type, calculating text instead of values, different format numbers (eg money, dates).
Whatever error it is, look at formulae and check each cell it refers to.
Thanks for the replies! I wasn’t sure if here was appropriate for such a question so thought I’d wait and see if I would get pointed elsewhere.
The following link is a close example of what I’m dealing with. On inspection, I’m not even sure if the interest in my basic formulae are compounding in any way. I am literally a moron.
So as you can see, it looks relatively functional on the surface as the figures looked accurate to me, until I tried a ‘lump sum’ and saw that the interim interest was higher than the week before. I then spent about 4 hours looking at the screen with drool coming out of my mouth and came up with no conclusions other than it’s all probably not even close to logical.
FIrst off, your days don’t increment by 7 evenly. This might be intentional to model a midweek payment, but that is why the interim interest jumps in a funny way. 70 → 74 → 77. Your interim interest is smaller for these stub periods.
That’s the short answer. The longer answer is that the calculations seem odd. You probably want to link columns C and I so that they match. Column C looks pasted and fixed.
Even longer answer, I didn’t check how reasonable your calculations are, but a quick glance looks like you are calculating the total interest in an odd way. If it were me, I would calculate the “interim interest amount” and then sum these up for the total. It’s possible that this is how the terms of the loan are calculated or it might be mathematically equivalent. I’d have to spend a little more time noodling in it to opine on reasonability.
Yes, the midweek payment is intentional. But it’s not the two following interim interest amounts that are the issue, it’s the final two in this example. Can you see how it goes from $68.41 (after a full 7 day period) to $68.49 (after another full 7 day period). That’s what confused me to no end until my brain fell out.
I will analyse your advise and see if it jolts some grey matter into action.