# Projecting rental income over fixed term

1. ## Projecting rental income over fixed term

Hi there,

I am trying to project revenue flows from rental income over a 3 year period.
We will be renting different quantities each month for a fixed term
I would like calculate the revenue received and amount outstanding to be paid by the customer at the end of each year.
Would also like it to be adaptable, ie change the rental term
I manged to hard code it by using an offset formula to add the number of units rented (this worked in the short term as we are not projecting past a rental period), but i am unsure how to make it variable

For example

Monthly rent: 300
Term: 36 months (like to be able to adjust this)
Units rented:
Jan 18: 5
Feb 18: 7
Mar 18: 3
Apr 18: 4
May 18: 9
etc

Would also like to build in a deferral period for payments to start and also a deposit on first payment, ie 2 months up front)
Hope the above makes sense!

2. ## Re: Projecting rental income over fixed term

Hello Gbrel and Welcome to Excel Forum.
I mocked up a sample of what I envision as being useful in forecasting rental income.
Both the amount and term are variable, test by changing B1 and/or B2.
I didn't address the amount outstanding, and would encourage you to upload a sample* showing your thoughts as to how that might be managed/arranged. Put values in manually so that we can attempt to automate the results using formulas or code.
In the sample attached range B7:M18 is populated using the formula:
Formula:
`Please Login or Register  to view this content.`

The "Deposit" column is populated using the formula: =INDEX(B\$4:M\$4,MATCH(A7,B\$3:M\$3,0))*B\$1
The"(End term)" column, which assumes that the last month's rent was paid in advance, is populated using the formula:
Formula:
`Please Login or Register  to view this content.`

Other columns are populated using straight forward mathematical functions.
*To attach a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1