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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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!
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.
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.
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.
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.