Spreadsheet to track performance

Question for the excel spreadsheet and maths wizards out there.

When creating a spreadsheet to track the performance of a particular investment does this sound like the correct way to capture and measure.

create an entry of every purchase. Date, amount invested and an entry for every dividend reinvested or not. Add up total of capital contributed and minus that from total investment value to date including all dividends. Take the profit to date and divid by the total capital contributed. This gives you the percentage of total return. Then to capture p.a return you divide that by the years held. Years held would be calculated from the date of the first purchase?

Is the above an accurate way to capture performance via a spreadsheet? Something that’s got me a little perplexed is the fact that different size purchases at different times would skew the end p.a return. Based on the formula I mentioned above does it accurately capture rate of return?
Example. If you make a number of small purchases in year 1 and you manage a 20% return. Year 2 you make a very large purchase. The moment you make that large purchase which has not generated a return yet, your average return across the original purchases that returned 20% and this additional large purchase would come right down.

Am I missing something or is that exactly how it should be captured?

 

I think you are correct or that’s how I would calculate too. Different timing, amounts, entry price will impact the overall return over time

 

One of the best methods for calculating an average return for a stock investment is the XIRR function in Excel

 

@devank is an expert of MSE.

 

Thank you sir. Had not heard of this function before. Will play around with it.

 

You could also look for CAGR excel formula

 

Plenty of free online calculators

XIRR Calculator - Irregular Internal Rate of Return

 

That would be too easy. Since I am no longer messing around with picking which fund to invest in and when to invest, I have turned to creating unnecessary spreadsheets to keep me busy.

 

Silly question, but looking at how this works you enter the amounts invested and the dates. Where do the dividends get captured in this whether they are collected or reinvested?

 

Fair enough. You can use my template if you want.

 

HAHAH

Why you trick me like that? Got me all excited for nothing.

 

Midway through a conversation with a friend a couple of years ago, he asked me how my investments were performing.

“Good” I said.

“So what’s your rate of return?” he asked.

Me: Blinking.

Isn’t that the whole point of index investing? By not focusing on my actual rate of return and instead accepting the market returns, I knew I should expect to do better than a large majority of my peers chasing the stock market up and down.

Yet, I really had no idea how my particular returns were performing.

You can rely on the returns published by the funds each year, but that’s the time-weighted return and not the actual return that you’re getting.

In other words, if you take a look at the Vanguard Total Stock Market Index Fund’s (VTSAX) performance, you’ll see that it returned 12.56% in 2014, 0.39% in 2015 and 12.66% in 2016.

But this isn’t the dollar-weighted return that you’d actually see since you’ve been buying into the fund at various times (and possibly selling as well). Some of those purchases were at dips in the market while others were at high points. How can you figure out how your portfolio is actually doing?

While obsessively tracking your returns is not necessary, you should still know how to check in and see how you’re doing from time to time. This is particularly true if you’ve got an investment in something like a real-estate crowdfunding platform or peer-to-peer lending (e.g. Prosper) where you might not have good data on your actual returns.

It’s surprisingly easy to calculate your returns and keep track of this stuff. All of you have to know is the amount of money that you’ve invested and the dates you made those investments (or any withdrawals you’ve made). After that, a spreadsheet program like Excel or Google Sheets can handle the rest.

Using XIRR to Calculate Returns

 

Sharesight's Performance calculation methodology

Performance calculation methodology | Sharesight Help