Excel calc for portfolio performance
Discussion
I have kept records of my investment portfolio size and inflows/outflows for some time, but haven't come up with a performance calculation that gives me confidence.
I'm currently working with a calculation that gives me time weighted performance but it only works on days where there has been an inflow or outflow. Ideally I'd like one that can give me a figure for whatever date range I ask for.
I'd like something that tells me total growth, compound annual growth, growth by month and year etc.
Can anyone point me to a resource I can use?
I'm currently working with a calculation that gives me time weighted performance but it only works on days where there has been an inflow or outflow. Ideally I'd like one that can give me a figure for whatever date range I ask for.
I'd like something that tells me total growth, compound annual growth, growth by month and year etc.
Can anyone point me to a resource I can use?
I can't help with your question, sorry. However, it's not clear to me where having that information will actually take you.
I've never found a perfect way of tracking stuff and, to be honest, I've never seen any platform or manager report things in a perfect way. It seems relatively easy to track a static portfolio with income reinvested but the whole thing goes down the tubes as soon as cash is added or withdrawn from time to time. Then you crash into the question of whether the particular portfolio is in a wrapper (ISA/SIPP) or taxed (general investment account) with all the gross/net implications that go with it. And then there's the question of whether you're looking at arithmetic returns or real returns, adjusted for inflation. It does my head in. So, broadly speaking, I try not to fret too much about it. Just look at a year, what's gone in, what's come out, what's the total return and then what's the total "real" return. And all of this is before going anywhere near the balance of risk and reward.
I've never found a perfect way of tracking stuff and, to be honest, I've never seen any platform or manager report things in a perfect way. It seems relatively easy to track a static portfolio with income reinvested but the whole thing goes down the tubes as soon as cash is added or withdrawn from time to time. Then you crash into the question of whether the particular portfolio is in a wrapper (ISA/SIPP) or taxed (general investment account) with all the gross/net implications that go with it. And then there's the question of whether you're looking at arithmetic returns or real returns, adjusted for inflation. It does my head in. So, broadly speaking, I try not to fret too much about it. Just look at a year, what's gone in, what's come out, what's the total return and then what's the total "real" return. And all of this is before going anywhere near the balance of risk and reward.
It’s the old saying “you can’t manage what you can’t measure”.
I want to be fully informed about what’s going well and what’s not going well either in absolute terms, against another metric like inflation or a market index or against my own projections.
I also want to be able to answer questions like “what total and annual performance have you had from the past 3,5 and 10 years?” or “how much did your portfolio decline during the financial crisis?”.
Ultimately, that should better inform me about how I should manage risk in the future.
I don’t think the wrapper question is relevant. It’s the investment returns I’m after, not gross-up returns.
I want to be fully informed about what’s going well and what’s not going well either in absolute terms, against another metric like inflation or a market index or against my own projections.
I also want to be able to answer questions like “what total and annual performance have you had from the past 3,5 and 10 years?” or “how much did your portfolio decline during the financial crisis?”.
Ultimately, that should better inform me about how I should manage risk in the future.
I don’t think the wrapper question is relevant. It’s the investment returns I’m after, not gross-up returns.
I keep a simple Excel spreadsheet for each item, showing a price and valuation at the end of each quarter or half year. In many ways the "price" column is more informative than the "value" column. A also run a separate totals spreadsheet.
For things such as "impact of Covid" it happens I saved a snapshot as at 20 Feb 2020 fearing there may be trouble to come, and can simply use percentage calculations to see how things have moved since that date.
It's a bit of a pain inputting all the detail but once it's in the system it's in the system, and easy to see performance over time.
I've never found benchmarks particularly informative because results vary so much depending on the particular mix you've got on board. And as mentioned earlier, my brain melts long before getting into the balance of risk and reward.
Have you looked at Morningstar's "Portfolio Manager"? Might be worth a click, https://www.morningstar.co.uk/uk/portfoliomanager/... Inevitably they're keen to sell "Premium Membership" which would give greater analysis. I find Morningstar and Trustnet useful, free resources.
There's also a good deal of performance information available for free from the likes of Fidelity. For instance, this link should show how a fund has performed over any period you choose and compared with its sector. https://www.fidelity.co.uk/factsheet-data/factshee...
For things such as "impact of Covid" it happens I saved a snapshot as at 20 Feb 2020 fearing there may be trouble to come, and can simply use percentage calculations to see how things have moved since that date.
It's a bit of a pain inputting all the detail but once it's in the system it's in the system, and easy to see performance over time.
I've never found benchmarks particularly informative because results vary so much depending on the particular mix you've got on board. And as mentioned earlier, my brain melts long before getting into the balance of risk and reward.
Have you looked at Morningstar's "Portfolio Manager"? Might be worth a click, https://www.morningstar.co.uk/uk/portfoliomanager/... Inevitably they're keen to sell "Premium Membership" which would give greater analysis. I find Morningstar and Trustnet useful, free resources.
There's also a good deal of performance information available for free from the likes of Fidelity. For instance, this link should show how a fund has performed over any period you choose and compared with its sector. https://www.fidelity.co.uk/factsheet-data/factshee...
LeoSayer said:
I'd like something that tells me total growth, compound annual growth, growth by month and year etc.
Can anyone point me to a resource I can use?
These are quite simple things to write a formula for, you can write formulas that will calculate practically anything. I used to use the following website for help with writing them:Can anyone point me to a resource I can use?
https://www.excelfunctions.net/excel-formulas.html
Depends how you format your data but the XIRR formula is probably a useful one to use. Look up internal rate of return to understand what it means but essentially it is a time and cash flow weighted measure of return that is industry standard in the private markets world.
If you want to measure performance of your portfolio over different time periods against some specific benchmark it is perhaps not the best but it does allow you to fairly easily measure your portfolio performance on an absolute level.
If you want to measure performance of your portfolio over different time periods against some specific benchmark it is perhaps not the best but it does allow you to fairly easily measure your portfolio performance on an absolute level.
LeoSayer said:
It’s the old saying “you can’t manage what you can’t measure”.
I want to be fully informed about what’s going well and what’s not going well either in absolute terms, against another metric like inflation or a market index or against my own projections.
I also want to be able to answer questions like “what total and annual performance have you had from the past 3,5 and 10 years?” or “how much did your portfolio decline during the financial crisis?”.
Ultimately, that should better inform me about how I should manage risk in the future.
I don’t think the wrapper question is relevant. It’s the investment returns I’m after, not gross-up returns.
Everything youhave requested is handled relatively easily using Excel, but its not suited to a one-line answer in a forum unfortunately.I want to be fully informed about what’s going well and what’s not going well either in absolute terms, against another metric like inflation or a market index or against my own projections.
I also want to be able to answer questions like “what total and annual performance have you had from the past 3,5 and 10 years?” or “how much did your portfolio decline during the financial crisis?”.
Ultimately, that should better inform me about how I should manage risk in the future.
I don’t think the wrapper question is relevant. It’s the investment returns I’m after, not gross-up returns.
I use this one, not sure if it will give what you want.
I have each investment on a tab then an overall total.
https://www.vertex42.com/ExcelTemplates/investment...
I have each investment on a tab then an overall total.
https://www.vertex42.com/ExcelTemplates/investment...
highpeakrider said:
I use this one, not sure if it will give what you want.
I have each investment on a tab then an overall total.
https://www.vertex42.com/ExcelTemplates/investment...
That's the ticket - thanks.I have each investment on a tab then an overall total.
https://www.vertex42.com/ExcelTemplates/investment...
It shows an XIRR of 6% since 1999 - the same as my own XIRR calc.
A bit different to my own time weighted perf calc of 8.5% - so I'd like to try and understand why they're so far apart.
IM use the Modified Dietz Method:
https://en.wikipedia.org/wiki/Modified_Dietz_Metho...
Mr Groat doesn't
https://en.wikipedia.org/wiki/Modified_Dietz_Metho...
Mr Groat doesn't
Mr Pointy said:
IM use the Modified Dietz Method:
https://en.wikipedia.org/wiki/Modified_Dietz_Metho...
I found an online calculator for that and it's showing something like 1.5% which just can't be right, given that my portfolio is up over 80% is absolute terms.https://en.wikipedia.org/wiki/Modified_Dietz_Metho...
Having read up on it, it sounds like Modified Dietz it is most useful when time weighted performance can't be calculated due to lack of historic valuation data, which isn't a problem for me.
Gassing Station | Finance | Top of Page | What's New | My Stuff