How to Create Rent Payment in Excel Spreadsheet

The Rental Payment Spreadsheet is a very powerful tool to keep track of all the rents. It allows users to arrange and calculate their rents. It can be used for many purposes i.g; keeping track of the rents of a house or shop. In this article, we will show how to create rent payment Excel spreadsheet.

Download Practice Workbook

You can download the practice workbook here.

Rent Payment Spreadsheet.xlsx

Step-by-Step Procedures to Create Rent Payment in Excel Spreadsheet

In this article, we will discuss how to create a rent payment Excel spreadsheet in an exhaustive way. Here, we will the data of an owner who has 5 shops rented by different renters. We will calculate the rents for the month of October 2022 from the shops. Here, is the Terants’ Information table,

Step 1: Writing Name of Shops

In this step, we will write the names of the shops from the information table into our rent payment spreadsheet. We will use the cell reference of one sheet in another sheet. Follow the steps below to do so.

Firstly, select the C5 cell and type the equal sign.
Then, select the Tenant’s Info sheet.
Consequently, the sheet will be opened.

Then, select the D5 cell.
The formula bar will show that the value of the D5 cell is added to the formula.
Finally, hit Enter.

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

After that, move the cursor down to autofill the rest of the cells.

Step 2: Determining Payment Method

In this stage, we will add payment options for the renters. We will insert a drop-down list in each of the cells under the Payment Method column.

Firstly, select the E5 cell.
Secondly, go to the Data tab.
Thirdly, select the Data Validation command from the Data Tools option.
Consequently, a prompt will be on the screen.

In the prompt, first, go to Setting.
Then, select List under the Allow option.
Finally, select the upward arrow to the right of the box under the Source option.

Then, select the Tenants’ Info sheet.

From the sheet, first, select the cell range C12:C14.
Then, upon inclusion of the cells in the Data Validation box, select the downward arrow sign to the right of the box.

Back in the prompt, those informations will be added in the Source box.
Finally, hit OK.

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

Similar Readings

Step 3: Calculating Monthy and Total Due Amount

In this article, we will calculate the deficits in amounts given by the renters from the actual rent price. We will subtract the amount received from the renters from the rent price.

To begin with, write down the amount of rent in the Monthly Rent column.
Then, start typing the money received from the renters in the Amount Received column.

After that, select the G5 cell and write down the following formula,

D5-F5

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

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

Step 4: Calculating Total Due Amount

The renters often do not pay the full amount. So in this step, we will calculate the total amount that is due by summing up the unpaid amount of this month and the past due.

Click on the H5 cell and write,

=G5+

Then, select the Tenants’ Info sheet.

From the sheet select the H5 cell which is the past due for this renter.
Then hit Enter.

Consequently, the past due will be added to this month’s unpaid amount.
Finally, lower the cursor down to find the total due for the rest of the shops.

Similar Readings

Step 5: Evaluating Payment Delay

Renters often pay the rent in due time. So, we need to keep track of the delay in payment. In this step will calculate the payment delay.

Firstly, select the K5 cell and write the following,

=J5-I5

Press Enter.

Consequently, we will find the delay for that particular renter.
Then, move the cursor down to autofill the rest of the cells.

Step 6: Calculating Total Amount

Finally, we need to calculate the total amount received, the total monthly due, and the total overall due. We will use the SUM function to carry this out.

To start with, select the F10 cell and write the following formula,

=SUM(F5:F9)

Then, hit Enter.

Consequently, 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

Finally, we will summarize the transactions for each shop over a year. We will show the summary only for October but in practice, we will have to do it for each month.

Firstly, select the C15 cell under the Payment Received column.
Then, select the Rent Payment sheet.

In the Rent Payment sheet, select the F5 cell under the Amount Received column. This is the amount received for October from the first shop.
Then, hit Enter.

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

After that, choose the C18 cell and write the following formula down,

=SUM(C6:C17)

Consequently, we will get the total amount received from that shop in a year.

Since we are calculating for October only, we will show only this month. In practice, we will have data for all the months.

Then, calculate the total yearly amount for the Due Payment and the Total Due for that shop.

Finally, do the same for the rest of the shops.

Conclusion

In this article, we have discussed how to create a rent payment Excel spreadsheet from the scratch. This is one of many variants of this type of spreadsheet. But the basic structures will be the same.

<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!