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.
Get 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
Stock portfolio category
This blog article explains in greater detail how to determine stock portfolio performance based on units of NAV (Net Asset […]
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid […]
Excel categories
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.
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 get your Excel workbook??
To repeat Ron Temko's Question:
How and where can I get your Excel workbook?? It looks to be useful.
Prof. Michael Adler,
I can open 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' when I press with the left mouse button 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 units Transfer data We need monthly data from both the stock portfolio and S&P 500. First open [...]
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.