+ Reply to Thread
Results 1 to 6 of 6

Quarterly rent to annual totals

  1. #1
    Registered User
    Join Date
    02-24-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    5

    Quarterly rent to annual totals

    Hi all

    Firstly apologies if this has been dealt with before!

    I have a sheet that contains quarterly rental data for an office building in a single row. Each cell along the row is the next quarter in the sequence. The start date for the rent is the first quarter after construction finishes but this might change if I build in a void period or if we assume construction takes longer..

    In another page I would like to express the rent annually y1 y2 y3 etc.. With y1 starting on the first quarter that rent is paid. Y1 would be the first 4 quarters (1-4). Y2 would be quarters 5-8 and so on

    Can anyone help with this? It's been driving me nuts!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Quarterly rent to annual totals

    Might be better to attach a sample Excel file (the FAQ describes how to), so that we don't have to guess which cells/columns you are using, or the names of your sheets etc.

    Do you have more than one building? If so, wouldn't it be a bit confusing if the start of the years are dependent on the completion date/date of first occupancy of each building ?

    Pete

  3. #3
    Registered User
    Join Date
    02-24-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    5

    Re: Quarterly rent to annual totals

    Hi Pete

    Sorry for the delay getting back to you - have only just managed to get back on a computer!

    I have attached the sheet that I have been working on - there are a couple of lines added in that were an effort to set it up so I could get to an annual rent set out in a row where each cell is one year later than the previous. Hope that makes sense

    Cillian
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,421

    Re: Quarterly rent to annual totals

    Try this formula:
    Please Login or Register  to view this content.
    Here it is applied to a separate page, as specified in OP, however with the actual years in row one as opposed to Y1, Y2 ...
    Copy of Excel Help Sheet.xls
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    02-24-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    5

    Re: Quarterly rent to annual totals

    Back in the office finally

    Thank you for that Jete

    However, I son't think your approach is not quite yielding the result I am after - in row 13 you can see the annual rent amounts. I would like to express these without the gaps in the cells in row 17. I expect the result for year 1 to be 4,516,840

    The rent could start in any quarter of the year - if in Q3 of 2018 for example, year 1 should be the total rent from Q3 2018 to end of Q2 2019.. This is why I would rather express the rent in terms of year 1,2,3,4,5 etc rather than 2016,2017,2018 etc.

    If you have any other ideas that would be great

    Best

    Cillian

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,421

    Re: Quarterly rent to annual totals

    Here is an array formula* that will give those results based on the values in row 13 of the Rent Review sheet:
    Please Login or Register  to view this content.
    Array formulas are activated by pressing the Ctrl, Shift and Enter keys simultaneously. Here is a copy of your file with the formula applied:
    Copy of Excel Help Sheet.xls
    Let me know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating annual compounded rent increases
    By WarrenLF in forum Excel General
    Replies: 10
    Last Post: 05-11-2022, 12:44 AM
  2. Replies: 1
    Last Post: 05-07-2014, 06:19 PM
  3. [SOLVED] How do I group daily totals into weekly/monthly/quarterly totals
    By situationroom in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2013, 09:58 AM
  4. Chart- Annual & Quarterly
    By bookman3 in forum Excel General
    Replies: 1
    Last Post: 06-09-2010, 04:13 PM
  5. Replies: 6
    Last Post: 06-07-2010, 04:57 AM
  6. Annual and quarterly data in one chart
    By henrik2h in forum Excel General
    Replies: 1
    Last Post: 04-03-2009, 08:55 AM
  7. IRR calculations Annual vs Quarterly
    By SimpleSmile in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-22-2008, 08:59 PM

Bookmarks

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