Questions concerning calculating Gains/Losses and DCA

[Diesel]

First off, let me say thanks to everyone in this wonderful forum for all help you've personally provided me and for your posts in other sections I've enjoyed reading.

My excel spreadsheet is coming along quite nicely but now I have another question. Perhaps it has it's own definition and someone can explain.

I track each contribution I make per fund. The purchase price of the share is input on the day the transaction occurs. I then calculate the gain/loss on that specific transaction based on the latest fund prices. I tally that up by all transactions in the form of amount of money I have gained or lost for a particular fund. I've got that and understand how that works.

I also know there is a different type of gain/loss. If I sell (through an IFT) at higher price than originally purchased I have a positive gain, and at a lower price than I purchased, a lower gain.

How do you all track the difference between those two types of gains/losses in your spreadsheet? Is there a universal definition to differentiate the difference in the two?

I’ve also been attempting to understand DCA and I think I have it but want to make sure. If I make steady purchases over an extended period of time within the same fund can’t I take the total cost of all the shares in a particular fund and divide that by the total number of shares? Does that apply even if I’ve contributed different amounts of money at specified increments? If this is the case, can’t I determine by this formula (TOTAL COST/TOTAL # SHARES) what the target share price must get achieve to have zero gain and zero loss?

 

[peterson82]

If this is the case, can’t I determine by this formula (TOTAL COST/TOTAL # SHARES) what the target share price must get achieve to have zero gain and zero loss?

I believe this is the case.

I also know there is a different type of gain/loss. If I sell (through an IFT) at higher price than originally purchased I have a positive gain, and at a lower price than I purchased, a lower gain.

It seems you would have to keep track of the Price of the shares at buy time. So you would need to have a section of the spreadsheet that tracks all your purchases, and at what price you purchased them at. Or you could just update an average everytime you purchase a fund. This average could then be used to determine how much you gained and lost.

So I would make a section for each fund with an average share price (Total Price of Shares/Total Shares Purchased). When you sell use that number for you gains or losses.

 

[fabijo]

I don't know how much this will help, but think of it this way.

The total number of shares times the price of the shares is how much in assets you have.

If you kept track of how much money you were putting into your TSP, your gain in dollars would be your current value of assets minus how much you deposited.

To get a percentage amount of your gains, it would be (current value minus deposits) / deposits.

It might be easier to see the formulas like this:

totalValue = sharePrice x numberShares

dollarGain = totalValue - totalDeposits

percentGain = (dollarGain / totalDeposits) x 100

priceToBreakEven = totalDeposits / numberShares

I hope that helps. (and I hope I'm right) This is all assuming that you are only in one fund at a time.

 

[eccougar]

At one time, I used the xirr in excel.
http://www.gummy-stuff.org/XIRR-stuff.htm

 

[Diesel]

Thanks everyone for your great explanations. I can tell you all have helped many through your advise and explanations. Thanks also for the gummy-stuff link. Great information with lots to learn.