Compound interest - cell which calculates months in default is not accurate.

[Baiid93]

Hello everyone,

I have recently created a spreadsheet which shows total money owned and total months in default.

I use this function which change the date =IF(TODAY()>=F19,DATE(YEAR(TODAY()),MONTH(TODAY())+IF(DAY(TODAY())>12,0),12),F19) - and that I calculates total month in default.

Let's say that I lent 10k on 12/5/2018 and due back was on 12/8/2018 - total money back 12k(include interest etc).
The borrower did not pay back and my default interest is 2%.

My function for calculating the money owned by borrower is =G4*(1+J4)^K4 (G4 is the amount due back*(1+default(2%))*total months in default which I calculate seperately((today's - due back)/30.4(month)).

I found that the function ((today's - due back)/month or 30.4) gives you an incorrect number in money owned by the borrower. When I rewrite the cell with the total months in default and i input only number than the momey owned by borrower is correct.

Could someone help me why does it do that?

Thank you

[billgyrotech1]

Hi,

Can you please attach a sample file showing what you expect to happen? To attach reply and select 'Go Advanced'. Then scroll down to 'Manage Attachments'. Upload the file.

[Richard Buttrey]

As Bill says a file would be helpful

But I presume if you're after the amount of interest due then the following is what you want

A1: 12/5/2018 Loan date
A2: £10000 Loan Amount
A3: 12/8/2018 Due Date
A4: £12000 Due Amount
A5: 2% Annual Interest rate
A6: =TODAY() Today's date

Interest due formula:

Please Login or Register  to view this content.

which today gives £121.59 interest