Excel calculations - loan interest worksheet

[hellkat]

I just can't get my head around creating equations in Excel. I've not had enough experience in Excel to be able to get much beyond basic addition and subtraction equations in cells.

So I need help to create what must surely be a simple spreadsheet.

Here's the scenario:
* Some money ($2000) was lent
* An interest rate was agreed (2%)
* No specific loan period was agreed
* A couple of lump sums (10%) were paid and then repayments were moved to smaller regular (weekly) payments
Shortly after the initial loan another lump sum was borrowed of the same amount ($2000)
Repayments were continued at the same rate initially, but later altered to fortnightly, albeit double the amount.

I want to reflect this in a spreadsheet so that I can see how much daily interest is chargeable, and how much has been paid off, as well as to stay aware how much is left/when it will finish being paid off.

I found a spreadsheet on Microsoft but it doesn't seem to do what I want it to do, and I can't understand the pre-loaded equations in the cells.

If someone could *either* look at the spreadsheet I downloaded - I can send you a copy - to show me how to input my data.

OR* throw together a spreadsheet in which I can input:

(1) the initial loan sum
(2) the daily interest rate
(3) the dates and sums paid back

And then be able to include
(A) the further sum and
(B) the altered payments set-up

And see the results after each payment:
(i) remaining principle
(ii) remaining interest (in case I decide to negotiate just paying back the principle)

I tried to follow this example:
https://www.wikihow.com/Calculate-Daily-Interest

But I got massively confused and couldn't get beyond step 2
(in fact Step 1 only resulted in me getting a #VALUE! result in cell B4) (see below)

Help!

The data I want to input has been stored in a simple "database" style, showing only when the money was lent, and when/how much repayments were made, just without the reductions or the interest calculations.
____________________
Not nearly as interesting in real life.

[Pjay]

Use this:

[The Shaggy D.A.]

Quick questions, what period does the agreed 2% interest rate apply to - annual? And is the interest capitalised at the end of the period, or does it just accrue separately?
____________________
Chances are quite high you are not in my Monkeysphere, and I don't care about you. Don't take it personally.
Currently : Royal Enfield 350 Meteor
Previously : CB100N > CB250RS > XJ900F > GT550 > GPZ750R/1000RX > AJS M16 > R100RT > Bullet 500 > CB500 > LS650P > Bullet Electra X & YBR125 > Bullet 350 "Superstar" & YBR125 Custom > Royal Enfield Classic 500 Despatch Limited Edition (28 of 200) & CB Two-Fifty Nighthawk > ER5

[MarJay]

[hellkat]

I dunno
God, how does it all get *that* complicated?

We both simply anticipated that the repayments are made "until the loan is paid off"

So I sought to regularise the situation by attempting to provide a spreadsheet showing reducing balances but also including interest applied. I am tempted to cancel any interest charges, but my bank is charging me to accept automatic payments from the borrower, and those bank fees vary according to how many payments are made. The fewer the payments, the less is the monthly fee charged*. She is not aware the bank is charging me these auto payment transactions for her to conveniently pay me back. I am reluctant to mention it, as she is a fairly uncomplicated soul, and the nature of the transaction is intended to be "between friends" and therefore not over-commercialised.

Is it less draconian to apply the interest daily or at the beginning?

I think the assumption is that the 2% lasts "until repaid" ... so I suppose that it would be anticipated to be annual.

With repayments at their current level (a fairly steady $100 a month), a $4000 loan will take roughly 40 months to repay? So roughly 3.3 years of repayments.

The original plan was to pay off lump sums based on the potential sales of books. I planned to apply interest EITHER at the start of the loan, or after each lump sum payment, rather than daily, but which is more consistent? More fair?

But the books are not selling in as large quantities as anticipated. This is a fairly normal trend, the likelihood of which I believe to have been anticipated but unspoken by both parties.

PJay, thank you. Having applied my basic figures to that spreadsheet, I can see how it (kind of) works out, but its still troubling me that I can't just add a new entry on a weekly (or fortnightly) basis showing date and payment made, and then the numbers adjust accordingly.

I'd like to be able to say "yeah, you've paid off x amount, and there is x left to pay" - that is how simple I would like the transaction to continue to be.

However, as it is, that worksheet is rather useful, as I can see how the payments are calculated when I change the numbers.

I can't understand the chart at ALL (except that no interest is paid for the duration of the loan?)

I'd make a rubbish shylock




*fewer/less, IDK .. now is not the time to be pedantic about grammar.
____________________
Not nearly as interesting in real life.

[hellkat]

ok so I am now inputting the dates and payments into the spreadsheet that PJay linked me to, and it is working quite well, I;ve even managed to put in the second loan as a (2000) so it changes all the numbers.

I shall print out a copy once the payments are inputted and see if it makes sense to anybody who knows how to read the damn things
____________________
Not nearly as interesting in real life.

[hellkat]

Right: I have input all the payments, and the extra loan, and taken into account the dates/amounts that changed to fortnightly.

Am I correct in assuming that despite me having started latterly to input fortnightly dates and amounts, that it is still calculating based on the number of weeks it will take to pay back?

Therefore should I make the fortnightly payments into a separate spreadsheet with different calculations on?

I can see the chart has changed to show that the interest payable (red line) is roughly what I expected ($300ish) - so if the taxman were to say "but what about interest income on loans??" I can say, oh, okay, there was that $300 (not even £) over 3.3 years - and once the costs of the bank charges are taken out, there will be huge amounts of tax to pay on that, then ...

But can someone please just confirm that at least I can say to her, "This is where you are now (blue highlighted line) and this is how long it will take you to pay off at your current repayment rate, and the last payment will be in early Sept 2018".

It does also give me the option of saying, okay, look, there's only x-amount left to pay, so once I take out the cost of the bank charges, lets call it quits early.
____________________
Not nearly as interesting in real life.