Microsoft Excel Question

Missy86

Would anyone be able to help me set up a template in excel that tracks rental income?

Basically I'm looking after a few properties for someone - just making sure the rents paid on time, etc. There are approx 10 tenants and I just need to set up a formula so that i can input the rent paid in the month in say column a, then it tells me in another column how much rent is outstanding.

I realise there is software I can download to do this but you have to pay for it and i'm not sure he'd go for it. If I had a template that could calculate the outstanding rent based on the data I input it would make life so much easier for me.

mordeith

Are you talking about the rental amount due per tenant or an single overall figure?

Missy86

Sorry, the rental amount due per tenant...I have a worksheet per tenant.

mordeith

Okay so presumably there is set amount due at the end of each month for each tenant. Do the tenants pay in weekly installments or monthly. Unless I am misuderstanding you the easiest thing is to insert the full amount due in a particular cell. You will have a column with the amount that the tenant has paid. In order to get the amount owed you would enter this basis formula into a cell that you could call rent owed or something. Cell reference for total ammount - cell reference for amout paid e.g. =SUM(C1-A1).
If there are a few payments per month the cell reference for A1 would be cell where the total of the amount paid is.
Apologies if I'm getting this wrong!

Missy86

Thanks for getting back to me so quickly

Ok so i followed your instructions, but I wanted it to accumulate the arrears in the rent owed column. I've attached what I've done to show you what I mean..

mordeith

You just need to add a total calculation at the bottom of the rent owed column. I have done this for you in the attached

Missy86

But that doesn't work out correctly because going by the spreadsheet, the total rent owed is €100.00 - they've paid €200 in Jan and €300 in Feb.

Your probably sorry you answered me now:(

mordeith

I should have spotted this earlier but you have the formula wrong on the second month. The formula must always have B2 as the first cell reference as it contains the overall rent. You can see that your formula for Feb has B3 (which is a blank cell). I imagine you dragged the formula hence why you have B3 instead of B2

Missy86

Got it! Thanks a million for your time

mordeith

No bother

rockal

If there is any change in the 'weekly rent figure at B2' this will alter everything. Perhaps a better option is to add another column for 'weekly rent due'.