All you need to know about calculating NAV units for your stock portfolio

This blog article explains in greater detail how to determine stock portfolio performance based on units of NAV (Net Asset Value). I made a similar blog article before (Calculate your stock portfolio performance with Net Asset Value based on units in excel) and the questions I got from that article are answered in this article.

NAV units make it easier to calculate the value of a stock portfolio if you deposit or withdraw money from your account. The value of a particular stock is changed when the markets are open, this means that NAV / unit changes when stock prices go up or down.

It is recommended to calculate NAV / unit when stock markets are closed using the closing prices of the stocks you have in the portfolio. You can then monitor the performance of your stock portfolio day by day.

Day 1 - Deposit money

Today you deposit $100 000 to your account meaning you add money $100 000 to your broker account. A stockbroker is a firm that buys or sells shares or other securities based on what you tell them to do. They often accept orders by phone calls or a trading application through the internet.

I use 1000 NAV units in this example but you can use any number you like here. NAV is $100 000 and NAV / unit is $100 000 / 1000 = $100 per unit.

Formula in cell H2:

Day 2 - Buy a stock

On the next day you buy 50 Google shares and they cost $1000 each. You pay $10 in commission.  You now have  $100 000 - 50*$1000 -$10 = $49 990 in cash in your account.

The stock is worth the stock price multiplied with the number of shares you own, in this case, 50 shares. $1000 * 50 shares = $50 000. Your NAV (account balance) is the value of the stocks plus the cash in your account, $50 000 + $49 990 = $99 990.

You have 1000 NAV units and the NAV / unit is now $99 990 / 1000 = $99.90. Portfolio performance is 1 - 99.9/100 = -0.1%.

Why did the stock portfolio decrease in value? You paid $10 in commission.

Formula in cell I2:

$H$2 is an absolute cell reference meaning it won't change when you copy the cell and paste to cells below. On the other hand, H2 is a relative cell reference and it will change when you copy and paste the cells to cells below as far as needed.

Column I shows the performance from date 1 and not based on the prior day or the prior calculation. To calculate the performance day by day use this formula in cell I3:

Both cell references are relative and will change when you copy the cell to cells below.

Day 3 - Withdrawal

Google share price is today $1010. Your stocks are now worth 50 * $1 010 = $50 500. You have $49 990 in cash and your account balance is $50 500 + $49 990 = $100 490.

NAV / unit is $100 490 / 1000 = $100.49. Portfolio performance is 1 - 100.49/100 = 0.49% This article explains how to

You have $49 990 in cash and you have decided to withdraw $20 000 from your account. The cash is now $49 900 - $20 000 = $29 990. A deposit or withdrawal affects your NAV units. How many NAV units?

$20 000 / $100.49 is approximately 199.02 NAV units. 1000 - 199.02 is approximately 800.98 NAV units.

Despite the fact that the account balance is lower than the day before, your stock portfolio performance is still up 0.49% compared to day 1.

Day 4 - Dividend

You receive a dividend $10 per share. A dividend is a cash payout from the company to the shareholders which is common if the company is profitable.

50 * $10 = 500. Your cash is now $29 990 + $500 = $30 490.

Your Google shares are now worth $1 007 per share. 50 * $1007 = $50 350. Your account balance is $30 490 + $50 350 = $80 480

Dividends affect NAV / unit. NAV / unit = $80 480 / 800.98 is approximately $100.48

The stock price went down from $1010 day 3 to $1 007 today (day 4), however, you also received a dividend that increased the NAV value.

Day 5 - How is your stock portfolio doing today?

Google is now at $1050. $1050 * 50 = $52 500. Your have $30 490 in cash. $52 500 + $30 490 = $82 990.

$82 990 / 800.98 is approximately $103.61. Your portfolio is up 3.61%

Day 6 - Deposit $20 000

Google is now at $980. 50 shares * $980 = $49 000. Your have $30 490 in cash.  $49 000 + $30 490 = $79 490. NAV  / unit is $79 490 / 800.98 = $99.24. Your portfolio is down -0.76%

Deposits affect NAV units. $20 000 / 99.24 is approximately 201.23 NAV units. 800.98 + 201.23 = 1002.50 units

You now have $30 490 + $49 000 + $20 000 = $99 490. The portfolio is down -0.76%, however, your account is now worth more than day 5 because of that deposit you made.

Day 7 - Sell stock

Today you sell Google stock at $1100. 50 shares * $1 100 = $55 000. You have $50 490 in cash - $10 in commission + $55 0000= $105 480.

Selling a stock affects NAV / unit.  The NAV (account balance) is $105 480 and NAV units are $1002.50.

$105 480 / 1002.50 is approximately 105.22 Your portfolio is up 5.22% compared to day 1.

Day 8 - Short selling a stock

Short selling means that you borrow shares from someone and return the shares at some point in the future. This allows you to sell the stock shares and then, later on, buy them back.

This is something you can do when you believe the share price is going to go down. Today you sell 100 shares of Caterpillar at $70. $105 480 -$10 = $105 470.

NAV / unit is $105 470 / $1 002.50 = $105.22 Your portfolio is up 5.21%

Day 9 - How is your stock portfolio doing today?

Caterpillar is at $71. $70*1000 - $71*1000 = -$1000. $105 470 - $1000 is $104 470

$104 470 / $1 0002.50 = $104.21. Portfolio is up 4.21% from date 1.

Day 10 - Buy stock

Today you buy 1000 shares of Caterpillar at $60. The share price is lower than day 9 so you made a profit.

The NAV value is $105 470 + $70*1000 -$60*1000 -$10 = $115 460. NAV units are $1 002.50. The NAV / unit is $115 460 / $1002.5 = $115.17

The stock portfolio is up 15.17% from the start.

Remember

1. Deposits and withdrawals change NAV units.
2. Stock value, dividends and commissions change NAV / unit.

Read more

• How to calculate NAV? Measuring portfolio performance
• Let Excel do the calculations, here is a basic example:
  Automate net asset value (NAV) calculation
• Compare your stock portfolio to S&P500

Get example file *.xlsx

NAV calculations.xlsx

Stock portfolio category

Automate net asset value (NAV) calculation on your stock portfolio
Table of Contents Automate net asset value (NAV) calculation on your stock portfolio Calculate your stock portfolio performance with Net […]

Compare the performance of your stock portfolio to S&P 500 using Excel
Table of Contents Compare the performance of your stock portfolio to S&P 500 Tracking a stock portfolio in Excel (auto […]

Excel categories

Comments (1)