+ Reply to Thread
Results 1 to 2 of 2

Projecting rental income over fixed term

  1. #1
    Registered User
    Join Date
    MS-Off Ver

    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

    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. #2
    Forum Guru
    Join Date
    MS-Off Ver
    Excel 2010

    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: copy to clipboard
    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: copy to clipboard
    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

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