Author Topic: Illiquid assets - Do you mark to market or retroactively update spreadsheets?  (Read 5282 times)

dragoncar

  • Walrus Stache
  • *******
  • Posts: 9922
  • Registered member
I happened to invest in a property with family many years ago and, for spreadsheet purposes, just left my equity constant.  I didn't really know how to value my share on a month-to-month or year-to-year basis.

Well now we are selling, and it will increase my NW substantially enough to make the charts look funny.  But it's not like I just had really good investment returns this month, it was a slower rise over many months.  Those who don't keep charts can probably just move on to the next topic and really, this doesn't matter too much in the grand scheme of things.

Anyways, I'm thinking I'll just adjust my NW numbers for previous months according to the CAGR of the investment (assuming an exponential growth from original equity to current equity). 

What do you think?

Middlesbrough

  • Bristles
  • ***
  • Posts: 268
I have a expenditure spreadsheet i update with paycheck, 401k, expenditur categories, and student loan debt. I update it everyday i can. My student loans updates everyday so i plug the daily interest in. When i miss days, i look at the total change and retroactively change the value linearly. If you have to have every square like me with no outliers, i would do the same. If you dont want to do the work, dont worry about it then.

Rezdent

  • Pencil Stache
  • ****
  • Posts: 814
  • Location: Central Texas
On my personal tracking I just book stuff on that month.
So there's some weird jumps up and down on my graphs.  But I can point to a month and go "oh yeah that's where blahblah".  My charts and graphs are like a scrapbook.
I like being able to do that.  I like pointing to a blip and telling my DH "that's the month we...".
Yeah I'm a nerd.
A lazy, sentimental one to boot.

Thedudeabides

  • Stubble
  • **
  • Posts: 242
CAGR makes sense if understanding historical changes is important to you.

I keep charts too and this is definitely the approach I would take.

brewer12345

  • Handlebar Stache
  • *****
  • Posts: 1381
I would not bother retroactively altering spreadsheets unless you are deeply into excel-based masturbation.  It is your private sheet, you know where the money came from, and the past no longer matters.  Who cares?

warfreak2

  • Handlebar Stache
  • *****
  • Posts: 1136
  • Location: UK
    • Music by me
unless you are deeply into excel-based masturbation
In which case, you are definitely on the right forum.

brewer12345

  • Handlebar Stache
  • *****
  • Posts: 1381
unless you are deeply into excel-based masturbation
In which case, you are definitely on the right forum.

Spreadsheets are a tool for me, not an object of a fetish.  YMMV.

rmendpara

  • Pencil Stache
  • ****
  • Posts: 610
I happened to invest in a property with family many years ago and, for spreadsheet purposes, just left my equity constant.  I didn't really know how to value my share on a month-to-month or year-to-year basis.

Well now we are selling, and it will increase my NW substantially enough to make the charts look funny.  But it's not like I just had really good investment returns this month, it was a slower rise over many months.  Those who don't keep charts can probably just move on to the next topic and really, this doesn't matter too much in the grand scheme of things.

Anyways, I'm thinking I'll just adjust my NW numbers for previous months according to the CAGR of the investment (assuming an exponential growth from original equity to current equity). 

What do you think?

Even this will make your past NW numbers incorrect. I'm guessing you use charts/graphs of some sort, and the big jump is screwing up the analytics?

I'd say just figure out a way to "normalize" the information. Maybe just remove it entirely? Why input numbers that you know are wrong?

Since I don't know what type of data you maintain, I can't give any more specific advice.

Good luck.

dragoncar

  • Walrus Stache
  • *******
  • Posts: 9922
  • Registered member
I happened to invest in a property with family many years ago and, for spreadsheet purposes, just left my equity constant.  I didn't really know how to value my share on a month-to-month or year-to-year basis.

Well now we are selling, and it will increase my NW substantially enough to make the charts look funny.  But it's not like I just had really good investment returns this month, it was a slower rise over many months.  Those who don't keep charts can probably just move on to the next topic and really, this doesn't matter too much in the grand scheme of things.

Anyways, I'm thinking I'll just adjust my NW numbers for previous months according to the CAGR of the investment (assuming an exponential growth from original equity to current equity). 

What do you think?

Even this will make your past NW numbers incorrect. I'm guessing you use charts/graphs of some sort, and the big jump is screwing up the analytics?

I'd say just figure out a way to "normalize" the information. Maybe just remove it entirely? Why input numbers that you know are wrong?

Since I don't know what type of data you maintain, I can't give any more specific advice.

Good luck.

Incorrect how?  No numbers are 100% accurate, but this is a lot closer fit to whatever gyrations occurred than a random spike.   Meaning I'd rather have

1 2 3 4 5 6 7 8 9

Than

1 1 1 1 1 1 1 1 9

Mr Mark

  • Handlebar Stache
  • *****
  • Posts: 1229
  • Location: Planet Earth
  • Achieved Financial Independence summer 2014. RE'18
But because you couldn't and didn't mark to market,  111111119 is exactly what happened.

it looks weird on your spreadsheet because it is -  you had a highly illiquid investment.

yes, it gives a huge % gain in 1 year, so your total portfolio cagr looks weird for that year,  but that is because it was weird.

Going back and fiddling the past in your spreadsheet is silly. If you want an average cagr, take a 3 or 5 year moving average. If you want to see trends in cagr in your normal portfolio, just exclude the illiquid one- offs from the analysis.



warfreak2

  • Handlebar Stache
  • *****
  • Posts: 1136
  • Location: UK
    • Music by me
But because you couldn't and didn't mark to market,  111111119 is exactly what happened.
Well, then pretty much the same thing happens when you buy and hold stocks*. They aren't money until you sell them. But everyone includes stocks on their balance sheet, and they mark them to market! The only real difference is that it's easier to measure their value accurately. If you have a way of estimating the historical price of the property, either using market data or a simple there compound growth model, there isn't any particular reason that you shouldn't. Professional accountants do it.

*And not really, in either case - you can still have gains/losses apart from when you buy and sell, they are called "unrealised gains/losses" or "appreciation/depreciation".

wtjbatman

  • Handlebar Stache
  • *****
  • Posts: 1301
  • Age: 40
  • Location: Missouri
If it's not too big of a hassle, I like the idea of just going back and retroactively updating your old spreadsheets.

TomTX

  • Walrus Stache
  • *******
  • Posts: 5345
  • Location: Texas
Well, I update our house value annually to whatever the tax assessment is. Good enough around here - they update annually and tend to follow the actual market (with some lag - but good enough for my purposes.)