Working out rent arrears & interest... anyone help?

Flowerydaisies

Hello
Desperate cry for help from a non-mathematical/Excel genuis.

I need to work out how much interest can be charged on the following arrears amounts - it's to do with a flat rental.

Interest can be charged at 4% above BOE base rate from 14 days after the money became due - but can't work out how to get Excel to do this....

The dates and amounts are as follows:

5/2 Due £425 (BOE rate 5.25)
5/3 Due £425 + £100 court fees (BOE rate 5.25)
5/4 Due £425 (BOE rate 5)

Can anyone work it out for me or point me in the direction of a web tool to do it please? Thanks!!!!

geo555

I don't know the fomula for excel, but you work it out like this
425 x 9.25% = 39.31 per year /365 days = 11p a day
525 x 9.25% = 48.56 per year /365 days = 13p a day
425 x 9% = 38.25 per year /365 days = 10p a day


In all you probably need to charge £30 - £32 interest upto now

PasturesNew

As your tenant didn't pay 3 lots of rent and a court fee, I wouldn't waste much time on adding interest to be honest.

mlz1413

for Excel do a colomn for rent another for interest rate and another for total (which will hold the sum)
A B C (sorry these won't align!)
425 9.25% =A*B

for sum go into column C, press = button, then tab to column A (you should see the box 'move') then press * button and tab to column B, then press enter button. Sum s/be in column C.

Bungarm2001

I wouldn't even bother wasting the electricity/battery power on your 'puter. You are clearly not going to get whats owed you let alone the interest owed.

m.colak

Flowerydaisies wrote: »

Hello
Desperate cry for help from a non-mathematical/Excel genuis.

I need to work out how much interest can be charged on the following arrears amounts - it's to do with a flat rental.

Interest can be charged at 4% above BOE base rate from 14 days after the money became due - but can't work out how to get Excel to do this....

The dates and amounts are as follows:

5/2 Due £425 (BOE rate 5.25)
5/3 Due £425 + £100 court fees (BOE rate 5.25)
5/4 Due £425 (BOE rate 5)

Can anyone work it out for me or point me in the direction of a web tool to do it please? Thanks!!!!

ok might be think but simple answer 6 columns
date amount additional cost BOE interest total
(A)date is self explanatory
(B)amount again self explanatory
(C)additional costs (court fees etc)
(D)BOE again self explanatory as whole number i.e 5 or 5.25
(E)Interest =IF(TODAY()+14>=A1,((((D1+4)/36500)*(TODAY()-A1-14))*(B1+C1)),0) .....copy that formula exactly including the equals sign at front
total =sum(b1+c1+e1)

Flowerydaisies

m.colak wrote: »

ok might be think but simple answer 6 columns
date amount additional cost BOE interest total
(A)date is self explanatory
(B)amount again self explanatory
(C)additional costs (court fees etc)
(D)BOE again self explanatory as whole number i.e 5 or 5.25
(E)Interest =IF(TODAY()+14>=A1,((((D1+4)/36500)*(TODAY()-A1-14))*(B1+C1)),0) .....copy that formula exactly including the equals sign at front
total =sum(b1+c1+e1)

The formula is giving me 0 as an answer - copied it exactly as shown in your reply. What date formula should I use? I have done: 05/02/08 - the first date that arrears happened?

Flowerydaisies

! - Got it to work, put the numbers on line 2 - moved them into line 1 and have got a number now! Thanks again.

Flowerydaisies

OK, seems I made a mistake when reading the contract. The clause says:

To pay interest at the rate of 4% above the BOE base rate...prevailing on any rent or other money lawfully due from the tenant under this agreement which remains unpaid for more than 14 days, interest to be paid from the date the payment fell due.

So, I think the formula needs adjusting - but not sure how. I can charge from the date it became due - not after 14 days.... Can you change the formula for me please?

m.colak

Hi you have to follow what i've said about which should go in which column else it won't work. You should have only gained about £15 in interest unfortunately cause the date isn't that different.

if you want to make it from the due date then just remove any point of the 14 appearing

=IF(TODAY()-14>=A1,((((D1+4)/36500)*(TODAY()-A1-14))*(B1+C1)),0)

will change the formula slightly

=IF(TODAY()>=A1,((((D1+4)/36500)*(TODAY()-A1))*(B1+C1)),0)

if you want it to start calculating the interest only after 14 days have gone but at the from the original date:

=IF(TODAY-14()>=A1,((((D1+4)/36500)*(TODAY()-A1))*(B1+C1)),0)