Tracking mortgage/house equity

[yegle]

Hi all,

I'm trying to figure out how to track mortgage/house equity with beancount. So here's my understanding of the problem, let's assume a 100k house with 20% down payment:

Before purchase, I may receive some gift fund from parents:

Income:Parents -10,000 USD

Assets:Bank:Checking 10,000 USD

During closing, I'll need to pay seller with down payment and mortgage amount:

Assets:Bank:Checking -20,000 USD

Liability:Mortgage -80,000 USD

Assets:House 100,000 USD

For each mortgage payment, the principle goes to liability account and interest goes to expenses:

Assets:Bank:Checking -1000 USD

Liability:Mortgage 800 USD

Expenses:Mortgage:Interest 200 USD

For selling a house, the excessive value will go to income:

Assets:House -100,000 USD

Assets:Bank:Checking 150,000 USD

Income:House 50,000 USD

So here's my question:

1. am I right on the accounting above?

2. how can I track the equity that I have on the house, considering maybe I'll need a HELOC loan?

--

Yuchen Ying
http://about.me/yegle

[Daniël Bos]

That's how I do it. Except that I increase the asset value against "unrealised gains" a couple of times a year. When I sell, I move "unrealised gains" to "realized gains". This way, the asset book value always reflects the actual value.

A HELOC is just another loan, create a new liability for it and book the amount from there to a new asset. It has little relation with your actual home equity. Sure, if you default on it, they'll take your home to recover the debt. But they'll do that for any other personal loans as well!

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAFL5w3Xqe4nqx1BgAkcz76Cti6BJtMgJPyU8pj5V6N-kdC5v7w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Martin Blais]

On Fri, Mar 18, 2016 at 6:29 PM, yegle <cny...@gmail.com> wrote:

Hi all,

I'm trying to figure out how to track mortgage/house equity with beancount. So here's my understanding of the problem, let's assume a 100k house with 20% down payment:

In Kansas?

Okay, hypothetical, otherwise you'd be missing a zero for SF ;-)

Before purchase, I may receive some gift fund from parents:

Income:Parents -10,000 USD

Assets:Bank:Checking 10,000 USD

That works.

(Personally I'd have named the account as Income:Gift and made "Parents" the payee.)

During closing, I'll need to pay seller with down payment and mortgage amount:

Assets:Bank:Checking -20,000 USD 

Liability:Mortgage -80,000 USD

Assets:House 100,000 USD

Almost: For the last leg you're better off creating a new currency, e.g., say "SHOEBOX" (to reflect your hypothetical example).

  Assets:House   1 SHOEBOX {100,000 USD}

Later on you when you request an evaluation from a real-estate agent you can use a price directive to reassess the value of your property:

  2016-08-01 price SHOEBOX    108,000 USD

The reality of your acquisition will likely go through some extra steps, e.g., putting down a deposit to escrow, and perhaps sending the rest of the downpayment to the notary's escrow before it being transferred legally to the seller. Mine looked something like this (using your numbers):

2010-02-12 * "Bank draft for deposit on 123 Shoebox Street, Kansas."

  Assets:Bank:Checking                      -2,006.50 USD

  Expenses:Financial:Fees                        6.50 USD

  Expenses:Shoebox:Acquisition:Escrow        2,000.00 USD

2010-02-22 * "Draft for balance of downpayment on Shoebox to escrow account."

  Assets:Bank:Checking                     -18,857.70 USD

  Expenses:Shoebox:Acquisition:Escrow       18,000.00 USD

  Expenses:Shoebox:Condo-Fees:Operating         36.58 USD ;; 5 days @ 7.32/day                  

  Expenses:Shoebox:Taxes:Municipal             671.19 USD

  Expenses:Shoebox:Taxes:School                149.93 USD

2010-02-23 * "Purchase of Shoebox realized."

  Assets:Shoebox:Property                           1 SHOEBOX {100,000.00 USD}

  Expenses:Shoebox:Acquisition:Escrow      -20,000.00 USD

  Liabilities:Bank:Mortgage:Loan           -80,000.00 USD

2010-02-24 balance  Expenses:Shoebox:Acquisition:Escrow  0.00 USD

For each mortgage payment, the principle

"principle" -> "principal"

 

goes to liability account and interest goes to expenses:

Assets:Bank:Checking -1000 USD

Liability:Mortgage 800 USD

Expenses:Mortgage:Interest 200 USD

Yep, that's right.

Or more likely, you'll see amounts like this:

2010-04-28 * "MORTGAGE PAYMENT"

  Assets:Bank:Checking                        -464.46 USD

  Liabilities:Bank:Mortgage:Loan               171.01 USD

  Expenses:Shoebox:Loan-Interest

And each of the payments will vary with the amortization schedule.

This _can_ be a bit annoying... your bank, depending on how bad it is, _may_ or may not share with you the breakdown of principal vs. interest on each payment. If you have a fixed rate, I suppose you could precalculate it (or ask them to give you the schedule ahead of time) but if you have a variable rate, it may change mid-year, and it may change if you make some of the optional prepayments. The bottom line is, you'll want to check that fraction on each of the payments you make if you want accurate numbers. Anyhow, in my case, my bank did _not_ report it, except that the last payment's interest portion was visible _only_ on their website for two weeks, so I would have the discipline to go fetch it myself... and of course, miss out a few here and there and end up once/year annoyed at a desk at the bank bothering some guy in a suit to call the backoffice to print me the last year's worth of exact payment breakdowns so I could put them in my Beancount file :-)

For selling a house, the excessive value will go to income:

Assets:House -100,000 USD

Assets:Bank:Checking 150,000 USD

Income:House 50,000 USD

That's right. 

And again... more realistically, something like this, with fees, a cut to the selling agent, and the receipt of your money by a legal person in a couple of payments afterwards:

2012-08-01 * "Sale of Shoebox"

  Liabilities:Bank:Mortgage:Credit-Line       10,010.00 USD  ; Credit loan account to cover                   

  Expenses:Shoebox:Loan-Interest                 101.94 USD  ; Accrued interest (see loan statement)          

  Expenses:Shoebox:Loan-Interest                  15.31 USD  ; Three more days of interest at 5.10/day        

  Expenses:Shoebox:Acquisition:Legal             596.40 USD  ; Discharge (incl tax)                           

  Expenses:Shoebox:Acquisition:SaleAgent       9,200.00 USD  ; Agent fees (incl tax)                          

  Assets:Shoebox:Condo-Assoc                     211.30 USD  ; Condo association account to zero              

  Assets:AccountsReceivable                   50,000.00 USD  ; First check to be received from notary         

  Assets:AccountsReceivable                   56,865.05 USD  ; Second check to be received later              

  Assets:Shoebox:Property                            -1 SHOEBOX {100,000 USD} @ 127,000 USD

  Income:Shoebox:PnL                            -27,000 USD  ; Capital gains on the property                  

2012-08-02 close Assets:Shoebox:Property

2012-09-12 * "BR TO BR" | "Notary bank draft deposit"

  Assets:Bank:Checking                        50,000.00 USD

  Assets:AccountsReceivable

2012-09-23 * "BR TO BR" | "Second check from condo proceeds from notary"

  Assets:Bank:Checking                        56,865.05 USD

  Assets:AccountsReceivable

2012-09-24 balance Assets:AccountsReceivable       0.00 USD

 

So here's my question:

1. am I right on the accounting above?

2. how can I track the equity that I have on the house, considering maybe I'll need a HELOC loan?

I don't know what a HELOC loan is.

Your equity is simply the value of the house minus your loan.

Finally, if you book all the expenses under a common root (as in the above, e.g., Expenses:Shoebox:*) you can then compute the cashflows of the property as an investment value, including all the little crumbs. I've been meaning to build an example text using data from my first home to show how to do this, and how to do the above as well.

I hope this helps, feel free to ask more questions,

(Full data from above in attachment.)

 

--

Yuchen Ying
http://about.me/yegle

--

[yegle]

On Fri, Mar 18, 2016 at 5:40 PM, Daniël Bos <cor...@gmail.com> wrote:

That's how I do it. Except that I increase the asset value against "unrealised gains" a couple of times a year. When I sell, I move "unrealised gains" to "realized gains". This way, the asset book value always reflects the actual value.

A HELOC is just another loan, create a new liability for it and book the amount from there to a new asset. It has little relation with your actual home equity. Sure, if you default on it, they'll take your home to recover the debt. But they'll do that for any other personal loans as well!

Hmm I always assumed HELOC loan amount will be based on the equity you have on the property. Guess I'm wrong then.

 

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CADPdpKYkK49mtxeb32WmBpWLYjvBzDdA8ZOk%2B%2Bk0YKpd%3DB%2BGbg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

[yegle]

Thanks Martin, reply inline.

On Fri, Mar 18, 2016 at 5:54 PM, Martin Blais <bl...@furius.ca> wrote:

On Fri, Mar 18, 2016 at 6:29 PM, yegle <cny...@gmail.com> wrote:

Hi all,

I'm trying to figure out how to track mortgage/house equity with beancount. So here's my understanding of the problem, let's assume a 100k house with 20% down payment:

In Kansas?

Okay, hypothetical, otherwise you'd be missing a zero for SF ;-)

Before purchase, I may receive some gift fund from parents:

Income:Parents -10,000 USD

Assets:Bank:Checking 10,000 USD

That works.

(Personally I'd have named the account as Income:Gift and made "Parents" the payee.)

During closing, I'll need to pay seller with down payment and mortgage amount:

Assets:Bank:Checking -20,000 USD 

Liability:Mortgage -80,000 USD

Assets:House 100,000 USD

Almost: For the last leg you're better off creating a new currency, e.g., say "SHOEBOX" (to reflect your hypothetical example).

  Assets:House   1 SHOEBOX {100,000 USD}

Later on you when you request an evaluation from a real-estate agent you can use a price directive to reassess the value of your property:

  2016-08-01 price SHOEBOX    108,000 USD

Ah great idea.

 

The reality of your acquisition will likely go through some extra steps, e.g., putting down a deposit to escrow, and perhaps sending the rest of the downpayment to the notary's escrow before it being transferred legally to the seller. Mine looked something like this (using your numbers):

2010-02-12 * "Bank draft for deposit on 123 Shoebox Street, Kansas."

  Assets:Bank:Checking                      -2,006.50 USD

  Expenses:Financial:Fees                        6.50 USD

  Expenses:Shoebox:Acquisition:Escrow        2,000.00 USD

2010-02-22 * "Draft for balance of downpayment on Shoebox to escrow account."

  Assets:Bank:Checking                     -18,857.70 USD

  Expenses:Shoebox:Acquisition:Escrow       18,000.00 USD

  Expenses:Shoebox:Condo-Fees:Operating         36.58 USD ;; 5 days @ 7.32/day                  

  Expenses:Shoebox:Taxes:Municipal             671.19 USD

  Expenses:Shoebox:Taxes:School                149.93 USD

2010-02-23 * "Purchase of Shoebox realized."

  Assets:Shoebox:Property                           1 SHOEBOX {100,000.00 USD}

  Expenses:Shoebox:Acquisition:Escrow      -20,000.00 USD

  Liabilities:Bank:Mortgage:Loan           -80,000.00 USD

2010-02-24 balance  Expenses:Shoebox:Acquisition:Escrow  0.00 USD

I think the escrow account should be considered an asset account, since if the purchase does not close due to contingency, the deposit in escrow account will be returned to your banking account, maybe minus some fees.

HELOC is short for Home Equity Line of Credit, I assumed (based on the name) that the amount I can borrow from HELOC is related to the equity I have in the property, thus I was trying to track the equity for my own reference.

 

Finally, if you book all the expenses under a common root (as in the above, e.g., Expenses:Shoebox:*) you can then compute the cashflows of the property as an investment value, including all the little crumbs. I've been meaning to build an example text using data from my first home to show how to do this, and how to do the above as well.

I hope this helps, feel free to ask more questions,

I think the whole mortgage case can be written down in a document and listed with the other Cookbook & Examples document.

 

(Full data from above in attachment.)

 

--

Yuchen Ying
http://about.me/yegle

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAFL5w3Xqe4nqx1BgAkcz76Cti6BJtMgJPyU8pj5V6N-kdC5v7w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhPGh65XAYPuMgrQHiDBTwEaT1e1C9yfaUDGhxLUpHYMEA%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

[Martin Blais]

On Fri, Mar 18, 2016 at 11:38 PM, yegle <cny...@gmail.com> wrote:

 

The reality of your acquisition will likely go through some extra steps, e.g., putting down a deposit to escrow, and perhaps sending the rest of the downpayment to the notary's escrow before it being transferred legally to the seller. Mine looked something like this (using your numbers):

2010-02-12 * "Bank draft for deposit on 123 Shoebox Street, Kansas."

  Assets:Bank:Checking                      -2,006.50 USD

  Expenses:Financial:Fees                        6.50 USD

  Expenses:Shoebox:Acquisition:Escrow        2,000.00 USD

2010-02-22 * "Draft for balance of downpayment on Shoebox to escrow account."

  Assets:Bank:Checking                     -18,857.70 USD

  Expenses:Shoebox:Acquisition:Escrow       18,000.00 USD

  Expenses:Shoebox:Condo-Fees:Operating         36.58 USD ;; 5 days @ 7.32/day                  

  Expenses:Shoebox:Taxes:Municipal             671.19 USD

  Expenses:Shoebox:Taxes:School                149.93 USD

2010-02-23 * "Purchase of Shoebox realized."

  Assets:Shoebox:Property                           1 SHOEBOX {100,000.00 USD}

  Expenses:Shoebox:Acquisition:Escrow      -20,000.00 USD

  Liabilities:Bank:Mortgage:Loan           -80,000.00 USD

2010-02-24 balance  Expenses:Shoebox:Acquisition:Escrow  0.00 USD

I think the escrow account should be considered an asset account, since if the purchase does not close due to contingency, the deposit in escrow account will be returned to your banking account, maybe minus some fees.

Ah yes, look at that. You're totally right.

I did that a long while ago, while I was still learning. Haven't even noticed now.

I'll fix it.

Thanks for reporting it.

2. how can I track the equity that I have on the house, considering maybe I'll need a HELOC loan?

I don't know what a HELOC loan is.

Your equity is simply the value of the house minus your loan.

HELOC is short for Home Equity Line of Credit, I assumed (based on the name) that the amount I can borrow from HELOC is related to the equity I have in the property, thus I was trying to track the equity for my own reference.

Ah, it's just a collateralized line of credit. I used to have one of those.

It's related, but the bank will set their own maximum amount on it.

It's not a dynamic thing.

Finally, if you book all the expenses under a common root (as in the above, e.g., Expenses:Shoebox:*) you can then compute the cashflows of the property as an investment value, including all the little crumbs. I've been meaning to build an example text using data from my first home to show how to do this, and how to do the above as well.

I hope this helps, feel free to ask more questions,

I think the whole mortgage case can be written down in a document and listed with the other Cookbook & Examples document.

Yes. I was planning to also include a calculation of the property as an investment and to demonstrate that what looks like a great deal really isn't, when you count all the bits and pieces, and Beancount allows you to do that.

[Zhuoyun Wei]

On Sat, Mar 19, 2016 at 8:54 AM, Martin Blais <bl...@furius.ca> wrote:
> That works.
> (Personally I'd have named the account as Income:Gift and made "Parents" the
> payee.)

A little OT here. But "payee" should refer to the party who receives
the money? When writing my own beancount file, I fill in "payee" field
only for expense transactions. I leave "payee" field blank for income
transactions and use "narration" field instead for "payer" information
if needed.

Correct me if I misunderstand the meaning of "payee" and "payer", as
English is not my native language...

--
Zhuoyun Wei

[Martin Blais]

No, you're right, we're abusing the specific meaning of it.

It's just an extra field I use to refer to the counterparty.

"Counterparty" would be a better name for it.

[man...@gmail.com]

Hi,

On Friday, March 18, 2016 at 8:54:37 PM UTC-4, Martin Blais wrote:

Finally, if you book all the expenses under a common root (as in the above, e.g., Expenses:Shoebox:*) you can then compute the cashflows of the property as an investment value, including all the little crumbs. I've been meaning to build an example text using data from my first home to show how to do this, and how to do the above as well.

I hope this helps, feel free to ask more questions,

This is a long-delayed reply to this thread, but I've just found beancount and am curious about a use-case.

You suggest keeping all expenses relating to a property under a common root. If you have n properties, there would be Expense:<N>:Maintenance for each property. What are the pro/cons to this, versus having just Expense:Properties:Maintenance and then #tagging each transaction with #propertyN?

Thanks,

  -k.

[Martin Blais]

Just the ability to select them all.

The right way to think about selection is that you have a single table filled with postings.

Attributes of transaction are replicated on their postings.

So how are you going to segment them out to aggregate in various ways?

- You can use a subset of the account name

- Tags are per transaction only; you can use a combination of accounts and tags

I prefer using a part of the account name.

That's all.

--

You received this message because you are subscribed to the Google Groups "Beancount" group.

To unsubscribe from this group and stop receiving emails from it, send an email to beancount+unsubscribe@googlegroups.com.

To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/3a97ce6b-224b-4b92-a02c-1e00b5288550%40googlegroups.com.