 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007Need to solve for the total value of a commercial lease
I hope my grid below looks OK once it is posted. I need a formula to solve for cell G3. I need a single cell formula to solve for the total value of a commercial lease given the initial annual rent, the rent increase percentage and how often (in years) that the rent is increased. Any ideas? A B C D E F G 1 Total Value 2 Tenant SF $/SF Term (yrs) Increase amount % Every X years of Lease 3 Ross 30,000 7.00 10 5% 2 ??? "Grid" never work well. Please confirm: B3: 30,000 Swiss francs is the annual lease payment C3: 7.00 converts SF to $US D3: Is term of lease (10 years) E3: Is the amount the lease will increase by (5%) and this will be compounded, or you add 5% of initial payment every X years? F3: The lease payment will increase every x years (here every 2 years). So in year 2 (or year 3 ?) the lease payment is 33,000? What is it in year 4 - You want to know how much the lessee will part with over the life of the lease? Depending on how the questions are answered I get: 345,000SF, 347760SF, 375,000SF or 384621.3SF. Which (if any) is correct? OR, (a bit more difficult) you want to know the present value of the lease? In which case there are supplementary questions: (A) Is the payment made at the start or the end of the year? (B) What value will be used for the bank rate (the amount you could get on a savings deposit) during the life of the lease? best wishes  SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email >I hope my grid below looks OK once it is posted. I need a formula to > solve for cell G3. I need a single cell formula to solve for the [quoted text clipped - 11 lines] > 3 Ross 30,000 7.00 10 5% > 2 ??? Thank you for your response! SF in this case actually stands for square feet. Lets just say that the annual rent for the first year is C3. C3: $210,000 ($7*30,000). D3: 10 years - Correct, term of lease E3: 5% - Correct, this is the percentage that the lease amount increases at the interval stated below. This should be compounded. F3: 2 years - Correct, this is the interval at which the increase happens, in this case every 2 years. In this example, the first increase happens after 2 years (starts in year 3) and the next increases start in years 5, 7, and 9. G3: This is where I would like a formula that comes up with the total *undiscounted* value of the lease. In this case the correct answer is $2,320,765. Here is the information that I am trying to capture in one cell: Yr1 $210,000 Yr2 $210,000 0%incr Yr3 $220,500 5%incr Yr4 $220,500 0%incr Yr5 $231,525 5%incr Yr6 $231,525 0%incr Yr7 $243,101 5%incr Yr8 $243,101 0%incr Yr9 $255,256 5%incr Yr10 $255,256 0%incr Total$2,320,765 I would like C3, D3, E3 and F3 to all be variables. Thanks again for any help! On Oct 5, 10:46 am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca> wrote: > "Grid" never work well. Please confirm: > B3: 30,000 Swiss francs is the annual lease payment [quoted text clipped - 41 lines] > > 3 Ross 30,000 7.00 10 5% > > 2 ??? > In this case the correct answer is $2,320,765. Hi. Are you looking for something like this? =(2*((ir + 1)^yr - 1)*s)/ir where s = 210000, ir = 5%, and yr = 5. Returns: $2,320,765 SignatureHTH :>) Dana DeLouis Windows XP & Excel 2007 > Thank you for your response! > SF in this case actually stands for square feet. Lets just say that [quoted text clipped - 80 lines] >> > 3 Ross 30,000 7.00 10 5% >> > 2 ??? Thank you so much Dana! That formula does indeed work for those variables and I imagine it would in every case where each increase interval is fully captured in the term of the lease. In this case, when I change the increase interval to 3 years the formula produces a result that is $3,942.76 less than the matrix. Any ideas for how to adjust the formula eliminate that variance? > > In this case the correct answer is $2,320,765. > [quoted text clipped - 42 lines] > > Yr10 $255,256 0%incr > > Total$2,320,765 Hi. Are you looking at 3 groups of 3 payments over 9 years, plus a single $243,101 in year 10 for a total of 2,229,176 ? (10 Years stays constant) SignatureDana DeLouis > Thank you so much Dana! > That formula does indeed work for those variables and I imagine it [quoted text clipped - 50 lines] >> > Yr10 $255,256 0%incr >> > Total$2,320,765 Hi, Dana, Yes, exactly as you described - $2,229,176.25 for 10 years. Ideally, the formula would work with any lease term and increase interval or piece thereof provided in the variables. Thanks again. > Hi. Are you looking at 3 groups of 3 payments over 9 years, plus a single > $243,101 in year 10 for a total of 2,229,176 ? (10 Years stays constant) [quoted text clipped - 60 lines] > >> > Yr10 $255,256 0%incr > >> > Total$2,320,765 Hi. I think this should work: s: 210000 'Start Value ir: 0.05 'Interest Rate years: 10 'Years Grp: 3 'Groups Ng =INT(years/Grp) 'Number of Groups Rg =years-Grp*Ng 'Remaining Group Fx =Grp*(((ir + 1)^Ng - 1)*s)/ir + Rg * (1 + ir)^Ng*s = $2,229,176 SignatureHTH :>) Dana DeLouis Windows XP & Excel 2007 > Hi, Dana, > Yes, exactly as you described - $2,229,176.25 for 10 years. Ideally, [quoted text clipped - 69 lines] >> >> > Yr10 $255,256 0%incr >> >> > Total$2,320,765 This array formula works for an increase in rent after 1 or 2 years. After entering the formula, you must press Ctrl+Shift+Enter, not just enter. G3: =(C3+SUM(C3*(1+E3)^ROW(INDIRECT("1:"&INT(D3/F3)-1))))*F3 I know Professor Liengme will come up with something better for other years. I am no mathematician. Tyro P.S. (Tyro = beginner) > C3: $210,000 ($7*30,000). > D3: 10 years - Correct, term of lease [quoted text clipped - 7 lines] >*undiscounted* value of the lease. In this case the correct answer is > $2,320,765.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.