<p>This is an Accounting question, but OK.</p>
<p>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.</p>
<p>(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).</p>
<p>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.</p>

CC BY-SA 4.0
2023-08-21T12:28:57.240Z
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?
vlad popov
CC BY-SA 4.0
2023-08-23T12:24:28.627Z
what column should i use to calculate 'chg, %' between today and yesterday?
vlad popov
CC BY-SA 4.0
2023-08-23T16:05:18.000Z
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.
noob2
CC BY-SA 4.0
2023-09-08T10:24:18.923Z
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.
vlad popov
CC BY-SA 4.0
2023-08-21T09:38:12.270Z
2023-08-21T16:45:05.453Z
2023-08-21T16:45:05.453Z
noob2
<p>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.</p>
<p>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?</p>
<p>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</p>

<finance><statistics><accounting>
Show withdraw funds from trading account in Google Sheets
vlad popov
# Show withdraw funds from trading account in Google Sheets

(Asked by: username_0 on 2023-08-21)

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

**Tags:** finance, statistics, accounting


---

## 1 answers

### Answer by username_1 on 2023-08-21. Score: 0 | Accepted answer

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.

> **Comments:**
> **username_0**: 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?  
> **username_0**: what column should i use to calculate 'chg, %' between today and yesterday?  
> **username_1**: 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.  
> **username_0**: 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.  

---

