How to Create a Rent Payment Spreadsheet in Excel (Step-by-Step)

We have the data of an owner with 5 shops rented by different renters. We will calculate the rents for October 2022 from the shops. Below is the Terants’ Information table.

rent payment excel spreadsheet

Step 1: Writing the Name of Shops

Select cell C5 and type the equal sign.
Select the Tenant’s Info sheet.
The sheet will be opened.

selecting sheet to create a rent payment excel spreadsheet

Select cell D5.
The formula bar will show that the value of cell D5 is added to the formula.
Press Enter.

linking two sheets to create a rent payment excel spreadsheet

As a result, the value of the Tenants’ Info sheet will be added to cell C5 of the Rent Payment sheet. The two sheets are linked now.

Move the cursor down to autofill the rest of the cells.

autofilling the column to create a rent payment excel spreadsheet

Step 2: Determining Payment Method

Select cell E5.
Go to the Data tab.
Select the Data Validation command from the Data Tools option.
A prompt will open on the screen.

using data validation command to create a rent payment excel spreadsheet

In the prompt, go to Setting.
Select List under the Allow option.
Select the upward arrow to the right of the box under the Source option.

Select the Tenants’ Info sheet.

From the sheet, select the cell range C12:C14.
Once the cells are included in the Data Validation box, select the downward arrow sign to the right of the box.

selecting the range of payment methos to create a rent payment excel spreadsheet

Back in the prompt, that information will be added in the Source box.
Press OK.

A drop-down list of the payment options will be added in cell E5.
Repeat the process for the rest of the cells in that column.

adding list in the drop-down list of a cell to create a rent payment excel spreadsheet

Similar Readings

Step 3: Calculating Monthy and Total Amount Due

Enter the rent amount in the Monthly Rent column.
Enter the money received from the renters in the Amount Received column.

inserting data to create a rent payment excel spreadsheet

Select cell G5 and enter the following formula:

D5-F5

This is the difference between the monthly rent and the amounts received.

inserting formula to create a rent payment excel spreadsheet

Lower the cursor to the last cell to autofill the cells.

Step 4: Calculating Total Amount Due

Click on cell H5 and enter,

=G5+

Select the Tenants’ Info sheet.

adding formula to create a rent payment excel spreadsheet

From the sheet, select cell H5, which is the past due for this renter.
Press Enter.

The past due will be added to this month’s unpaid amount.
Lower the cursor to find the total due for the rest of the shops.

Similar Readings

Step 5: Evaluating Payment Delay

Select cell K5 and enter the following:

=J5-I5

Press Enter.

typing date difference formula to create a rent payment excel spreadsheet

We will find the delay for that particular renter.
Move the cursor down to autofill the rest of the cells.

Step 6: Calculating Total Amount

Select cell F10 and enter the following formula:

=SUM(F5:F9)

Press Enter.

summing up the amount received to create a rent payment excel spreadsheet

We will get the total monthly amount of money received by the owner.
Do the same for the Amount Due (For the Current Month), and Total Due Amount columns.

Step 7: Summarizing Transactions

Select cell C15 under the Payment Received column.
Select the Rent Payment sheet.

selecting another sheet to create a rent payment excel spreadsheet

Select cell F5 under the Amount Received column in the Rent Payment sheet. This is the amount received from the first shop for October.
Press Enter.

As a result, the received amount will be added to the cell.
Move the cursor to the right to fill the Due Payment and Total Payment cells for that shop for October.

calculating monthly and total due of shops to create a rent payment excel spreadsheet