## Calculate your stock portfolio performance with Net Asset Value based on units in excel

In a previous related post we calculated the stock portfolio performance using the most current stock prices compared to buying prices. Dividends, interest, deposits and withdrawals are not calculated in this post.

In another previous related post we created a web query to calculate Net Asset Value at any given date. Net Asset Value is cash balance in your stock account + total portfolio stock value. Dividends payed, interest earned and commissions are calculated and affects account balance. Deposits and withdrawals are not calculated in this post.

In this post we are going to calculate units of Net Asset value. Doing so we can easily calculate stock portfolio performance also when a deposit or withdrawal is made. Calculate portfolio performance by comparing your account balance day by day is wrong. Why? When you make a deposit or withdrawal, portfolio performance is distorted.

**Example,**

Day 1, Deposit $10 000 - Account balance: $10 000

Day 2, Buy 200 shares Caterpillar @ $40

Account balance: $9 990 ($40*200= $8000 + cash $2000 - Commission:$10 = $9 990)

Day 3, Deposit $10 000

Account balance: $9 990+$10 000= $19 990 (Caterpillar is still at $40)

The account balance has increased from Day 1: $10 000 to Day 3: $19 990 but the stock price is the same.

Portfolio performance: $19 990/$10 000 -1= +99,9%

Clearly calculating stock portfolio performance based on account balance is wrong.

**Example - Net asset value based on units**

**Day 1**, Deposit $10 000 - Account balance: $10 000.

I have chosen 100 NAV units in this example but any number will do. NAV / Units is $10 000 / 100 units = $100 per unit.

NAV/unit is the number we compare day by day to calculate portfolio performance.

**Day 2**, Buy 200 shares Caterpillar @ $40 (Commission $10) - Account balance: $9 990

NAV is now $9 990 and we divide with 100 NAV units equals $99,90 per unit.

Portfolio performance: 99.90/100.00 -1= -0,1%

**Day 3**, Deposit $10 000 - Account balance: $9 990+$10 000= $19 990 (Caterpillar is still at $40)

Account Balance = NAV. NAV is now $19 990.

NAV units increases when we do a deposit and decreases when we do a withdrawal.

NAV units increases $10 000/$99,90 = 100,1001001 units to a total of 200,1001001 units.

NAV / Units = $19 990 / 200,1001001 = $99,90

Portfolio performance: $99,90/$100,00 -1= -0,1%

(The commission is affecting stock portfolio by -0,1%.)

**Excel tutorial file**

In a previous related post we created a web query to calculate Net Asset Value at any given date. I have added NAV units, NAV / units and portfolio performance to the attached file.

Download excel file

Track-your-stock-portfolio-performance.xls

Excel 97-2003 *.xls

Remember to enable macros and you can´t undo a macro. Backup!

**NAV formula in L7:**

Copy and paste cell down as far as needed.

**NAV units formula in M7:**

Copy and paste cell down as far as needed.

**NAV per unit formula in N7:**

Copy and paste cell down as far as needed.

**Portfolio performance formula:**

Copy and paste cell down as far as needed. Format cells as percentage.

**Previous related blog posts**

Calculate your stock portfolio performance in excel

Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel

Automate net asset value (NAV) calculation on your stock portfolio

Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]

Compare your stock portfolio with S&P500 in Excel

By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid […]

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 […]

### 18 Responses to “Calculate your stock portfolio performance with Net Asset Value based on units in excel”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

How do I get your excel software?

Thank you!! I have one account and in it I have a portfolio of stocks and a portfolio of bonds. Your spreadsheet makes it very eazy to track the performance of both portfolios separately even with multiple activities associated with each portfolio. Before It was very labor intensive to keep the two portfolios individually since they were in the same brokerage account. One could track many sub portfolios just by setting up your NAV spreadsheet for each one and checking to make sure the sum of the sub portfolio's NAV was equal to the brokerage account NAV.

It also will be eazy to reallocate between the two just by changing the Start values from say 80/20 to 60/40 or whatever in the spreadsheet. After resetting the start values the ending cash values will tell me how much to move between the portfolios and not destroy my previous daily accounting. Great!!!

I put you on my favorites list :D

John Prather,

I am really happy you found it useful!

Actually, I have found that with the two portfolios you just sell/withdraw one and deposit/buy the other to reallocate. Don't mess with the start values. That way your historical NAV information is unaffected so if you are graphing the data, the graphs stay intact through the reallocation. Certainly an important upgrade to my past efforts and very efficient time wise. I just down load the daily activity from Interactive Brokers and plug in the info (buys, sells, dividends, accured dividends, fees, and commissions) into the two NAV sheets and my portfolios are nicely separated so that performance of each is trackable.

To repeat Ron Temko's Question:

How and where can I download your Excel workbook??

To repeat Ron Temko's Question:

How and where can I download your Excel workbook?? It looks to be useful.

Prof. Michael Adler,

I can download the file here. If it doesn't work, try later or email me.

Track-your-stock-portfolio-performance.xls

Hi, The spreadsheet you provided in your comment above "Track-your-stock-portfolio-performance.xls" gives Runtime Error'1001' whenI click on update "Unable to open: table.finance.yahoo.com/..."

thanks, Uday

uday

It is well known that Yahoo made significant changes to their US website in mid May 2017. A lot of people were left high and dry most of the price look up does not work any more and there are no straight forward work arounds. If you find one please post

I am using this now on several portfolios within one brokerage account. I have learned that by carefully selecting the number of NAV units in each portfolio you can make them all have the same NAV/unit at the start. Doing this allows for easy graphing of the portfolios vs time to get relative performance. Vey nice Spread Sheet.

[...] based on units in this post.Automate net asset value (NAV) calculation on your stock portfolio (vba)Calculate your stock portfolio performance with Net Asset Value based on unitsDownload dataWe need monthly data from both the stock portfolio and S&P 500. First download [...]

john,

i am in the process of trying to set this up. I am confused a bit about the second page and what needs to be input here ? or does it simply pull from the main page where everything is entered? Also, is it possible to pull data from Intereactive brokers instead of yahoo?

any extra help would be appreciated

thanks,

wyatt

john,

ok figured out my last questions. Running into two issues. Will it affect the data if i short stocks and have shorts in the portfolio?

When i press the "update button" the spreadsheet locks up on me, and i have to force it to close, for it never recovers", any thoughts?

Since you are far more ahead of my excel skills, can you write a simple formula that will give me percentage of returns automatically whether it is a buy or sell? i am assuming that somewhere in the equation, there has to be IF=BUY, then, or IF = Sell then....just to get the math right when getting percentage returns. I have racked my brain and cannot figure it out

thanks for any help you can provide. I see that the last commetns were quite sometime back, hope your still around

cheers,

wyatt

So when we sell some shares, we have to deduct the NAV units from the total NAV units right?

Akshay

Yes

Hi,

this was really helpful.

One question though - what happens when we sell the stock?

If we sell something in a loss, then also units are reduced and NAV increases?

Hi,

I've been using your spreadsheet for 10 years. Thank you. Its great. I update it on the 30th of every month with any withdrawal/deposit plus put in the new total value of my portfolio. I don't track individual stocks in this. I just treat my portfolio like a mutual fund and this spreadsheet has helped me to understand performance year by year. I update the contributions made and portfolio value each month with one line item.

As I've been relooking at this, I have a question about Column M that is confusing me. If I deposit or withdraw in the month, the updated # NAV units is affected by both the deposit/withdrawal amount and the new gains/losses in the portfolio. WHen I do a trial and deposit money but assume the portfolio value was unchanged (no gain or loss in the month) I get a different number of NAV units added/subtracted than if I update the portfolio value at the same time. Can you explain this please?

Thanks

Craig

Craig,

I am not sure I understand, you get a different number of NAV units because your portfolio value has changed during the month?

For example, day 3 above has this comment: (Caterpillar is still at $40)

This makes the example calculation easier, we only have to calculate the new number of NAV units for day 3.

However, if Caterpillar was at $38 at the end of the day 3 the NAV becomes 38*200 = 7600 + 1990 = 9590. NAV/units is 95.90. The deposit is $10000 and 10000/95.90 = 104.27528 additional units. The total number of NAV units is now 104.27528 + 100 = 204.27528.