0
$\begingroup$

I am tracking my portfolio in Google Sheets. I have columns like daily NLV, % and value change etc. This stats are used to calculate some extra data and draw performance charts.

Now, I want to withdraw some cash from my trading account. But I do not know how to make entry about it in my Google Sheets. I can not just reduce the NLV because it will show loss in stats and performance charts. So, what is the available ways to show withdraw in Google Sheets?

Problem: if i do withdraw and put new value in NLV, my NLV will be reduced by withdraw amount. And this reduction will break my other stats which are calculated by using data from NLV column

$\endgroup$

1 Answer 1

0
$\begingroup$

This is an Accounting question, but OK.

You need a column called Xin (cash in) that shows withdrawals (negative) and additions (positive) of cash to the account, leave it blank when nothing happens. Then you add another column called Surplus, the Surplus of today is the Surplus of yesterday, plus the NLV of today minus the NLV of yesterday minus today's Xin. (In words it is the sum of changes in NLV which is endogenous, i.e not explained by cash movement in and out). Then the Surplus column is what you look at to see if you are making money or not.

(BTW the difference between the NLV (Net Liquidation Value) column and the Surplus column is called the PaidInCapital column, some people like to show it between the Xin and the Surplus. PiC only changes when there is a Xin movement in or out).

This is just a general concept to get started, it is likely that many other parts of your spreadsheet will ahve to change also once you allow cash additions and withdrawls. It is a major accounting upgrade.

$\endgroup$
4
  • $\begingroup$ thank you, i will try to do it. Is this some common Accounting method to handle such situations? And to calculate additional statistics, i need to use Surplus column or original NLV? $\endgroup$
    – vlad popov
    Aug 21, 2023 at 12:28
  • $\begingroup$ what column should i use to calculate 'chg, %' between today and yesterday? $\endgroup$
    – vlad popov
    Aug 23, 2023 at 12:24
  • $\begingroup$ Personally I use surplus divided by NLV at December 31 of the previous year to find the YTD return. Each new year I change the formula's absolute refernce to the most recent December 31. $\endgroup$
    – noob2
    Aug 23, 2023 at 16:05
  • 1
    $\begingroup$ i tested this solution, but decided to go with different approach, for example if i withdraw 10k, i reduce NLV by % which is 10k from current NLV. $\endgroup$
    – vlad popov
    Sep 8, 2023 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.