Real estate and Org table formulas

Daniel Gopar (he/him, IRC: gopar, gopardaniel@gmail.com)

In this talk, Daniel Gopar shares how he uses Org tables as spreadsheets to help him decide on real estate investments. Afterwards, he will handle questions via the Etherpad.

The following image shows where the talk is in the schedule for Sat 2022-12-03. Solid lines show talks with Q&A via BigBlueButton. Dashed lines show talks with Q&A via IRC or Etherpad.

Format: 25-min talk followed by pad Q&A (#emacsconf-gen)
Etherpad: https://pad.emacsconf.org/2022-realestate
Discuss on IRC: #emacsconf-gen
Status: Q&A finished, IRC and pad will be archived on this page

00:00.000 Introduction
00:20.480 The end goal
00:51.800 Constants and variables
02:11.520 ROI
02:40.040 Demo
03:32.240 Detecting input
04:02.240 Creating a table
04:26.400 C-c C-c
05:17.960 Our first formula
07:13.000 Basic arithmetic
08:11.720 Debugging
09:53.600 Flags
12:07.040 Recalculating
12:50.040 Multiple formulas
14:56.777 Formatting
16:36.202 Conditional prompts
18:12.203 Custom formulas
21:57.857 Automatically updating

Description

This will be a 20 minute presentation on how I use org mode tables (specifically formulas) on how I can quickly see if a deal is feasible or not.

Topics will include:

• Creating Table Formulas
• Special Buffer Editing Formulas
• Formatting output
• Special Debugging just for formulas
• Creating Custom Formulas
• Automatically re-calculating rows/table
• Finally Putting it all together to tell at a glance if a property is ideal or not

Essentially bits and pieces from this section of the manual: https://orgmode.org/manual/The-Spreadsheet.html

Discussion

Notes

• https://orgmode.org/manual/The-Spreadsheet.html
• Really liking the idea of the talk so far, but I would suggest zooming in on the text more next time
• Damn, org tables have so many more features than the ones I use already
• tables in org are highly underused. all the functionality of spreadsheet calculators with the power of elisp at your fingertips!
• this talk is awesome
• and org-mode has plenty of hidden power...
• and the power of Emacs Calc, to boot. Including handling units like kW, m, Pa, ...
• +1 for the awesomeness
• I've always been slightly apprehensive of going full spreadsheet with org tables but the features are very cool.
• I actually learned a lot from this talk. Thanks @gopar.
• great talk, thank you very much!
• excellent presentation, thanks!
• loved the presentation
• just downloaded a .webm of the talk. looks great on my screen (1280x720)
• Thanks for the Org mode Table tips
• was a fun, straight forward how to. very useful.
• I don't have any questions now, but I'm going to try to learn many things that you demonstrated!
• I hadn't seen the formula debugger before. Very useful information.
• org tables are so powerful
• moving around cols + rows - is also way faster than what one can do in GUI spreadsheet apps
• oh! great, thanks for sharing!

Questions and answers

• Q:  Pretty neat-- Can you say how this is better than maintaining a gui spreadsheet? 
  • A: As all things in life, this is preference. org tables are text only and for me this is easier than learning yet another tool (google spreadsheets/MS excel/etc) but totally up to you   Also, you can write your formulas in Elisp instead of whatever crippled language you have to use in other spreadsheets.+2 (move the logic to lisp to other package and decouple/reuse it other documents, test it, etc.); Moving around cols + rows - is also way faster than what one can do in GUI spreadsheet apps
• Q: Is there a way to share the constants between the tables?
  • A: You can create a constants for the entire buffer, or save them as variables
• Q: Can you please provide the link to the org file?
  • A: Currently the file is not hosted anywhere. I can put it in a github repo in a bit
• Q:Is there a way to link to a cell in another row in the same table? or other tables?
  • A:I think i remember reading that it is possible to do the first part of your question. I don't think you can do the 2nd part about linking cells from other tables. I'd recommend double checking w/ the org docs since I can be misremembering things
  • Update: Looks like it is possible -> https://orgmode.org/manual/References.html
• where is the org file for thase calculations?

Transcript

[00:00:00.000] Hey, I'm Gopar and this is the Real Estate and Org Mode Table Formulas talk. Not very creative, but it is what it is. Now I just want to say that everything I talk about here is in the Org Mode in the Emacs manual. I posted a link to the web version, but it should be inside of Emacs as well.

[00:00:20.480] Now before I start, I want to showcase the end goal. That way you know if you guys want to actually see the talk or not. So I always think that's pretty cool to see what you're actually going to build before you start building it. Alright. So let me start off with the goal, the end goal. Here we have a simple table formula and we have some constants, some values inside the list-- inside the table, I'm sorry, and some other stuff that we will get to, but for now... I don't want to spoil too much. I just want to give you a demo of how it actually works.

[00:00:51.800] We have a few values. Let me first go over the constants. We have PMI, which stands for private mortgage insurance, so it's insurance that you'll have to pay depending on how much money you put into the deal. The property tax, which is self-explanatory, the tax that you owe for owning the property, and then home insurance, and the interest rate at the loan that you get. So, here we have a few columns. The first one is called House, which I usually just put a description of the house with the link of the posting, the price of the house, the percentage down payment (this I play around with to see how much the deal will be structured), the down payment (which is calculated from the previous two columns), the monthly mortgage (which is calculated as well), and then the tenant income (which is what I suppose would be an example of the tenant income that I can potentially make off the property, the 1% rule and the ROI. I'll quickly go over the last two columns. So first is the 1% rule. The 1% rule is essentially a "rule," in quotes, that says that if a property matches this specific formula, it is a good deal to look into. So for example, the first two pass, and the last one does not pass. The last one, at a quick glance, we can just ignore it and say, "hey, that's not going to fly," we'll just ignore it. I won't go too much into details. That's just a brief summary.

[00:02:11.520] So now the ROI is the return on investment. So it says "how much of a return am I getting on the amount that I invested?" So let's say you put in $12,000, and at the end of the year, you cashflow $6,000. So if you calculate the ROI off of that, you get a 50% return on investment. In two years, you'll make your money back, which is pretty good. Then all the rest of the years, you'll just slowly be reaping in all that, all the excess cash flow. But yeah, that's it in a nutshell.

[00:02:40.040] So let me demo it real quick. So for example, I'm going to change the down payment, but I want you to pay attention to this column [down payment] and the monthly mortgage column. So right here [down payment] is $25,000, and here [monthly mortgage] is around $1,200, a little under $1,300. So what happens if I say, you know, what I'm going to change the down payment to 5% instead, because I just don't want to put 10. So let's just put say 5. Then I tab out of here, and voila - you see it updated to half of $25,000. So now it's $12,000, and this [monthly mortgage] went up over actually $1,300, and then this [1% rule] hasn't changed at all and the ROI is there. So one thing that I should mention is everything that I'm putting here is just example numbers, should not be taken into literal real estate advice. I just want to put that out there. These are just examples to show you how you can potentially make it on your own, do the formulas on your own.

[00:03:32.240] Alright, so another cool thing that I did was if there is no tenant income and I tab, it says "Enter Tenant Income". So if I don't put anything, it will automatically tell me, hey, I can't calculate without the tenant income. I can also do this right here [ROI field], say the same thing, Enter Tenant Income, but I just didn't put it for whatever reason, but after this video, you should be easily able to put it without much struggle. Alright, so if that's something you're interested in, then keep watching.

[00:04:02.240] So let's go over the basics first. So, how do we create a table? Well, we can do M-x org-table-create. If we run that it, will prompt us in a minibuffer. It says table size, columns times row. Usually it's rows times columns, but it is what it is. So let's just leave the default of 5 times 2, and voila, we get this. Beautiful. Awesome.

[00:04:26.400] So the other way is using the magical C-c C-c in Org Mode, which basically is context-aware and does anything, does the right thing like, almost 100% of the time, which is pretty amazing. Alright, let's just say I write a pipe, some words, and then another pipe, Gopar, and then another pipe. Let's just say we're trying to write it out via text, because in Org mode everything has text. There's nothing fancy about it. If I do C-c C-c, Org mode should automatically be context-aware that this area is a table. So, C-c C-c, boom. So if I press enter, another column. If I press tab, it should automatically move me. So yeah, so that's pretty much it. That's how you get started into the column. So, I'm assuming most of the people here already know that. This is just the primary basic review.

[00:05:17.960] So, let's first go... Let's go dive right into our first formula. So I copied some values over here, just to save time, and the columns. So, let's go ahead and say that we have single family house, and the price is a hundred thousand. And, Let's say that I want the price, the down payment that I want to put is 10%. Right. Alright. 10%. Now if I tab or go to the next column, nothing happens. Why is that? Well, it's because (you probably guessed it) we haven't written or tied any table formulas. So we're saying, alright, enough talk. How do we do that? Well, the answer is very simple. We do a pound sign (#), if I can find it. #+ and then we do TBL for table and then FM for formula. So, table formula, and the column. So this, you're already halfway to writing your first table formula. So let's say we want to automatically, Let's just, for exercise, we want to put the down payment, just put some type of value in there, just to make sure that it's working. So the way Org Mode refers to columns is, we start with the dollar sign ($) and then we put the number that the column is. Indexes start with one, not a zero. As most of us watching are programmers, we're probably used to starting with zero, but it starts with one. So one, two, three, four, five. So down payment is the fifth column, we say five, and then we say equal to, let's say Gopar. Then we do C-c C-c to evaluate it, and the table is automatically updated. Look at that. So when you do just this, dollar sign ($) 5, it updates every single column. There is a way to specify that this cell only and this cell only but this is out of scope and it's not that hard it's just not in this video. I would recommend, I commend you, or, actually I recommend that you go check out the manual for that.

[00:07:13.000] All right so, let's say we want to do some basic arithmetic we want to do some list values instead of just putting in text. So how do we do that? Well, we have to pull the expression that we want to put in. So, for example, if we want to add we'll do 20 plus 20 and if we do C-c C-C to evaluate it, it should update every single column, the entire column, the fifth column I mean and, tada, it does. Cool! So now let's say we want to do a little bit more advanced. Let's say we want to add the previous column to this column, so how do we refer to this one? Well, 1-2-3-4 is the fourth column, so we would just simply do $4 and this should automatically be referring to this column (% DP). So we'll do 10 + 20, it's going to be 30 over here, and let's do C-C C-c. Ooh, error, what happened? Oh my god, oh my god. Well, this seems scary but no worries.

[00:08:11.720] This is where debugging comes in pretty handy, which is actually our next section as you can see. So, what happens if we do, if we go into the debugging section, what is the first step? Well, the first step is to try out, is to enable formula debugger. So, if you do C-c {, it will turn on a minor mode that whenever you evaluate a table formula, the debugger will be enabled, will automatically kick in. And if you want to disable the debugger, you just run the command again, and it will turn off. So, let's go ahead and run it. C-c {. As you can see in the mini buffer, it says, "formula debugging has been turned on". Awesome! So if we go back to our table and we try to run this, and see what's going on, we see... Oh, first off, before we look at the buffer that just opened, look at the mini buffer, it says "Debugging Formula. Continue to next?" So if you have multiple or a series of formulas, it will say, "hey, do you want to debug this one or the next one?" So this is just saying, "hey, do you want to go into the next formula?" And since there's no next formula, debugger will just exit out and leave you with the other buffer to see. For now, we'll just click no. Right now, it doesn't matter if you click yes or no because there's only one formula, but we'll just click no, and let's go ahead and pay attention to the new buffer. Well, over here it might seem a little confusing, but don't worry, we're just going to ignore most of this. The first thing that we're going to pay attention is to the original. So it says, okay, this is the original, so we have a quote expression, which is just trying to add the fourth column. And if we go over here once everything is finalized, it says "hey, we're trying to add this 10, but it's actually a string 10, and added to 20. So of course it's going to be an error, so now we know what the error is.

[00:09:54.099] So you're saying all right cool, awesome, now how do we transform that string into a number? Well, Org Mode formulas have these flags that you can use, and essentially a flag looks like this. It's a semicolon (;) followed by some letter or some identifier that will let Org mode know that hey, this should be turned into a number or this should be turned into whatever. There's different ones for alpha literal and for a bunch of... I think there's even "i" for "integer", so it depends what you want. So for now we're just going to put "number" because it's a real number. If we do this and the debugger is still on, (remember because it automatically stays on until we turn it off), if we reevaluate the the formula, we should be able to see it. But first, before I do that, let's check step two. I'll now rerun formulas with C-c * and table, which calls org-table-recalculate. To do this, you actually have to be inside the table. Otherwise, Org mode will try to do some other stuff because it is context-aware, so depending on the context it might do something else. So if we do C-c ... As you can see the debugger has kicked in, says, "Do you want to continue to next?" Let's press yes (y), and it has been applied. So as you can see, it only updated one column-- I'm sorry, one row--and the thing is, when you run this, the recalculate, it will only run for the current row that you're in. If you want to run for the entire table, you're going to do C-u C-c . Before I do that, let me turn off the debugger since we no longer are in need of it. So C-c {, and debugging has been turned off. Now let me do C-u C-c * and as you can see the other rows also calculated, updated as well. Beautiful! So as I mentioned, feel free to look / browse the documentation for more flags because each flag has its own special meaning and will do different things, which is pretty cool.

[00:12:07.140] All right, cool. We're done with debugging and we fixed it. So there, now we know how to create formulas and how to debug them whenever they break, which is awesome. All right, but remember how I said that you can only debug... Whenever you run recalculate, It will only run the first formula? Well, let's say you want to have multiple formulas? This is completely valid, except the bad thing is that you have to do C-c C-c C-c on each each one, because C-u C-c * won't recalculate everything. It won't. Sadly, it doesn't do it. There is a way that you can do it, which is hacking together some elisp. You can probably find it or you can probably make it yourself if you look around, but that's out of scope for this.

[00:12:51.207] So now, how do we... We can write all the formulas we want in one single line. There's a way to to distinguish when one ends and one begins and that is the double colon (::). So right there, and a new formula will begin. So for example, let's say for the seventh column we say "gopar". If I do C-c C-c, it'll run every single thing so that... "gopar". Tada! There, awesome. But this is going to get very annoying if you're simply trying to edit formulas like this, right? So that's where the nicer debugging, nicer editing section comes in. So, yes, just like as mentioned, table calls only the first formula. So what's the step onto this nicer editing section? Try out "C-c ," or org-edit-special. So let's go back to the table formula and call it... oh my god, look at that, a new buffer just for editing, and each formula is in its own line to make it easier, which is beautiful! So, let's just say I want to do another calculation. Let's do eight times eight, which should be 64, and we have no need of putting this flag because the flag only affects it on the input coming in. I should have mentioned that earlier. Only input coming in. There is ways to affect the output, which we'll also cover in this topic later on, but for now, you can either leave the end flag or leave it out. It will still work fine. Let's just leave it out for now. Let's just do C-c C-c to make sure that everything is working. 64. Beautiful. So there you have it. You can have multiple formulas just stacked up into one line, and whenever you need to edit it, just go into that into that line and "C-c ,", and tada! You have this ready, good to go, and for editing. Oh and if you want to exit out, also just do "C-c ," again and you're back. I did not mention that. Sweet! So now we know how to have a better editing experience. Sweet!

[00:14:57.106] So now comes the formatting section which I talked about. So what's the first step? Well for formatting, Org mode uses the printf function from C. So those who are familiar with C, you'll feel right at home because the way you format it is exactly the same way. So for example, this will print off a floating number with two decimal points. As you can see here, this is how you will use it. It will be after the semicolon and it will be "%.2f". So let's go ahead and test that out. Actually, let's go ahead to our latest function-- I mean, to our latest formula. Let's go ahead to the nicer buffer and let's do, Ctrl... Let's enter the semicolon, and then let's put the percent sign, we do 2f. Actually, let's make it five just to differentiate from the formula, and let's see. Let's run it, and tada! So yeah, as you can see, five decimal points. and if we want zero decimal points, we can also just move point zero, and tada! Just like that. We can also just leave it as is how it was before, because before, we didn't even actually need it, but yeah, just an example. And just a reminder, there are plenty more ways of formatting, just look at the documentation. There will be more. Basically every single thing that you need, just use the documentation as reference. Cool! So now we know how to debug, how to write formulas and how to get a better, nicer editing buffer for the formula so we don't have to do it all in a single line.

[00:16:36.340] So, what about conditional prompts, like I was showing in the first table in the end goal? Well that's actually pretty simple because we already know how to do this. Yes, if you think about it for a second, if we are able to pull lisp s-expressions, then we are able to basically do it already. Here's an example. We're saying if the second column is empty, is zero-- so actually, this should be with the N flag because we will transform empty values as zero, and that's how Org mode will read those. It's saying if the second flag is zero, then I want you to put "Enter the values." And if it's not empty, we're going to put "Values entered." We're going to recognize. So let's go ahead and actually do this. So let's grab this, and let's type it in. So let's go ahead. "Enter the values," because it doesn't have any. So let's go ahead and say "Hello EmacsConf" and let's go ahead and run it again. Since there are values, it's going to overwrite what's here and put value entered. So let's go ahead. Tada, so there you go. So that is pretty much how you do conditional props, which is pretty straightforward once you think about it because if you are able to insert Lisp expressions, then you are able to just do that check and do the conditional check yourself. So, custom formulas.

[00:18:13.640] Yeah, you'll see what I mean. We want to be able to put our own custom functions, and you probably have an idea how to do this already. Yes, we also know how to do this already. Before I continue, I'm going to say that I already have some formulas that I use which are not part of this talk because they're just functions that calculate some stuff. So for example, they calculate the monthly mortgage, the monthly PMI, property tax, homeowners insurance, so a bunch of other stuff. I have these functions already and they are a bit off, but for this example, they're doing everything. It's close enough, so don't worry too much. Just an example, you can have your own function that does something else like calculate a REI B rental or something like that. So you can do whatever you want. As long as you can call via Lisp, you can call it in Org mode, in the table formulas. So let's exit out of there. Now that we know how to do everything, let me go back to the original table and go from there. Let me close all these out actually. Now let's go back and revisit this table, since we'll be much, much more familiar except for one thing, which I will explain. So we have the constants right there and we have the house prices and we have everything just like I mentioned before. The main part that we want to look at is the table formula. So let's open up our special editing buffer, "C-c ,", and as you can see, I have some right here. So, the fifth column I'm saying, "Hey, we're gonna call this function rei-calculate-down-payment, and I'm gonna pass in the third column and fourth column and I'm going to pass in t." (true) I believe this is just to normalize, to convert to the proper decimal place, so let's not worry too much about that. Then for the sixth column, we're going to say "I calculate the monthly mortgage." We're going to pass in the third column the INTEREST_RATE, which we have defined over here in the constants. The 30, I believe this is for 30 years, PMI, the PROPERTY_TAX and HOME_INSURANCE, and then the fourth column. Then everything is going to be accepted as numbers, and we're going to have two decimals at the end of the place. So, what else? Over here is where we have our conditionals. We're saying, "hey, if the seventh column is 0, press 'Enter Tenant Income'." Over here as well, if the seventh column is zero, we're going to press 'Enter Tenant Income' and for this one we're going to say, "Hey, we're going to normalize the price." I believe this is a 1% rule. This could have been extracted into a function, but I just did the calculation right here. This, I believe, is the ROI, 12 months. This is just calculating the cash flow. This is a very rudimentary function or formula. Do not use this because there is way more to go into calculating the cash flow and also, it differs from person to person. Some people are more conservative, other people are way more liberal, so it just depends how you want to calculate it. As you can see, we have the N flag for numeric number and then we're saying we're formatting to one decimal place. The %% sign is just to input a percent sign as itself. Otherwise, Org mode is going to think it's some type of a formatter, which it's not. If you do... As I mentioned, if you've used the printf function in C or C++, then you probably know how to use it. Okay, so this is pretty much everything in a nutshell.

[00:22:01.924] So, one thing that I do want to say is the last section, which is automatically updating, which is the part that blew my mind when I realized that Org Mode can do this. So how do we do it? Well, you probably guessed just from looking at at the first table that we have right now. We add a column at the beginning of the table with a percent (%)-- I'm sorry, with the pound sign (#) or hashtag as the younger kids call it. So this is what we do. We added a column at the beginning of the table, we do pound sign (#). So this is what it's for. This lets Org mode know that "hey, I want the values, the table formulas, to automatically run on each tab change." Now I believe that you can make it so that it changes, so that it updates on every keystroke. I think that's too much. And then you can also make it so that only certain rows update or certain columns. There's a plethora of things that you can do. You should definitely read up on the documentation, because you will probably make a way better talk, a way more advanced talk than I on this one, so I'm looking forward for that. But this is essentially how you build a Org mode table formula that will help you know if a property is correct. So, let's go ahead and as a final out to this demo, let's go ahead and enter a new column. Org-mode automatically puts the pound sign (#), which is awesome, and let's just say 'Emacs House', and let's say it is 100k. So 100k, and as you can see it's already trying to calculate the monthly mortgage, but we'll see about that, and let's just put 10% down. So 10% of 100k should be 10,000. So 10,000, correct, and the monthly mortgage is that and let's just say the tenant income is, as you can see right here, tenant income is, let's say 1500. Is it passing the 1% rule? Yep! What's my ROI? 118%, which is kind of cool actually. That's a nice deal. So this is a rudimentary way of calculating deals in Org mode. I hope you found it interesting, and yeah, that is it.

Captioner: tom

Questions or comments? Please e-mail gopardaniel@gmail.com

CategoryOrgMode