# Excel: formula Rent\$ = date

December 29, 2009 at 20:32:57
Specs: Windows Vista

 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.

See More: Excel: formula Rent\$ = date

#1
December 30, 2009 at 05:00:28

 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

Report •

#2
December 30, 2009 at 08:36:19

 A couple of questions...1 - Just curious: What does the word Mixeisift in 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 not get credit for the payment and have it just show as a credit without changing the "paid up to" date?

Report •

#3
December 30, 2009 at 16:37:12

 Hi Jennifer SUMN,I have a program called Open Office.org by Sun Systems. Which is a generic ExcelI cant find Access on my pc

Report •

Related Solutions

#4
December 30, 2009 at 17:25:32

 Hi DerbyDad03I 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.

Report •

#5
December 30, 2009 at 20:26:43

 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 300 ```Important 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 credit2 - Put that total in the cell you selected3 - Determine the number of weeks the total payment covers and update the Paid Through date4 - 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 ```

Report •

#6
December 30, 2009 at 22:07:22

 Hi,Thanks for the info but I am lost before I start . Where do I find "the sheet tab" ?

Report •

#7
December 31, 2009 at 05:47:17

 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 SubThen 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

Report •

#8
December 31, 2009 at 06:30:25

 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.

Report •

#9
December 31, 2009 at 16:53:48

 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 ?

Report •

#10
December 31, 2009 at 17:04:23

 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).

Report •

#11
December 31, 2009 at 21:03:33

 DerbyDad03Thanks for your help, its appreciated.Are you able to write a similar program that will work in 0003or am I streching the frendship.

Report •

#12
January 1, 2010 at 10:20:12

Report •

#13
January 1, 2010 at 10:23:55

 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 headingsEach 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 3B3 Name C3 Address D3 Weekly E3/F3 First PaymentG3/H3 Start of tenancyI3/J3 Last paymentK3/L3 BalanceM3/N3 Paid untilO3 PaymentRow 4D4 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 O6Now 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

Report •

#14
January 1, 2010 at 22:37:01

 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.

Report •