How to offset the values in an array?

What I'm trying to do is connect the numbers in a tree diagram where I don't know the size ahead of time, such as the one in the image below that I'll use as an example, but it changes based on user input.

Tree diagram 9-8-20.JPG

I have a 2 dim array where I have the values for the tree and number of the branch. In the array I have 16 elements for the 1st dim and 4 for the second corresponding to the size of the last branch in the array and the number of branches.

What I would like to do it to go from the last branch and put the preceding values in the 2nd dim for the array. I started from the end and worked backwards. For example, (16,4)=30, (16,3)=14, (16,2)=6, and (16,1)=2. The odd number before is always the same aside from the 1st number so (15,4)=29, (16,3)=14, (16,2)=6, and (16,1)=2.

From looking at the diagram I figured that the best way to do it is with a loop and a counter. On the 1st run through it takes the bottom value for all 4 elements. Then the 3rd run through (starting with 28) it offsets 1 on the preceding branch and none from the others. (14,4)=28, (14,3)=13, (14,2)=6, and (16,1)=2. Then the 5th (starting with 26) it offsets 2 on the preceding branch and 1 on the branch 2 prior (12,4)=26, (12,3)=12, (12,2)=2, and (12,1)=2.

So it seems like there is a definite pattern there. What I don't know how to do is offset the array's values according to this kind of logic. I tried to almost brute force my way through with a whole bunch of if statements, but every time I move on the branch it messes up. I've attached my attempt.

Is there a better way to do it or can you work with what I've tried? At this point I'm burned out and frustrated. I have the array starting with 1 and if the variable isn't defined below it's a public variable defined earlier.

Please Login or Register  to view this content.

well I will respond to this. Can I ask....what exactly are you doing here? you're calling this trees and branches. but what exactly is the point? If I were to help out in this thread, which is probably already long enough (), I would have to know these things for sure:

=> what line of business are you in?
=> what are you trying to get out of shifting these numbers around inside of these arrays?
=> what is the purpose of the numbers in yellow on the diagram? (obviously the numbers in count make perfect sense)

Originally Posted by vba_php

Max,

This might just boil down to my lack of knowledge about the real estate industry and your lack of knowledge about the resources available in this particular programming language. Based on everything I have just read from you, it sounds like you don't really need to be messing around with arrays, but rather you need to make use of Excel's built-in mathematical functions and accounting functions. What do you think of that? for instance, Excel has its own built-in function to calc the present value of an amount, or even an annuity I believe. Those functions are probably heavily used by financial advisers.

So, given what I just said, please post your thoughts about it and if I can help you get this done I would obviously need to ask you some more questions about the different analytical elements involved here, their purpose and what the overall goal is for this (although you have already partially stated it). I can probably help you if I can align the business goal, in plain English, with what I know is available in VBA to use for the purpose getting the work done. You must know that all programming languages are different, although most of them share common resources.

The way you can make good use of someone like me is to let me rattle off a bunch of questions strictly related to the real estate business. That way, I know what the real estate business needs and I also know what the language can do. If you want to work with me, that's the best way to do it.

What I'm trying to do is automate the process of doing a real estate pro forma for a commercial property.

There is software available already that does it, but I was hoping to be able to do my own so that I could customize it and save on costs. The big one is called Argus. So you can look at what that does to get a general ballpark idea of what I'm trying to do, but mine doesn't have to be anywhere near as sophisticated.

What makes things complicated is that when I look at information on a property there is no standardization for how it is displayed and often times all I have are PDFs. So what I do now for every property is I enter the information from the rent roll (tenant rent information like starting and ending dates, rental rate, renewal options, etc) into excel and build a pro forma from scratch. I have a program that will convert a PDF into excel, but it isn't reliable.

Once I have the information in I do sensitivity analysis for things like purchase price, growth rate, renewal prob, etc. I also do a 2 way data table.

So what I'd like to do is to use VBA so I enter all the rental information, renewal prob, options, commissions, TI assumptions, etc and then have it generate an IRR, NPV, and also do sensitivity analysis. I figured anything I can do manually with excel I can automate with VBA.

To that end so far my program will let me enter the basic info for a tenant along with market rents and renewal options and I put the PV of the existing rent into each branch and also the PV of the renewal rent into each branch. Now what I'm trying to do is to multiply back along the branch (just like with options in the picture I put in the previous post) to figure out the PV. That's where I'm getting stuck.

Does that make sense?

Originally Posted by vba_php

Here are my thoughts, Max. Some funny, some serious:


OK, so I know what that is from here (had to refresh my memory): https://learn.roofstock.com/blog/real-estate-pro-forma


EVERYONE is trying to save on costs. And that is very sad. =(


I will look this up later, once we get futher into this whole thing. I took at look here: https://www.altusgroup.com/argus/, however they don't seem to have trial versions of their software available unless you talk to a human being. That must mean that they are not cheap! hmmmmm


for the most part, that is always true. the entire VBA object model is literally just the interface of any given office program, written in a different visible representation.


have you considered using the interface for that? or, the code has a function that can do the work too.

that should be fairly easy. It terms of what you have asked, here is what I believe you should start with as an exploration mechanism:

Excel's IRR function

Excel's NPV function

as you can see, those built-in functions take multiple values as arguments, just like you are attempting to do with your binomial tree graph example. so, given what I've said, please get back to everyone here with another post and let us know if any of this is useful and where you want to go from here. and once again, I will reiterate that YES, almost anything can be done with code that you can already do through menus in this program.

Not sure how to do that fancy quoting like you did.

Yes, Argus is extremely expensive and way too costly and complex for the properties that I'm evaluating. I also like the idea of building my own model so that I can customize it to work exactly the way I want.

What do you mean by using "the interface" to go back through the branches in a binomial tree? When I do a model in excel I use the built in IRR and NPV. The whole reason I want to do it (or at least start it) in VBA is to make the model more dynamic so it will work for varying number of tenants, renewal assumptions for each tenant (% change of renewal, vacancy if don't renew, rent if renew, TI, Leasing Commission, etc).

Basically what I'm trying to do now with VBA is come up with all the rents, CAM, expenses, etc to get an NOI as the input for IRR and NPV. The way I thought about doing that is creating a market rent schedule and current tenant rent schedule as well as a renewal schedule. Then use the rents along with renewal % to figure out the rent. Does that make sense?

Originally Posted by max3732

Who knows, maybe you can do what took me multiple subs and pages in 5 lines.

that's very possible. soooo...what are you saying now? do you want me to offer further assistance, or are you in the process of fixing what you had before and attempting to do something new and wanting to post here again when you get stuck?

If you want the former, I will look into it a little later as promised, 2 posts ago....

Originally Posted by vba_php

that's very possible. soooo...what are you saying now? do you want me to offer further assistance, or are you in the process of fixing what you had before and attempting to do something new and wanting to post here again when you get stuck?

If you want the former, I will look into it a little later as promised, 2 posts ago....

Definitely I would like further assistance with what I have now! Looking into it a little later is fine. I really appreciate it!

I'm just going through what I have so far to remind myself what I have and why I have it since I tend to get lost in the weeds and it's been a while since I've looked at the whole program. I'm putting little notes for myself (and you as well if you want to look at the code I've done so far). I put some notes already, but nothing with the big picture.

I am looking into your issue now, Max. However, I wanted to point out something else to you, that you might not be aware of. If you want to know how MASSIVE database sizes can get in the world of ""big tech"" in california, check out this link (a post by me on facebook):

Please Login or Register  to view this content.

see the number assigned to the post!? LOL. If you delimit that stupid thing out, it becomes:

10,218,791,382,081,668

that equates to 10 QUADRILLION RECORDS in facebook's network of databases. Isn't that just stupid!? Come on now, Mark Zuckerberg is not THAT cool. 10 ^ 15th power in mathematics.

https://en.wikipedia.org/wiki/Names_of_large_numbers

Max,

I have produced the following bit of help for you based on everything below, which you said 2 posts ago:

Originally Posted by max3732

Not sure how to do that fancy quoting like you did.

Yes, Argus is extremely expensive and way too costly and complex for the properties that I'm evaluating. I also like the idea of building my own model so that I can customize it to work exactly the way I want.

What do you mean by using "the interface" to go back through the branches in a binomial tree? When I do a model in excel I use the built in IRR and NPV. The whole reason I want to do it (or at least start it) in VBA is to make the model more dynamic so it will work for varying number of tenants, renewal assumptions for each tenant (% change of renewal, vacancy if don't renew, rent if renew, TI, Leasing Commission, etc).

Basically what I'm trying to do now with VBA is come up with all the rents, CAM, expenses, etc to get an NOI as the input for IRR and NPV. The way I thought about doing that is creating a market rent schedule and current tenant rent schedule as well as a renewal schedule. Then use the rents along with renewal % to figure out the rent. Does that make sense?

I did not take into account the binom tree you posted, nor did I take into account any code that you have posted thus far. Sooooo....

Originally Posted by max3732

What do you mean by using "the interface" to go back through the branches in a binomial tree?

what I meant was that the interface (the SCREEN you look at when inside Excel) should probably have most of the resources you need to get done what you need. As I said before, VBA code available for internal use in Excel is nothing more than just binary numbers that can do the same thing as pushing buttons on the screen in Excel can do.

Originally Posted by max3732

The whole reason I want to do it (or at least start it) in VBA is to make the model more dynamic so it will work for varying number of tenants, renewal assumptions for each tenant (% change of renewal, vacancy if don't renew, rent if renew, TI, Leasing Commission, etc).

well obviously some of that data (if not all of it) would somehow be rolled into the IRR() and NPV() functions. Or at least it would be related somehow. Now, since I'm not a financing professional, I don't really care to learn and/or teach myself how this is all interconnected. If I am to help you accomplish your goal, it would be your job to explain these types of things to me. And on the flip side, it would be my job to explain to you how programming logic can assist you.

Originally Posted by max3732

Basically what I'm trying to do now with VBA is come up with all the rents, CAM, expenses, etc to get an NOI as the input for IRR and NPV.

NOI I looked up:

https://www.investopedia.com/terms/n/noi.asp

so....obviously it is relevant, however I don't have the time to do the research on it anymore than looking at that page. The formula looks very simple. You probably don't need me for that.

Originally Posted by max3732

The way I thought about doing that is creating a market rent schedule and current tenant rent schedule as well as a renewal schedule. Then use the rents along with renewal % to figure out the rent.

That's up to you. That is not my part of this relationship. It is yours. However, if you have questions in a technical sense with regard to how it can assist you in compiling that data, analyzing it or anything else, don't hesitate to ask me (or anyone else here for that matter).

Now, you're up. Talk to you soon I'm sure.

[on a side note, the website investopedia is seriously out of date! they are using ASP technology that is a decade or more old! they should be using .NET from visual studio. if they did, this would be their page: https://www.investopedia.com/terms/n/noi.aspx

but alas, a server error results. =( I think someone needs to contact those folks! LOL

Originally Posted by vba_php

Max,

I have produced the following bit of help for you based on everything below, which you said 2 posts ago:

I did not take into account the binom tree you posted, nor did I take into account any code that you have posted thus far. Sooooo....


what I meant was that the interface (the SCREEN you look at when inside Excel) should probably have most of the resources you need to get done what you need. As I said before, VBA code available for internal use in Excel is nothing more than just binary numbers that can do the same thing as pushing buttons on the screen in Excel can do.

well obviously some of that data (if not all of it) would somehow be rolled into the IRR() and NPV() functions. Or at least it would be related somehow. Now, since I'm not a financing professional, I don't really care to learn and/or teach myself how this is all interconnected. If I am to help you accomplish your goal, it would be your job to explain these types of things to me. And on the flip side, it would be my job to explain to you how programming logic can assist you.


NOI I looked up:

https://www.investopedia.com/terms/n/noi.asp

so....obviously it is relevant, however I don't have the time to do the research on it anymore than looking at that page. The formula looks very simple. You probably don't need me for that.


That's up to you. That is not my part of this relationship. It is yours. However, if you have questions in a technical sense with regard to how it can assist you in compiling that data, analyzing it or anything else, don't hesitate to ask me (or anyone else here for that matter).

Now, you're up. Talk to you soon I'm sure.

[on a side note, the website investopedia is seriously out of date! they are using ASP technology that is a decade or more old! they should be using .NET from visual studio. if they did, this would be their page: https://www.investopedia.com/terms/n/noi.aspx

but alas, a server error results. =( I think someone needs to contact those folks! LOL

Hmmm. Well I already know how to do what I want in excel. Where I need help is in getting VBA to do it and with different data.

What if I do a sample excel sheet with a simplified, fictitious property but shows what I'm trying to do. If I sent that to you would you be able to do get it to work with a different number of tenants and assumptions? That's what I've been trying (unsuccessfully) to do in VBA

More than likely if you sent me something that had every detail laid out in terms of what part of the process the tenant data is at and at what part of the process the assumption data was at I could probably do it with little issue. but you're going to have to give details of the mathematics that you're using and what that means is the functions and the data and the variables involved. Does that make sense to you?

I'm totally a data-driven person because everything in this world runs off of data everything is run by bits and bytes and machine language and that goes all the way to the unsuspecting consumers on the street clicking the buttons on the phones. I absolutely despise graphs and charts of any kind because they're just a professionally oriented representation of what actually goes through machines and pictures don't go through data does.

Originally Posted by vba_php

More than likely if you sent me something that had every detail laid out in terms of what part of the process the tenant data is at and at what part of the process the assumption data was at I could probably do it with little issue. but you're going to have to give details of the mathematics that you're using and what that means is the functions and the data and the variables involved. Does that make sense to you?

Yes. It will be simplified so there won't be a lot of numbers or categories beyond what is needed. I'm also going to highlight what is a user input/variable in a different color. The nice thing with excel vs. VBA is you can easily track the formulas and see if something is wrong. I'll try and explain the math as best I can.

Originally Posted by vba_php

I'm totally a data-driven person because everything in this world runs off of data everything is run by bits and bytes and machine language and that goes all the way to the unsuspecting consumers on the street clicking the buttons on the phones. I absolutely despise graphs and charts of any kind because they're just a professionally oriented representation of what actually goes through machines and pictures don't go through data does.

That's really cool! What an amazing mindset!

Originally Posted by max3732

Yes. It will be simplified so there won't be a lot of numbers or categories beyond what is needed. I'm also going to highlight what is a user input/variable in a different color. The nice thing with excel vs. VBA is you can easily track the formulas and see if something is wrong. I'll try and explain the math as best I can.

waiting....

Originally Posted by vba_php

waiting....

It might take me a little while. Whenever I've done one I've kind of winged it. I want to make sure the format is what I'd like and also easily explainable.

I know that waiting will be the hardest part.

I'm helping you for free. so don't get the impression that you will get *everything* that you want. if you want my deep level knowledge on any given concept, or anyone else here for that matter, you have to pay for that. the world isn't available for free.

but I'll do my best. I'm answering quite a bit today on here, as it seems. so I might not get back to you right away like I've been doing. and of course, I live life too.