Adnan Masruf
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.
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.
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 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.
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 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.
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 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.
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 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.
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 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.
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.
