Microsoft Excel 2007

My knowledge of excel is limited.

I want to set up a simple spreadsheet template for rent received. I have tenants who may chose to pay rent weekly, fortnightly or monthly.

Start with a cell that says their rent is xxx.xx ($300) pw

Say their rent is $300 per week.

In column A I enter date rent received.

The period starts on say 1st March 2010 (to be shown as 01/03/2010 european format) and $300 would pay their rent up to 07/03/2010. So that when I enter 300 in cell B1 it shows up as (paid to) 07/03/2010 in the adjoining cell C1. If they paid 320.50 it would show up as (a credit) of 20.50 in the next adjoining cell D1 and if they paid 280 it would show as -20 in D1.

If they paid 620 on 01/03/2010 it would show rent paid to 14/03/2010 C1 and a 20 credit in D1.

Also in colum E a running total of colum D which would show if they had any accumulated credit or debit.

✔ Best Answer

Hi, Just doing this with formulas is rather limiting.

The following will give you some of the features you want, and it works in OpenOffice.I left row 1 & 2 empty to leave room for some more headings if required.

Rows 3 & 4 are headings

Each Tenant takes up two rows, so that date of payment is on one row and amount paid is on the next row.To make this work each payment is recorded along with the payment date. I have used columns P to HO for the payment record. For someone paying weekly this would last for 4 years.

Enter the following text in cells. If I have two cells for one text, merge the cells and centre the text, and enter the text once.

Row 3

B3 Name C3 Address D3 Weekly

E3/F3 First Payment

G3/H3 Start of tenancy

I3/J3 Last payment

K3/L3 Balance

M3/N3 Paid until

O3 Payment

Row 4

D4 Rate E4 $ F4 Date G4 Date H4 Day I4 $

J4 Date K4 $ L4 Days M4 Week of N4 Day O4 recordOther rows O5 date O6 $

Formulas:

Enter these formulas:

H5=IF(D5="","",DAY(G5))

I5=IF(D5="","",IF(ISNA(HLOOKUP(J5,P5:HO6,2,FALSE)),"",HLOOKUP(J5,P5:HO6,2,FALSE)))

J5=IF(D5="","",IF(MAX(P5:HO5)=0,"",MAX(P5:HO5)))

K5=IF(D5="","",(SUM(P6:HO6)+E5)-INT((SUM(P6:HO6)+E5)/D5)*D5)

L5=IF(D5="","",INT(K5/(D5/7)))

M5=IF(D5="","",G5+(INT((SUM(P6:HO6)+E5)/D5)*7))

N5=IF(D5="","",M5+L5)Select cells D5 to O6

Now drag these cells down, as many pairs of rows as you have tenants.The formulas all test the weekly rate and if the weekly rate is empty, the formulas all return ""

For the first tenant, add data in cells D5 to G5 and enter dates and payment amounts (after the initial payment) starting with first regular payment date in P5 and amount in P6.

I hope that this gives you something to work on.

I haven't tested this with much data so you need to look at results closely when you first start using it, to be sure it is doing what you expect.One problem is that when you change weekly rates you will need to start a new pair of rows as the paid until calculation is based on one weekly rate.

Regards

There may be a Template available for download from Microsoft's Office Site. However, since you have Excel, I assume you have Access. There is a Real Estate Template right in Access that allows you to manage rental properties. Perhaps you'd like to try that.

LIR

A couple of questions... 1 - Just curious: What does the word

Mixeisiftin your subject line mean?2 - Is there some limit below which the tenant does not get credit for the payment?

In other words, per your OP, if the tenant pays $280, it sounds like you would show that as rent paid up to the next period but with a $20 debit. But what if the tenant only paid $250? or $200? or even just $1?

At what point does the tenant

notget credit for the payment and have it just show as a credit without changing the "paid up to" date?

Hi Jennifer SUMN, I have a program called Open Office.org by Sun Systems. Which is a generic Excel

I cant find Access on my pc

Hi DerbyDad03 I have no idea how Mixeisift got into the heading ? Ignore it.

Good thinking about credits.

I think if they do not pay the full amount due then it should just be shown as a credit and not have the date click over until enough credits make a full week. Then (if not to hard to program) once enough credit = a full week have the date click over leaving any excess credit in the credit Debit/column.

If the credits / dates are to hard to program I can manualy add any credit showing when they next make a payment to that payment.

It is not often a tenant pays an odd amount, so its no big deal.I would need a start date for the rent as a reference or starting point, usualy the start date of the lease.

Note. The date paid in column A may not be the same as the date rent is due. The date in column C would be the next date due.

This type of project can be very simple or very complicated because of the variabilities that could be encountered. It might make sense to use a packaged software application as Ms. Sumn suggested, instead of trying to re-invent the wheel. I'm sure you know that there is more to tracking rental expenses than just who paid their rent when. Taxes, depreciation, maintenance, etc. all have to be tracked carefully if you are going to be successful. An application written to handle of all of these is probably a good investment.

Quicken (click here) has one, as I'm sure many other companies do.

That said, I played around a bit and came up with this macro based solution that might work.

To see how this works, start by setting up a spreadsheet as follows:

A B C D E 1 Lease Amount Paid Credit Weekly Start Credited Through On Rate Date Date Books ----------------------------------------------- 2 30/12/09 300 06/01/10 300Important Note:

*** All of the words are in Row 1, all of the numbers are in Row 2. ***

I'm assuming a weekly rate of $300 and I'm assuming the tenant paid the first week's rent.The code uses the Weekly Rate from E2 for all of the calculations, so if you raise the rent, the sheet will still work.

Now, Right-Click the sheet tab and paste the code below into the window that opens.

When you click in the next empty cell in column B, you will see an Input Box asking for the amount of rent the tenant just paid. The code will:

1 - Add that amount it to any previous credit

2 - Put that total in the cell you selected

3 - Determine the number of weeks the total payment covers and update the Paid Through date

4 - Determine the new credit (if any) and put that value in Column D.Each time you click in the next empty cell in Column B, the process will repeat. Try values that are less than the Weekly Rate, more than the Weekly Rate and multiples of the Weekly Rate to see what happens.

Private Sub Worksheet_selectionChange(ByVal Target As Range) Dim tPayment, tCredit 'Check if the first empty cell in Column B has been selected If Target.Cells.Count = 1 Then If Target.Column = 2 And Target = "" Then If Cells(Target.Row - 1, 2) <> "" Then 'If yes, then get the payment amount from user tPayment = Application.InputBox("Enter Payment Received") 'Quit if user Cancels If tPayment = False Then Exit Sub 'Get credit from Column D tCredit = Cells(Target.Row - 1, "D") 'Add payment and credit Target = tPayment + tCredit 'Clear previous Credit Cells(Target.Row - 1, "D") = "" 'Put formula to determine Paid Through Date in Column C Cells(Target.Row, "C").Formula = _ "=ROUNDDOWN($B$" & Target.Row & "/$E$2,0)*7 + $C$" _ & Target.Row - 1 'Put formula to determine current credit in Column D Cells(Target.Row, "D").Formula = _ "=MOD($B$" & Target.Row & ",$E$2)" End If End If End If End Sub

Hi,

Thanks for the info but I am lost before I start .

Where do I find "the sheet tab" ?

Hi, The sheet tab is the tab at the bottom of the Excel window that has the name of the worksheet.

When you right-click on it you will get a short menu that includes 'View Code'.Select View Code and a new window opens.

Paste the visual basic code given by DerbyDad03 into this window.

Add one more line at the end:

End Sub

Then save the changes (File - Save)

Close the window and return to the main Excel Window.I note that in one of your responses you refer to OpenOffice, but the heading to your post states that you have Excel 2007. Do you have Excel as well as OpenOffice?

Regards

Humar, Good catch on the End Sub line - bad copying on my part- I've added it to the code.

Also good catch on the Open Office issue.

This isn't going to work with OOO3.

I strongly suggest the Quicken (or a similar) solution.

Hi DerbyDad03 & Humar,

Happy new year, thanks for your help in 2009.You guys must realise I have limited computing knowledge and skills.

I only have Open Office, which seems to convert most excell stuff OK. I do not want to have to buy another program (Quicken).

I have cut and pasted your formular on to a blank Open Office spreadsheet in cell A1 and all I got was all your formular written all over the page. I think I may be doing somthing wrong ?

Never mind the Macro code that I offered. It is not a formula, it is "program" that can be used to manipulate an Excel worksheet. Unfortunately, it will not work on OOO3 (Open Office).

DerbyDad03 Thanks for your help, its appreciated.

Are you able to write a similar program that will work in 0003

or am I streching the frendship.

My experience with OOo3 is very limited. I don't even have it installed on any of my machines. It is my understanding that the programming code for OOo3 is very different than Excel VBA and I've never had the need to learn it.

However, according to this site, there is apparently some compatibility between Excel VBA and OOo3.

http://www.linuxforu.com/how-to/vba...

Since I don't have OOo3 installed on any of my machines - and have no real need to install it - I can't tell you just how "compatible" my code would be.

At a minimum, I'm sure the formulae that my code places in the cells would need to be modified to match OOo3's syntax.

Now, although you didn't ask, and even though you said

I do not want to have to buy another program (Quicken)I am going to suggest it again.If you are trying to run any type of successful business, you have to have the proper tools. For ~US$128 (which I assume could be written off as a business expense) I can't imagine why you would want to rely on something written for you by a stranger on a website.

http://www.amazon.com/o/ASIN/B002KI...

Since you yourself have said that your knowledge of Excel is limited, who is going to maintain the spreadsheet for you? What happens if you need more features? What happens if an upgrade to the software or something you do blows the spreadsheet up?

In addition, you said that you are using OOo3

"which is a generic Excel". That is not true. OOo3 is aalternativeto Excel. It is a completely different application that provides many of the same features as Excel and can open/save some Excel files, but it isnota "generic" version of Excel.If you are really set on using OOo3 for this project, I suggest that you try your question in an OOo3 forum, such as this one or something similar.

http://www.oooforum.org/forum/viewf...

We don't get a lot of OOo3 questions in this forum, so I don't know if you'll get the answers you are looking for here.

Good Luck!

Hi, Just doing this with formulas is rather limiting.

The following will give you some of the features you want, and it works in OpenOffice.I left row 1 & 2 empty to leave room for some more headings if required.

Rows 3 & 4 are headings

Each Tenant takes up two rows, so that date of payment is on one row and amount paid is on the next row.To make this work each payment is recorded along with the payment date. I have used columns P to HO for the payment record. For someone paying weekly this would last for 4 years.

Enter the following text in cells. If I have two cells for one text, merge the cells and centre the text, and enter the text once.

Row 3

B3 Name C3 Address D3 Weekly

E3/F3 First Payment

G3/H3 Start of tenancy

I3/J3 Last payment

K3/L3 Balance

M3/N3 Paid until

O3 Payment

Row 4

D4 Rate E4 $ F4 Date G4 Date H4 Day I4 $

J4 Date K4 $ L4 Days M4 Week of N4 Day O4 recordOther rows O5 date O6 $

Formulas:

Enter these formulas:

H5=IF(D5="","",DAY(G5))

I5=IF(D5="","",IF(ISNA(HLOOKUP(J5,P5:HO6,2,FALSE)),"",HLOOKUP(J5,P5:HO6,2,FALSE)))

J5=IF(D5="","",IF(MAX(P5:HO5)=0,"",MAX(P5:HO5)))

K5=IF(D5="","",(SUM(P6:HO6)+E5)-INT((SUM(P6:HO6)+E5)/D5)*D5)

L5=IF(D5="","",INT(K5/(D5/7)))

M5=IF(D5="","",G5+(INT((SUM(P6:HO6)+E5)/D5)*7))

N5=IF(D5="","",M5+L5)Select cells D5 to O6

Now drag these cells down, as many pairs of rows as you have tenants.The formulas all test the weekly rate and if the weekly rate is empty, the formulas all return ""

For the first tenant, add data in cells D5 to G5 and enter dates and payment amounts (after the initial payment) starting with first regular payment date in P5 and amount in P6.

I hope that this gives you something to work on.

I haven't tested this with much data so you need to look at results closely when you first start using it, to be sure it is doing what you expect.One problem is that when you change weekly rates you will need to start a new pair of rows as the paid until calculation is based on one weekly rate.

Regards

Thanks guys,

I will give Humar's formular a go.DerbyDad03, I note what you say. I have found a program called RentMaster which I am trialing.

Thanks for your help.

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History