Investing can be a powerful way to build wealth over time, but it requires more than just buying stocks, bonds, or mutual funds. To truly master your financial destiny, you need to understand how to track your investments. This includes knowing your cost basis, keeping tabs on dividends, and measuring performance metrics. By mastering these elements, you can make informed decisions about when to buy or sell assets and how to optimize your portfolio for tax efficiency and growth.
Tips For Tracking Investments in Excel
Calculating Cost Basis
The cost basis of an investment is essentially the original value of an asset for tax purposes, usually the purchase price plus any associated costs such as commissions or fees. It’s important to calculate the cost basis accurately to determine the capital gains or losses when you sell an investment.
Steps to Calculate Cost Basis:
- Identify Purchase Price: Determine the amount you paid to acquire the asset.
2. Add Additional Costs: Include any transaction fees, brokerage commissions, and other expenses related to the purchase.
3. Adjust for Splits and Dividends: If the investment has undergone any splits or if you have reinvested dividends, adjust the cost basis accordingly.
Example:
Let’s say you bought 100 shares of XYZ Corporation at $50 per share, and you paid a $10 commission.
– Purchase Price: 100 shares * $50/share = $5,000
– Commission: $10
– Total Cost Basis: $5,000 + $10 = $5,010
If XYZ Corporation later had a 2-for-1 stock split, your shares would double, and your cost basis per share would halve.
– New Share Count: 100 shares * 2 = 200 shares
– Adjusted Cost Basis per Share: $5,010 / 200 shares = $25.05/share
Tracking Dividends
Dividends are payments made by a corporation to its shareholders, usually derived from the company’s profits. Tracking dividends is important for understanding the income generated from your investments and for accurate reporting on tax returns.
Steps to Track Dividends:
- Record Dividend Payments: Keep a log of all dividend payments received, including the date and amount.
2. Reinvested Dividends: If you reinvest dividends to purchase more shares, note the number of shares purchased and at what price.
3. Tax Reporting: Report dividend income on your tax return, and adjust your cost basis if dividends are reinvested.
Example:
Suppose XYZ Corporation pays a quarterly dividend of $1 per share.
– Annual Dividend: $1/share * 4 quarters = $4/share per year
– Total Dividend for 100 Shares: 100 shares * $4/share = $400/year
If you reinvest these dividends to buy more shares, you would adjust your cost basis accordingly.
Performance Metrics
Performance metrics are used to evaluate the success of your investment strategy. Common metrics include return on investment (ROI), annualized return, and comparing performance to benchmarks.
Steps to Measure Performance Metrics:
Calculate ROI: Determine the gain or loss on an investment relative to the amount of money invested.
2. Annualize Returns: Adjust the ROI to reflect a yearly rate, especially if the investment period is more or less than a year.
3. Benchmark Comparison: Compare your investment’s performance to a relevant index or benchmark to gauge its relative success.
Example:
If you sold your 100 shares of XYZ Corporation for $60 per share, your ROI would be calculated as follows:
– Selling Price: 100 shares * $60/share = $6,000
– ROI: (Selling Price – Cost Basis) / Cost Basis
– ROI: ($6,000 – $5,010) / $5,010 = 0.1976 or 19.76%
To annualize this return over a two-year holding period:
– Annualized Return = (1 + ROI)^(1/Number of Years) – 1
– Annualized Return = (1 + 0.1976)^(1/2) – 1 = 0.095 or 9.5%
Final Thoughts on Tracking Investments in Excel
Mastering investment tracking is essential for any investor looking to optimize their portfolio’s performance. By accurately calculating your cost basis, diligently tracking dividends, and measuring performance metrics, you can gain a clearer picture of your investment’s true performance and make more strategic decisions. Remember to keep detailed records and consider using investment tracking software to streamline the process. With these tools and knowledge, you’ll be well-equipped to navigate the complexities of investment tracking and position yourself for financial success.
Live Excel Support Transcript Example:
Below is a live transcript of one of our Excel Support experts helping a customer track investments in Excel:
Full Conversation:
Customer: Yeah, um, I’m actually, I’m going to look up what’s a normal way of doing that is, I mean, so the thing that gives me pause is that the, it’s weird that you would be counting the dividends as a percentage of the value after you subtract the dividends, you know, that sounds a little weird. Um, let’s see. Um, so as far as the IRS is concerned, the basis is the purchase price plus additional costs or commissions associated with the purchase. So that wouldn’t go down with, um, certain events. Yeah. Um, so that wouldn’t change for dividends for sure. Let me see if I can double check that. Okay. Um, well, in either way, you’ve got the column D is the contributed capital. Right. Oh, okay. Yeah. So we’ve got, we’ve got both things anyway. Okay. That’s fine. Yeah. And all, all I’ve done for the cost basis is I’ve taken the contributed capital, less the dividends. Okay. So we, we can just use the contributed capital and then our distributions, I guess. Yeah. So let’s say contributed capital, less the total distributions. That’s how I’ve gotten the cost basis. Okay. Got it. That, that, that sounds right. Um, okay. But then the, but my question would be, um, if you’re doing inception to date for this year, it doesn’t matter because it’s just the one year, but beyond that we would need prior distributions. Oh, I guess that’s what you can put in the E column. So, so, um, that’s a total distributions. Okay. There you go. Perfect. Yeah. So then, okay. Yeah. And Carl, if there was a, there was a more logical way of organizing this, you’re welcome to change it. Um, I think that’s probably, that seems, that seems, that seems right to me. Okay. So then what it should be is the current market value, not Q4, Q11, plus, yeah, minus, okay. So this is just if D11 is, okay. Um, so that would be the inception to date percentage. Um, what do you mean by inception to date cumulative? Um, okay. So you’re looking at a U. Yeah. Um, I guess that would have probably been that one. We can change the wording. Um, because, okay. Right. Because the other thing that I, that you might want, I guess, would be inception to date annualized. Exactly right. Yeah. I think I had those dates on there. In the W column, it looks like. Inception, inception date. Right. So, fun. Where’d those go? Oh, um, yeah. Where did those go? Oh, you said it was the seventh for everything, right? I think it was more or less, um, like November 7th, but they were there. Um, hold on. I think I have them on another sheet here. Yeah, sure. So then this can be, what’s up here? Um, it’s more or less November 7th and I can always go back and change those. Yeah. Um, so then what we really want is first, we can get the total percentage. So that’d be this. Exactly. Yep. Um, raise to the one over today minus the inception date. You’re good. And then raise that to the 365. Okay. So let’s make up some numbers here. Um, or unless you have actual numbers, we can put those in. Um, which one are you talking about? Um, so, I mean, let’s just, you know, if you’ve got this, uh, this Las Vegas, one of the top, we can put those numbers in. Yeah. So we’ve got 30,027. Sorry. You’re looking for the current market value of that guy. Um, yeah. And then any, you know, whatever, whatever, uh, Q4 distribution, if there’s been one, I guess. Yeah. So those probably won’t have a distribution because it’s a bond. It’s more like a loan. I’ve given him, you know, like, um, and yeah, for the current market value, which one are you looking for? Uh, Las Vegas VY. Las Vegas VY is at 5% or 4%. Oh, uh, 4%. Current cost was 30,000 bucks or 48. Oh, the contributing, the cost basis is 30,000. Yeah. 30,000. Okay. Perfect. And so the current value for that guy is 31,738.79. Um, that shouldn’t be. Shouldn’t be percentages really should. Um, well I show a gain of, yeah, yeah. I, this isn’t a format of the way that I want it. So those aren’t percentages. Those are just dollar amounts. Sorry. 31. I’ll put it in there. Okay. There we go. Oh, no, because that principle should be there. Apparently not. So, oh, because what it really should be is one plus that raised to one over today minus that. Okay. Then that isn’t right though. Okay. Okay. So obviously that’s, uh, a bit unrealistic at this point, but that’s because we’ve had that 5% gain in under a month. Right. So that like when, when the inception to date period is short, then the annualized rate is going to be super high variance. Um, yeah. Okay. I don’t see it on my end, but if you’re showing 5%, if you’re 5, 5.7% would be just the difference in, from the, I show 5.69%. So we are right there. Cool. Okay. Good. Love it. Okay. Perfect. I think this is the crux of it. And then ideally, so this is 2023. If I could, you know, is there like a template I could take? I don’t know if you’re able to say that as a template. So at 2024 and so on, I can kind of keep it flowing. Yeah. Um, to do that, you could just kind of, you know, you could, you know, you could, you know, keep it flowing. Yeah. Um, to do that, you could just copy this sheet, basically make a new sheet. The only thing you would want to do is put, um, the prior and, and like, so this won’t matter because these are bonds. Um, but for things that have distribution, do you just want to lump all of the previous distributions into this E column? Um, because you need that to get the, uh, inception to date total. Right. Cause those would still count for that. Okay. Um, Oh, I should include that in there. What the heck? Okay. Um, yeah, but other than that, there’s nothing about this that’s specific to 2023. Um, like this, this annualized thing is keying off of the current day. So that’ll just update every time you open the file. Right. Um, and yeah, other than that, you can just copy, copy to a new copy of the same sheet. Okay, perfect. Um, and my final question, and we don’t have to tackle this right now. Anyways, I hope this is like an ongoing conversation because I think some of this stuff is complex throughout the year and it seems like you have a nice ending of it. Um, so if you look down at row 106 through 108, I’m just going to put it here. Um, let’s see here. So you can see there’s some funds that are closed out. Uh-huh. And so what I’d like to do, and we can kind of come back to it, but so since those have been closed out, you can look at like the Goldman for example, that was a purchase price and I put it in the distribution column of what they closed at. Right. And so I’d like to somehow incorporate that into like the total performance. Does that make sense? Yeah. So the thing that’s complicated, if you want to get total performance, you sort of need dates for everything. Right. Because like, that’s how it gets weighted. Because like if that loss came in a week, that’s going to impact total performance differently than if it came in, you know, five years. Um, so like we, we can do that, but I think that it requires the information to be in like a somewhat different form than it is now. Um, and as I, um, as I think about this, I don’t know, I’ll, I’ll have to think a little bit about, about whether there’s a better way to organize this because this, so the thing that makes me nervous about doing it, laying it out like this is this seems, if I had this, I would screw it up. Um, and so I wonder if there might be a better way to do it where we’re not using the same sheet for input and for summary, where like you have one sheet for investments, right? Like when you make the, the, the, when you actually deposit the capital and then another sheet for, um, distributions, dividends, whatever, and then sort of have the sheet that does this work. Um, you don’t actually do input on, on an ongoing basis. Um, I think that that’s a summary page almost. Yeah. Right. Right. Right. Um, but yeah, so we can, um, I’ll think about what the best way to do that is. And then, you know, we can set something up maybe for the, the back end of next week. Um, I’ll have sort of more than limited time. Um, because yeah, the next, the next few days, uh, are pretty jammed. Um, but yeah, so if, if, if you want to schedule something for like next Thursday, Friday, um, we can do that then. Okay. This sounds great. Um, yeah, it sounds like we’re both pretty busy. So maybe next Friday. Okay. Um, and I think this does not need to be done right now. So if you think that that’s the best course of action, then I would say, let’s go for that. Yeah. Because, um, because I mean, like whatever makes the most logical, I mean, these are, this is years out, you know, this is a project. It’s not going to be just today, tomorrow, years. So, yeah, that, that, that would be fine. Um, but I think, I understand what you’re saying. This is not, that would not be like a 15 minute project in the afternoon. Right. Right. Right. Um, but yeah, so we can, um, I’ll think about what the best way to do that is. And then, you know, we can set something up maybe for the, the back end of next week. Um, I’ll have sort of more than limited time. Um, because yeah, the next, the next few days, uh, are pretty jammed. Um, but yeah, so if, if, if you want to schedule something for like next Thursday, Friday, um, we can do that then. Okay. This sounds great. Um, yeah, it sounds like we’re both pretty busy. So maybe next Friday. Okay. Um, and I think this does not need to be done right now. So if you think that that’s the best course of action, then I would say, let’s go for that. Yeah. Because, um, because I mean, like whatever makes the most logical, I mean, these are, this is years out, you know, this is a project. It’s not going to be just today, tomorrow, years. So, yeah, that, that, that would be fine. Um, but I think, I understand what you’re saying. This is not, that would not be like a 15 minute project in the afternoon. Right. Right. Right. Um, but yeah, so we can, um, I’ll think about what the best way to do that is. And then, you know, we can set something up maybe for the, the back end of next week. Um, I’ll have sort of more than limited time. Um, because yeah, the next, the next few days, uh, are pretty jammed. Um, but yeah, so if, if, if you want to schedule something for like next Thursday, Friday, um, we can do that then. Okay. This sounds great. Um, yeah, it sounds like we’re both pretty busy. So maybe next Friday. Okay. Um, and I think this does not need to be done right now. So if you think that that’s the best course of action, then I would say, let’s go for that. Yeah. Because, um, because I mean, like whatever makes the most logical, I mean, these are, this is years out, you know, this is a project. It’s not going to be just today, tomorrow, years. So, yeah, that, that, that would be fine. Um, but I think, I understand what you’re saying. This is not, that would not be like a 15 minute project in the afternoon. Right. Right. Right. Um, but yeah, so we can, um, I’ll think about what the best way to do that is. And then, you know, we can set something up maybe for the, the back end of next week. Um, I’ll have sort of more than limited time. Um, because yeah, the next, the next few days, uh, are pretty jammed. Um, but yeah, so if, if, if you want to schedule something for like next Thursday, Friday, um, we can do that then. Okay. This sounds great. Um, yeah, it sounds like we’re both pretty busy. So maybe next Friday. Okay. Um, and I think this does not need to be done right now. So if you think that that’s the best course of action, then I would say, let’s go for that. Yeah. Because, um, because I mean, like whatever makes the most logical, I mean, these are, this is years out, you know, this is a project. It’s not going to be just today, tomorrow, years. So, yeah, that, that, that would be fine. Um, but I think, I understand what you’re saying. This is not, that would not be like a 15 minute project in the afternoon. Right. Right. Right. Um, but yeah, so we can, um, I’ll think about what the best way to do that is. And then, you know, we can set something up maybe for the, the back end of next week. Um, I’ll have sort of more than limited time. Um, because yeah, the next, the next few days, uh, are pretty jammed. Um, but yeah, so if, if, if you want to schedule something for like next Thursday, Friday, um, we can do that then. Okay. This sounds great. Um, yeah, it sounds like we’re both pretty busy. So maybe next Friday. Okay. Um, and I think this does not need to be done right now. So if you think that that’s the best course of action, then I would say, let’s go for that. Yeah. Because, um, because I mean, like whatever makes the most logical, I mean, these are, this is years out, you know, this is a project. It’s not going to be just today, tomorrow, years. So, yeah, that, that, that would be fine. Um, but I think, I understand what you’re saying. This is not, that would not be like a 15 minute project in the afternoon. Right. Right. Right. Um, but yeah, so we can, um, I’ll think about what the best way to do that is. And then, you know, we can set something up maybe for the, the back end of next week. Um, I’ll have sort of more than limited time. Um, because yeah, the next, the next few days, uh, are pretty jammed. Um, but yeah, so if, if, if you want to schedule something for like next Thursday, Friday, um, we can do that then. Okay. This sounds great. Um, yeah, it sounds like we’re both pretty busy. So maybe next Friday. Okay. Um, and I think this does not need to be done right now. So if you think that that’s the best course of action, then I would say, let’s go for that. Yeah. Because, um, because I mean, like whatever makes the most logical, I mean, these are, this is years out, you know, this is a project. It’s not going to be just today, tomorrow, years. So, yeah, that, that, that would be fine. Um, but I think, I understand what you’re saying. This is not, that would not be like a 15 minute project in the afternoon. Right. Right. Right. Um, but yeah, so we can, um, I’ll think about what the best way to do that is. And then, you know, we can set something up maybe for the, the back end of next week. Um, I’ll have sort of more than limited time. Um, because yeah, the next, the next few days, uh, are pretty jammed. Um, but yeah, so if, if, if you want to schedule something for like next Thursday, Friday, um, we can do that then. Okay. This sounds great. Um, yeah, it sounds like we’re both pretty busy. So maybe next Friday. Okay. Um, and I think this does not need to be done right now. So if you think that that’s the best course of action, then I would say, let’s go for that. Yeah. Because, um, because I mean, like whatever makes the most logical, I mean, these are, this is years out, you know, this is a project. It’s not going to be just today, tomorrow, years. So, yeah, that, that, that would be fine. Um, but I think, I understand what you’re saying. This is not, that would not be like a 15 minute project in the afternoon. Right. Right. Right. Um, but yeah, so we can, um, I’ll think about what the best way to do that is. And then, you know, we can set something up maybe for the, the back end of next week. Um, I’ll have sort of more than limited time. Um, because yeah, the next, the next few days, uh, are pretty jammed. Um, but yeah, so if, if, if you want to schedule something for like next Thursday, Friday, um, we can do that then. Okay. This sounds great. Um, yeah, it sounds like we’re both pretty busy. So maybe next Friday. Okay. Um, and I think this does not need to be done right now. So if you think that that’s the best course of action,