Tracking Tenant Rent

HI there all,

I'm hoping someone could help me.

I currently have 2 Separate houses that I let out to 2 separate tenants. Lets call them Sally in house A, and Jane in house B lol.

The problem is... is that both tenants get benefit for part of their 4 weekly rent, then they have to make up the shortfall themselves.. e.g. If I'm charging £550 every 4 weeks - I will get a £450 payment from the government as their Housing benefit, then the tenant makes up the difference every 2 weeks by paying me £50. All payments go staring in to my bank account.

Therefore... for May - from Tenant Sally this is:
£450 Benefit
£50 First 2 weeks
£50 Second 2 weeks
= £550 in that 4 week cycle.

Its becoming quite complicated recently since randomly Sally could get a grant, for say £300 from the government which go straight in to my account separately... they don't even send me a letter lol.
That means Sally is + in her account with me.... I owe her money.

Sometime in the past also... her housing benefit amount could go up or down... where she owes me or I owe her.

How we've solved it in the past is Sally just stops paying me her portion of the rent... in this instance 3x4 Week cycles = £300. Then she re-instates her payment and all's balanced again.

I've got another house now - Jane in house B - and this is starting again lol.

As you can see this is a nightmare for tracking.... As I don't want to be conning them nor for defs I don't want to loose money.

I was wondering if anyone knows how to do the following;

At the min I download my statements every month via an Excel file from my banks website.
I copy this data in to a new sheet in my budgeting workbook.

Soo.... May's data is shown as sheet MAY10.

What I normally do then is to trawl through the statement (ctrl-f mostly) and find the payments from the Housing Benefit for each tenant... and their payment.

What I'd like is for excel to show this for me... e.g. in a calculations worksheet show me what has come in each month (and what date) and whether the tenant is + or - for that month.

Sorry this is soo long... i hope I haven't overdone it but pretty complicated (to me lol)

Sounds like you're doing some serious data mining. What you're asking can probably be done in Excel, but tbh I'm not sure if Excel is the best program to do what you're trying to do. (Hope this isn't blasphemy on this forum!) Have you ever tried using QuickBooks? If you were simply collecting rent once or twice a month from one tenant you could do without accounting software, but in your situation, with multiple tenants AND the government benefit, I think QuickBooks would go a long way towards you having some assurance that you've correctly accounted for everything.

How much activity do you have going through your bank statement? It sounds like at most there should be six rent-related transactions per month, right? Two biweekly payments from tenant A, two from tenant B, and a benefit payment for each of the two. Are all these payments running through your personal bank account? If so, I would highly recommend opening a separate account that you can dedicate to your real estate business. That will make it a lot easier for you to identify those benefit payments that are always changing.

I think those two steps - getting QuickBooks and opening a second account - will clean up your bookkeeping/budgeting a lot. QB isn't the only software out there, you could also check out Peachtree if it is available in the UK.

Hopefully that helps some, although I haven't done much to answer your Excel question. I think you will get more responses if you provide a little more detail on your data and what you are trying to accomplish. Attaching a file would help too, maybe a dummy example of what your exported bank statement looks like. You may be able to do what you're trying to do with SUMIF and some other simple functions. But even if you keep using Excel, I still think your life would be easier by implementing those other suggestions.