Assets - a worked example (feedback, please)

[Simon1D]

I've used bank accounts, credit card accounts and term accounts, transactions and payees in mmex for many years now, previously on Windows and now on linux (mmex v1.7.0) and am very happy with it - huge thanks from me to all the developers!
Occasionally I would try and use assets and asset accounts but always got confused and gave up.
This time I am determined to succeed, and wish there was a worked example (or even a tutorial) to help. I searched but failed to find anything other than a few asset-related exchanges here on the forum. Building on those exchanges, I offer the following. I hope I'm on the right lines (the details are made up, of course).

Assets - a worked example
I'd like to have mmex represent the acquisition and subsequent disposal of a property asset (my house).

In the "real" world - the purchase
I bought the property for £100,000 on 21 June 2000. I was a "cash buyer" - lucky me. The legal etc. fees for the purchase were £1000. On 19 June the balance in my bank account was more than £102k, and on 20 June I transferred £101k to a conveyancing solicitor, who completed the purchase and registered the property in my name on 21 June. My net worth changed:

• from £102k on 19 June (all in the bank account)
• to £1k on 20 June (in the bank account)
• to a nominal £101k on 21 June (consisting of £1k in the bank account plus the property, assigned a market value of £100k)

In the "real" world - the sale
I sold the property for £120,000 on 21 June 2010. The proceeds eventually went towards the purchase of another property but, in the first instance, they arrived in my bank account (after a deduction of £2k in legal fees). During the time that elapsed between the dates of purchase and sale:

• the property value increased by £20k

Whatever my net worth was during this period, immediately before the sale it can reasonably be said to include an asset whose value is £120k, because immediately after the sale, the asset value (to me) was reduced to zero and in its place I had £120k in the bank, albeit offset by costs of £2k in fees incurred to complete the sale.

In mmex - the purchase
I have one bank account, opened before 2000-06-19, whose reconciled balance on 2000-06-19 is more than £101k. I can afford the property purchase.
I create an asset "property-1" initial value £0 and an associated asset account also named "property-1" on 2000-06-19, with an initial balance £0, i.e. before any property-related transactions.
I create a (non-asset) transaction on 2000-06-20 which transfers £100k from the bank account to the asset account, and a withdrawal of £1k from the bank account, with payee "solicitor", on the same date.
I create a withrawal/transfer asset transaction on 2000-06-21 for £100k, with payee "vendor".
My net worth, on the mmex dashboard, is correctly shown throughout the stages of the property purchase: in the beginning there is no asset. Then there is an asset whose value is zero (this makes sense because I don't own it - the value to me is zero). Then my net worth plummets when I pay all that money to the solicitor. Finally, my net worth almost recovers, i.e. apart from legal fees, when I become the property owner the following day.

In mmex - the sale
In the existing asset account, I create a deposit (non-asset) transaction on 2010-06-18 for £20k, with payee "the market".
I create a withrawal/transfer asset transaction on 2010-06-19 for £20k with payee "valuation".
In the asset account, I create a deposit/transfer asset transaction on 2010-06-20 for £120k, with payee "purchaser".
I create one (non-asset) withdrawal from the asset account on 2010-06-21 of £2k, with payee "solicitor" and a non-asset transfer of £118k from the asset account to my bank account on the same date.
Again, my net worth on the mmex dashboard is correctly shown throughout the stages of the property sale: the asset value increases by £20k (I already own it, but this increase in its value is only confirmed when the sale contract is agreed with the purchaser) and then my net worth changes from consisting of a property asset plus a bank balance to just a bank balance. As in reality, the net worth only ever goes down by the amount of the legal etc fees, because the ownership doesn't change until the purchaser pays up.

Comments
In working out this example, I discovered by trial and error what has to happen in mmex for an asset value to change, and came to kind of understand why mmex handles assets the way it does, and also who the various payees should be in all the transactions, in particular the ones that I invented: "the market" and "valuation".
I assume that the asset account balance should be zero most of the time and only needs to be non-zero when any one of the purchase, the sale, or a valuation is in progress. It is the asset that appears on the dashboard as part of the net worth, not the asset account. Also, an asset can exist even if I don't own it but in that case its value should be zero, because value means value to me.
Again, huge thanks to the developers and maintainers of mmex. If any would care to put me straight in this example, I'd be delighted.

PS I could upload a database with only these things in it, but it's instructive to work through and observe how the net worth changes as each transaction is added.

[nstein]

This is a good write up, it seems like you've got the basics down. I recently did something similar in response to a question here https://github.com/moneymanagerex/money ... ssues/6167 which involved funds coming from multiple sources on multiple different dates (earnest money paid a month before closing, down payment coming from a bank account, remainder coming from a mortgage). Perhaps it will spark some questions or better yet suggestions for improvements in the handling of assets.

By the way, the stocks/shares concept works almost exactly the same way with stocks = assets and share account = asset account, so you're well on your way to being an MMEX master

[guanlisheng]

hi @Simon1D ,

i am wondering whether it can be reposted to https://moneymanagerex.org/blog/, some polish might be needed.

[guanlisheng]

here you go https://moneymanagerex.org/blog/assets- ... d-example/

[Simon1D]

Thanks @guanlisheng - that makes it a lot easier to read and yes, of course, I'm very happy to have it on the blog.
Cheers

[Simon1D]

TL,DR: In summary, as far as I can tell ...

What the MMEX dashboard shows as an asset's "Initial Value" is the VALUE stored in the database ASSETS_V1 table, and this has been updated from whatever was originally entered when the asset was created, by adding all the asset withdrawal/transfer transaction amounts, and subtracting all the asset deposit/transfer transaction amounts (for all the transactions that have been entered, including ones with a date in the future). "Current Value" differs from "Initial Value" by excluding the effect of updates due to transactions that have been entered but whose date is still in the future.

... but as always, I would welcome comments/clarifications/corrections.


Delving further into assets, and testing my limited understanding of how assets and asset values work, I'm puzzled by "Initial Value" and "Current Value" as shown on the MMEX dashboard.

Using DB Browser to look at my database, I see that the ASSETS_V1 table has a single numeric VALUE, rather than two independent values. That said, I've never seen the dashboard show Initial Value and Current Value to be different from one another (in my rather limited use of assets, that is).

I only recently came across @nstein's helpful explanation here https://github.com/moneymanagerex/money ... 1282865536 which includes a remark

"Entering this [asset] transaction forced the value of the asset to be recalculated."

and my interpretation of this is that MMEX must update the value stored in the table whenever an asset transaction is entered. From this it would seem that the value of an asset is handled very differently, say, from the "current" balance of an account - which isn't ever stored in the table and instead is always calculated "on the fly", by adding the relevant transaction amounts to the opening balance.

I might be interested in displaying the history of an asset value, and I suspect this would be obtained by subtracting all the relevant (i.e. back to the date of interest) asset transaction amounts from the value stored in the table, which I must assume is the *current* value (rather than the value assigned to the asset on the date it was created - my understanding of "initial" value, which seems different from what MMEX shows).

The treatment of asset values all seems backwards compared to that of account balances - maybe this is *literally* true, given that asset transactions have the opposite effect on asset values compared to naive intuition.

Edit: no sooner had I posted this comment, than I found a way to make the dashboard show different Initial Value and Current Value - enter a *future dated* asset transaction. It would appear that Current Value really does refer to "now" - and if an asset transaction has been used to update the asset value, this updating won't be undone if the transaction is voided, but it will be undone if the transaction is deleted. This is not the behaviour I would expect, tbh, but for the moment I'm treating it as a feature of the design, rather than (potentially) a bug in implementation.

[nstein]

MMEX must update the value stored in the table whenever an asset transaction is entered. From this it would seem that the value of an asset is handled very differently, say, from the "current" balance of an account - which isn't ever stored in the table and instead is always calculated "on the fly", by adding the relevant transaction amounts to the opening balance.

Quite right, historically assets have been handled the same way as stocks - we store the "current value" in the database and have a function to calculate the balance on any specific date (this is how the "Account Summary" reports function). Which is, as you noted, precisely the opposite of how we determine the balance of an account. I don't really have a good explanation of why it was designed this way, but whether we start at the end and work backwards or start at the beginning and work forwards we should end up with the same values. Users wouldn't know anything was different unless they looked "under the hood" as you have.

What the MMEX dashboard shows as an asset's "Initial Value" is the VALUE stored in the database ASSETS_V1 table, and this has been updated from whatever was originally entered when the asset was created, by adding all the asset withdrawal/transfer transaction amounts, and subtracting all the asset deposit/transfer transaction amounts (for all the transactions that have been entered, including ones with a date in the future). "Current Value" differs from "Initial Value" by excluding the effect of updates due to transactions that have been entered but whose date is still in the future.

Yes, the terms used in the dashboard do not represent the values displayed. The "Initial Value" should be the value on the asset STARTDATE. We should fix this. The "Current Value" is the value as-of today, but perhaps it should be shown the same way as balances in all other accounts are shown on the dashboard, i.e. include future transactions unless the user has turned on "Ignore Future Transactions" in Options -> View.

if an asset transaction has been used to update the asset value, this updating won't be undone if the transaction is voided, but it will be undone if the transaction is deleted. This is not the behaviour I would expect, tbh, but for the moment I'm treating it as a feature of the design, rather than (potentially) a bug in implementation.

I think this truly is a bug. Transactions marked "Void" should not be used for calculating balances.

I appreciate the thorough analysis you are doing here. It would be great if you could gather some suggestions for improving asset handling (or any bugs you find!) and submit them as new issues on GitHub to allow us to improve MMEX in this area.

[nstein]

I created this GitHub issue for correction of the "Initial Value" metric and the exclusion of "Void" transactions in the VALUE calculation.

https://github.com/moneymanagerex/money ... ssues/6610

[Simon1D]

Thanks very much for the detailed response @nstein (and addressing that bug, of course).

I am curently (but only intermittently) working through an analysis of my own mmex data - aiming for a view of net worth over time, past and anticipated, but using python/sqlite/pypika, which is closer to my comfort zone than lua scripting and mmex's own reporting capabilities would be (hence my need to look under the hood). However, that activity needs to have been completed in a week or so.

After that, I'll try to help however I can by e.g. offering suggestions prompted by having got my head around assets (etc).

Cheers

PS as an afterthought, I can see that an asset whose current value grows at some fixed rate as time passes (an mmex feature that I've never used) needs to work differently from an account, whose balance only ever changes as a result of transactions and where, for example, interest charged or credited only comes into play via explicit transactions. Perhaps an adequate treatment of asset values will always be more complicated than the way in which accounts and their balances work - quite apart from the fact that "value" is necessarily a matter of judgement, whereas an account balance is mere arithmetic.