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
Related posts:
Compare your stock portfolio with S&P500 in excel
Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel
Calculate your stock portfolio performance in excel
Excel udf: Import historical stock prices from yahoo – added features
Recommended categories
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
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
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 [...]