| ETFs and NAVs |
Mutual Funds invest in a basket of stocks and, at the end of each day, the Net Asset Value (NAV) is calculated, depending upon what percentage
of the fund is invested in what stock.
Alas, you have to wait until the market has closed to discover the Fund NAV. 
Exchange Traded Funds (ETF) are much like Mutual Funds except that they trade throughout the day, just like stocks.
In fact, their price depends upon the mechanics of bid-and-ask rather than the prices of the underlying stocks.
Usually, the NAV of the ETF will be available on some website after the market has closed and, usually, it's pretty close to the last trade, like so:
>So what's your point?
Wouldn't it be fun to know the NAV throughout the day. Then you could tell whether the ETF is trading low or high and buy or sell accordingly.
>You mean the NAV isn't available thoughout the day?
Sometimes it is ... sometimes it ain't. See, for example, etfconnect.com
>But what if the ETF has dozens of components? How would you ...?
How would you calculate the NAV? As it happens ...
>Aah, you have a spreadsheet, right?
Right. 
Here's what we do:
- After the market has closed we download all the component stock prices as well as the ETF closing price.
This establishes our BASE Prices. - If the ETF BASE Price is, say, $50, we divide this up according to the weights attached to each component.
For example:
If the most heavily weighted stock has a weight of 9.25% then we allocate 0.0925*($50) = $4.63 to that first stock.
If the next stock has a weight of 8.97% then we allocate 0.0897*($50) = $4.49 to that stock.
If the next stock has a weight of 7.32% then we allocate 0.0732*($50) = $3.36 to that stock.
If the next stock ...
We now have the ETF BASE Price subdivided according to the weights.
- On subsequent days we download the current current stock prices and see how they've changed from their BASE Prices and apply these changes to the ETF Price.
For example:
If the first stock has changed by 0.7% we change the $4.63 allocation to ($4.63)*(1.007) = $4.66.
If the next stock has changed by -0.9% we change the $4.49 allocation to ($4.49)*(0.991) = $4.45.
If the next stock has changed by ...
Adding all the modified allocations, we might have, for example, $51.25 which is our calculated NAV at the time we downloaded the current prices.
Now we compare to the current ETF price (which we downloaded along with all the component prices).
Then we decide whether the ETF is trading high or low and ...
>Where's the spreadsheet?
It's here ... just click on the picture to download:
There's an Explain sheet like so:
>There's a second sheet?
Yes, to download the BASE Prices. It looks like this:
>But where do I get the weights for the various components?
I dunno ... but for the EEB ETF that's in the spreadsheet you can go here:
www.claymore.com
>Aren't you assumimg that the downloaded BASE Price is the NAV? Why wouldn't you ...?
Uh ... yes, you're right. However, in cell I5 is the old downloaded BASE Price.
You may want to change it to the old NAV which (somehow) you get from a website.
>So I can make some money with that spreadsheet?
Money? Didn't I say: "Wouldn't it be fun to know the NAV throughout the day."
That's your reward: FUN!
