Trying to create a rental spreadsheet.

Here's an example. It requires you to enter the date of the payment in column A and the amount in column B. The weekly rate is listed in column C, but placing it in a separate table and a minor rewrite of the formula would make that entry needed only once.

Columns D and E both calculate the next due date, but you will notice a difference in the results in two rows, due to a difference in the formulas used.

Formulas

D2: =A+INT(7*B/C)

E2: =A+7*INT(B/C)

B/C calculated the number of weeks the payment will cover. Multiplying by 7 converts this to he number of days covered. INT() removes the fractional part of whatever number is in the brackets.

If the payment is an exact multiple of the weekly rate, either formula will return the same result.

If the payment is not an exact multiple of the weekly rate, the result of B/C will include a fractional part.

The result of the part after the + sign in the first formula is the number of full days covered by the payment.

The result of the part after the + sign in the second formula is the number of full weeks covered by the payment, multiplied by 7, and will always place the Next due date on the same weekday as the payment.

Note that no provision has been made for carryover of the 'extra days.'

Regards,

Barry

Galutus wrote:

I'm wondering if there is a way to format a column so that when I put the rent paid in one column, it automatically calculates the date it is paid up till? The rent is rarely paid weekly, it might be 2, 4 or 5 weeks at a time so I was hoping to be able to have a column that calculates when it is due again.

You might try something like this to keep track of irregular rent payments and when your next payment is do.

"Accrual" simply means the rent attributable to that period based on the rental rate (the rate for convenience in date arithmetic is converted into a daily rate in B3 of the 'Inputs' table). The accrued rent is compared to the rent actually paid. If you've paid more than what has accrued then column E shows how far in the future you are fully paid. If you've paid less than what is accrued, column E declares that you are behind.

The first row of the table is defined as a Header Row and the last row as a Footer Row.

Data is input in cells where font is blue. Everything else is calculated via formula.

The formulas in the 'Inputs' table:

C3: =B1÷B2

The formulas in the 'Accruals and payments' table:

A2: =A1

A7: =TODAY()

B7: =SUM(B)

C2 (copied down to the rest of cells in body of that column):

=DUR2DAYS(A2−A1)*Inputs::$B$3

C7: =DUR2DAYS(A$7−$A$1)*Inputs::B3

D2 (copied down to rest of cells in body of that column):

=DUR2DAYS(A$7−$A$1)*Inputs::B3

D7: =B7−C7

E2 (copied down to rest of cells in that column):

=IF(D2<0,"Behind!",A2+D2/Inputs::$B$3)

E7: =IF(D7<0,"Behind!",A7+D7/Inputs::$B$3)

To add payments as you go along simply add a row in the body of the table by choosing 'Add Row Below' from the dropdown by the last body row number.

The red warning triangles will disappear after you fill in the date and payment amount in the new row.

SG

What you are trying to do can be done but you will need to write an Apple Script to handle it. I suggest you go to this web site that has all sorts of examples of Apple Scripts for Numbers.

https://iworkautomation.com/index.html

You may not need an AppleScript for this. It depends on what you are trying to do. It's a little hard to tell from your description. What do you mean by "it automatically calculates the date it is paid up till." Can you give an example?

SG

Galutus wrote:

ok, so the formula given for row D, is the same as the one given for C7,

Oops! I copied the wrong formula into the post above. The formula in D2, copied down through the rest of the cells in the body of that column, should be:

=SUM(B$2:B2)−SUM(C$2:C2)

This calculates a "running sum" of the cells in column B up to the row the formula is on, and subtracts a "running sum" of the corresponding cells in column C. In other words it's subtracting what has "accrued" up to that date from what has been paid so far up to that date.

The formula in D7 (where row 7 is defined as a Footer Row) is:

=B7−C7

SG

Galutus wrote:

I did make a change with the last formula because it was subtracting the total of all of column C, when each cell in C is already an accumulative total... does that make sense??

In C2 I have =DUR2DAYS(A2−A1)*Inputs::$B$3. When copied down to C3, this becomes: =DUR2DAYS(A3−A2)*Inputs::$B$3

Note that A2-A1 has now become A3-A2. Unlike the formula in column D (which has $ "anchor" that tells it to expand the range and calculate cumulative totals) this one does not calculate a cumulative total. Rather what it is doing is taking the difference between the cell in column A on that row and the cell in column A on the row immediately above it, thus getting the number of days since the previous row only. Then it multiplies that result by the "rent per day'"to derive the additional rent "accrued" for that period only.

So I think my original formula may be correct. Still, there's plenty to puzzle through here to double-check to make sure it's doing the right thing! Won't hurt to take out a calculator to double-check it, at least the first time.🙂

Once you're satisfied that it's doing the right thing, it should be easier than a calculator and less prone to error. And of course it will make it easier and quicker to see the effect of different payment amounts.

Thanks for posing such an interesting problem.

SG

If you have further questions about getting formulas to work, don't hesitate to post them. Thanks for the green check!

SG