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.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook here.

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

*shops rented by different renters. We will calculate the rents for the month of*

**5***from the shops. Here, is the*

**October 2022***table,*

**Terants’ Information**### 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
cell and type the equal sign.**C5** - Then, select the
sheet.**Tenant’s Info** - Consequently, the sheet will be opened.

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

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

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

**Read More: ****How to Calculate Monthly Payment with APR in Excel**

### 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
cell.**E5** - Secondly, go to the
tab.**Data** - Thirdly, select the
command from the**Data Validation**option.**Data Tools** - Consequently, a prompt will be on the screen.

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

- Then, select the
sheet.**Tenants’ Info**

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

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

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

**Read More: ****How to Make a Cash Payment Voucher Format in Excel**

**Similar Readings**

**How to Create Line of Credit Payment Calculator in Excel****Calculate Auto Loan Payment in Excel (with Easy Steps)****How to Calculate Coupon Payment in Excel (4 Suitable Examples)****Calculate a Lease Payment in Excel (4 Easy Ways)**

### 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
column.**Monthly Rent** - Then, start typing the money received from the renters in the
column.*Amount Received*

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

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

**Read More: ****How to Calculate Monthly Payment in Excel (2 Handy Ways)**

### 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
cell and write,**H5**

`=G5+`

- Then, select the
sheet.**Tenants’ Info**

- From the sheet select the
cell which is the past due for this renter.**H5** - 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.

**Read More: ****Create Progressive Payment Calculator in Excel (with Easy Steps)**

**Similar Readings**

**How to Calculate Monthly Mortgage Payment in Excel (2 Ways)****Calculate Down Payment in Excel Using VLOOKUP****How to Calculate Balloon Payment in Excel (2 Easy Methods)****Calculate Loan Payment in Excel (4 Suitable Examples)**

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

**Read More: ****How to Calculate Monthly Payment on a Loan in Excel (2 Ways)**

### 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
cell and write the following formula,**F10**

`=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
and**Amount Due (For the Current Month),**columns.**Total Due Amount**

**Read More: ****How to Calculate Car Payment in Excel (with Easy Steps)**

### 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
cell under the**C15**column.*Payment Received* - Then, select the
sheet.**Rent Payment**

- In the Rent Payment sheet, select the
cell under the**F5**column. This is the amount received for**Amount Received**from the first shop.**October** - 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
and*Due Payment*cells for that shop for**Total Payment**.**October**

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

`=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
and the**Due Payment**for that shop.**Total Due**

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

**Read More: ****How to Create Payment Advice Format in Excel (with Easy Steps)**

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