Personal Investment Returns Spreadsheet (PIRS)

[scoothome]

With a hat tip to longinvest, who long ago created a returns spreadsheet for Bogleheads, I have created a modified version in response to user requests.

• Added money-weighted rate of returns and time-weighted rate of returns for individual accounts for life of accounts, specified periods (e.g., 1 yr, 3 yr, 5 yr, etc.), and annual returns
• Added Growth of $10K chart for individual accounts (up to 10 at a time)
• Added ability to toggle accounts on/off (up to 20 accounts)
• Modified sheet protection to allow reformatting without unprotecting

I have implemented this in Excel 365 and Google Sheets. Unfortunately, the Excel file is not backward compatible to earlier versions, as I use recently introduced functions. The Google Sheets version does the same calculations, but it appears that its IRR function is not as robust as Excel. Google Sheets IRR may fail to return a solution without an initial rate guess, whereas Excel solves it without any initial rate guess.

Here are some screenshots.

Money-weighted rate of return (MWRR) and time-weighted rate of return (TWRR) for life of portfolio and individual accounts:


Ability to show either MWRR or TWRR for specified periods:


Growth of $10K chart for individual accounts:


Annual MWRR or TWRR returns for portfolio and individual accounts:


Edit: see additional screenshots in these posts: post, post, post, post, post, post, post, post, post, post, post, post.

Here are links to obtain the Personal Investment Returns Spreadsheet (PIRS) in Google Sheets or Excel:
Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

I will likely not have the bandwidth to entertain many feature requests but Bogleheads are welcome to post their modifications directly in this thread. I can update the link to the latest and greatest in the original post if an update becomes the "official" version.

[retiringwhen]

Looks good, another toy to play with

[markgardner]

For MWRR, since the timing of cash flows is accounted for, isn't it necessary to also record dividends and interest and subsequent reinvestment?

[scoothome]

For MWRR, since the timing of cash flows is accounted for, isn't it necessary to also record dividends and interest and subsequent reinvestment?

No, that gets captured by the end of month account balance. As a Quicken user, the MWRR 1 yr, 3 yr, and 5 yr in PIRS match Quicken's Avg. Annual Return 1 yr, 3 yr, 5 yr % to within 0.1% by only considering transfers into or out of the account (e.g., from/to a bank account). I wish Quicken went beyond 5 years...

[BHawks87]

The updated sheet looks great except I have data from 2016 and Google Sheets isn't robust enough to return data from that far back (as you noted in the post).

[scoothome]

The updated sheet looks great except I have data from 2016 and Google Sheets isn't robust enough to return data from that far back (as you noted in the post).

Hmm, that's a bit surprising because the demo Google Sheet was able to handle 15+ years of data. [Update: it can handle more than 25+ years of data with Version 2.1] It might have something to do with your particular cash flows. Was it able to compute at least the 5 yr MWRR? I don't know if changing the IRR initial guess will work for you.

[BHawks87]

The updated sheet looks great except I have data from 2016 and Google Sheets isn't robust enough to return data from that far back (as you noted in the post).

Hmm, that's a bit surprising because the demo Google Sheet was able to handle 15+ years of data. It might have something to do with your particular cash flows. Was it able to compute at least the 5 yr MWRR? I don't know if changing the IRR initial guess will work for you.

Interesting.

Everything is handled correctly for the specific account but on the "Returns" tab I get a #REF! error saying "Error
Function INDIRECT parameter 1 value is 'E - Traditional IRA!E111'. It is not a valid cell/range reference." It is like this for every calculation on the "Returns" tab and the graphs don't work. I only had one account that I copy and pasted all my data from the previous spreadsheet from. I added a second account to see if that made a difference and no change.

Perhaps I'm doing something wrong?

[scoothome]

The updated sheet looks great except I have data from 2016 and Google Sheets isn't robust enough to return data from that far back (as you noted in the post).

Hmm, that's a bit surprising because the demo Google Sheet was able to handle 15+ years of data. It might have something to do with your particular cash flows. Was it able to compute at least the 5 yr MWRR? I don't know if changing the IRR initial guess will work for you.

Interesting.

Everything is handled correctly for the specific account but on the "Returns" tab I get a #REF! error saying "Error
Function INDIRECT parameter 1 value is 'E - Traditional IRA!E111'. It is not a valid cell/range reference." It is like this for every calculation on the "Returns" tab and the graphs don't work. I only had one account that I copy and pasted all my data from the previous spreadsheet from. I added a second account to see if that made a difference and no change.

Perhaps I'm doing something wrong?

Try toggling the Include? flag off, then on again for the account. See the README for the suggested sequence of changing sheet names. Google Sheets needs to "refresh" its formula output...

[scoothome]

The updated sheet looks great except I have data from 2016 and Google Sheets isn't robust enough to return data from that far back (as you noted in the post).

Hmm, that's a bit surprising because the demo Google Sheet was able to handle 15+ years of data. It might have something to do with your particular cash flows. Was it able to compute at least the 5 yr MWRR? I don't know if changing the IRR initial guess will work for you.

Interesting.

Everything is handled correctly for the specific account but on the "Returns" tab I get a #REF! error saying "Error
Function INDIRECT parameter 1 value is 'E - Traditional IRA!E111'. It is not a valid cell/range reference." It is like this for every calculation on the "Returns" tab and the graphs don't work. I only had one account that I copy and pasted all my data from the previous spreadsheet from. I added a second account to see if that made a difference and no change.

Perhaps I'm doing something wrong?

Try toggling the Include? flag off, then on again for the account. See the README for the suggested sequence of changing sheet names. Google Sheets needs to "refresh" its formula output...

P.S. if toggling the include flag doesn't work, try removing spaces from the sheet/account name

[BHawks87]

The updated sheet looks great except I have data from 2016 and Google Sheets isn't robust enough to return data from that far back (as you noted in the post).

Hmm, that's a bit surprising because the demo Google Sheet was able to handle 15+ years of data. It might have something to do with your particular cash flows. Was it able to compute at least the 5 yr MWRR? I don't know if changing the IRR initial guess will work for you.

Interesting.

Everything is handled correctly for the specific account but on the "Returns" tab I get a #REF! error saying "Error
Function INDIRECT parameter 1 value is 'E - Traditional IRA!E111'. It is not a valid cell/range reference." It is like this for every calculation on the "Returns" tab and the graphs don't work. I only had one account that I copy and pasted all my data from the previous spreadsheet from. I added a second account to see if that made a difference and no change.

Perhaps I'm doing something wrong?

Try toggling the Include? flag off, then on again for the account. See the README for the suggested sequence of changing sheet names. Google Sheets needs to "refresh" its formula output...

P.S. if toggling the include flag doesn't work, try removing spaces from the sheet/account name

The updated sheet looks great except I have data from 2016 and Google Sheets isn't robust enough to return data from that far back (as you noted in the post).

Hmm, that's a bit surprising because the demo Google Sheet was able to handle 15+ years of data. It might have something to do with your particular cash flows. Was it able to compute at least the 5 yr MWRR? I don't know if changing the IRR initial guess will work for you.

Interesting.

Everything is handled correctly for the specific account but on the "Returns" tab I get a #REF! error saying "Error
Function INDIRECT parameter 1 value is 'E - Traditional IRA!E111'. It is not a valid cell/range reference." It is like this for every calculation on the "Returns" tab and the graphs don't work. I only had one account that I copy and pasted all my data from the previous spreadsheet from. I added a second account to see if that made a difference and no change.

Perhaps I'm doing something wrong?

Try toggling the Include? flag off, then on again for the account. See the README for the suggested sequence of changing sheet names. Google Sheets needs to "refresh" its formula output...

Hmmm.... I tried both and it still didn't fix it. Perhaps copying and pasting a whole row of data broke something?

[g2morrow]

i had the same issue - I thought it might be the version of excel i have

[scoothome]

Hmmm.... I tried both and it still didn't fix it. Perhaps copying and pasting a whole row of data broke something?

On which sheet did you copy and paste data? Don't mess with the "MyPortfolio" sheet other than typing in the yellow-filled cells.

[scoothome]

i had the same issue - I thought it might be the version of excel i have

Yeah, for Excel you have to use a version that has the FILTER function. I use Excel 365 and didn't test for backward compatibility.

[BHawks87]

Hmmm.... I tried both and it still didn't fix it. Perhaps copying and pasting a whole row of data broke something?

On which sheet did you copy and paste data? Don't mess with the "MyPortfolio" sheet other than typing in the yellow-filled cells.

I only pasted in the Account sheet. Interestingly enough I made a fresh copy and repasted the data for an account and the return numbers are working now. The only thing not working is the 5 year return which shows an error of "Error
IRR attempted to compute the internal rate of return for a series of cash flows, but it was not able to."

[scoothome]

Hmmm.... I tried both and it still didn't fix it. Perhaps copying and pasting a whole row of data broke something?

On which sheet did you copy and paste data? Don't mess with the "MyPortfolio" sheet other than typing in the yellow-filled cells.

I only pasted in the Account sheet. Interestingly enough I made a fresh copy and repasted the data for an account and the return numbers are working now. The only thing not working is the 5 year return which shows an error of "Error
IRR attempted to compute the internal rate of return for a series of cash flows, but it was not able to."

Ah, I think I might know what's going on (?). I had put the IRR initial guess of 0.01 in cell Z16 on the account sheets, but I forgot to do that in the IRR calcs in columns AA:AM. For the 5yr, try going to cell AG76 and in the IRR portion of the formula replace:

Code: Select all

IRR(VSTACK(-(E16+W17/2),X17:X75,-W76/2+E76),)

with this:

Code: Select all

IRR(VSTACK(-(E16+W17/2),X17:X75,-W76/2+E76),0.01)

Copy and paste that cell all the way down the column. Hope that works. Note that in Excel the IRR calculation works without specifying an initial guess.

I'll work on pushing out an PIRS update in the next day or so that allows the user to set the initial guess for all IRR calculations.

[scoothome]

I've added the ability to globally specify the IRR rate guess parameter and updated the Google Sheets version to 1.1. I haven't made any changes to the Excel version.
Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.

I've updated the Google Sheets links in the original post as well.

It's a mystery to me why the Google Sheets IRR implementation is different from Excel in terms of convergence behavior. For Excel I didn't have to specify the IRR rate guess and it works fine. If anyone has insight into how to make Google Sheets IRR work as well as Excel's IRR, I'm all ears. [Update: this has been fixed in Version 2.1. There were some corrupted cells in earlier versions.]

[scoothome]

With apologies, I noticed that the Excel version had incorrect MWRR calcs for the portfolio trailing returns for specified periods (the account trailing returns were good). That has now been fixed in version 1.1. I've also added the same global IRR rate guess parameter as the Google Sheets version, although for Excel I leave the parameter blank.

Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

The Excel links in the original post have also been updated.

[Edit: I updated the Excel links to Dropbox since it seemed like Google was trying to convert them to Google Sheets, which is not what I want.]

[BHawks87]

Hmmm.... I tried both and it still didn't fix it. Perhaps copying and pasting a whole row of data broke something?

On which sheet did you copy and paste data? Don't mess with the "MyPortfolio" sheet other than typing in the yellow-filled cells.

I only pasted in the Account sheet. Interestingly enough I made a fresh copy and repasted the data for an account and the return numbers are working now. The only thing not working is the 5 year return which shows an error of "Error
IRR attempted to compute the internal rate of return for a series of cash flows, but it was not able to."

Ah, I think I might know what's going on (?). I had put the IRR initial guess of 0.01 in cell Z16 on the account sheets, but I forgot to do that in the IRR calcs in columns AA:AM. For the 5yr, try going to cell AG76 and in the IRR portion of the formula replace:

Code: Select all

IRR(VSTACK(-(E16+W17/2),X17:X75,-W76/2+E76),)

with this:

Code: Select all

IRR(VSTACK(-(E16+W17/2),X17:X75,-W76/2+E76),0.01)

Copy and paste that cell all the way down the column. Hope that works. Note that in Excel the IRR calculation works without specifying an initial guess.

I'll work on pushing out an PIRS update in the next day or so that allows the user to set the initial guess for all IRR calculations.

I'm playing around with the updated spreadsheet and I found out that the "Returns" sheet was breaking if you don't change the sheet name first and then the account name on the "MyPortfolio" second. Flipping the 1 to 0 and then back fixes it.

[scoothome]

I'm playing around with the updated spreadsheet and I found out that the "Returns" sheet was breaking if you don't change the sheet name first and then the account name on the "MyPortfolio" second. Flipping the 1 to 0 and then back fixes it.

Yep, that sounds right. I’ll make sure the instructions are clear on the README sheet.

[scoothome]

I'm playing around with the updated spreadsheet and I found out that the "Returns" sheet was breaking if you don't change the sheet name first and then the account name on the "MyPortfolio" second. Flipping the 1 to 0 and then back fixes it.

Yep, that sounds right. I’ll make sure the instructions are clear on the README sheet.

I've updated the instructions on the "MyPortfolio" sheet:

1. Set the start year and month of historical portfolio data.
2. Rename sheets (e.g., "Account1" to "His401k", "Account2" to "Her401k", etc.). Enter your data.
3. Relabel account name in column E to be the same as the corresponding sheet name (must be in same order).
4. Set Include? flag to 1 (if already 1, flip to 0, wait a second, then set to 1 again).
5. Hide unused Account sheets.

[scoothome]

Version 1.2 has been released with additional bug fixes to MWRR annual calcs and cleaned up "Returns" sheet, plus some under-the-hood changes for better traceability and maintenance. Both Google Sheets and Excel versions (and links in OP) have been updated.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

[snowday2022]

Thanks. It’s spectacular. I would rec modifying the verbiage in the README to more clearly state that the account names cannot have spaces or the sheet simply fails.

[cnrohio59]

It appears the Excel version of the PIRS is not backward compatible with Excel 2010. With no changes to a downloaded file - i.e. using the included data in Account1, there are multiple #NAME and #VAlUE errors in the Returns tab and also the Display and Calculations tabs. Toggling the 0/1 Flag does not have an effect. Have not tried the Google sheets version. I appreciate the effort to create the spreadsheet.

[scoothome]

Thanks. It’s spectacular. I would rec modifying the verbiage in the README to more clearly state that the account names cannot have spaces or the sheet simply fails.

Thank you! I've updated the live, one-account Google Sheets README and MyPortfolio sheets to make it clear to not use spaces in sheet/account names. BTW, unless there are bugs, I don't plan another release for a while.

[scoothome]

It appears the Excel version of the PIRS is not backward compatible with Excel 2010. With no changes to a downloaded file - i.e. using the included data in Account1, there are multiple #NAME and #VAlUE errors in the Returns tab and also the Display and Calculations tabs. Toggling the 0/1 Flag does not have an effect. Have not tried the Google sheets version. I appreciate the effort to create the spreadsheet.

Correct, the spreadsheet uses the FILTER function, which is only available in Excel 2021 and Excel 365. As I don't have an earlier version of Excel to test, I don't plan to make PIRS backward compatible (sorry!). If another Boglehead wants to take that on, that would be great.

[BHawks87]

Thanks. It’s spectacular. I would rec modifying the verbiage in the README to more clearly state that the account names cannot have spaces or the sheet simply fails.

Thank you! I've updated the live, one-account Google Sheets README and MyPortfolio sheets to make it clear to not use spaces in sheet/account names. BTW, unless there are bugs, I don't plan another release for a while.

Thanks a lot for this, it is great!

I do have a feature request if you do another release. Can you include the portfolio growth; in/out, gain/loss (market), year end value, and total growth calculations for the portfolio as a whole?

[scoothome]

Thanks a lot for this, it is great!

I do have a feature request if you do another release. Can you include the portfolio growth; in/out, gain/loss (market), year end value, and total growth calculations for the portfolio as a whole?

By portfolio growth, do you mean a chart of portfolio balance since the start date? In addition to a portfolio balance data series, I can add another data series on the same chart for net contributions; i.e., cumulative (monthly flow = contributions - withdrawals).

Can't really do gain/loss because that requires knowing the cost basis. Can't compute the cost basis because we aren't capturing dividends in the tool (to keep it simple).

What do you mean by total growth calculations for the portfolio as a whole? Isn't that reflected in trailing returns for life of accounts section in the left-most column (column B, labeled Portfolio)?

For year-end value, I can add a table that shows annual year-end value for overall portfolio and each account.

BTW, I'm planning to restructure the account sheets to switch columns D and E to have a contiguous block of contribution/withdrawal/balance columns, and move the missing+extra warnings from column F to column A. I'll be making other changes on the hidden sheets as well. Not sure when I'll get to this. Maybe 1 or 2 weeks (?)

[BHawks87]

Thanks a lot for this, it is great!

I do have a feature request if you do another release. Can you include the portfolio growth; in/out, gain/loss (market), year end value, and total growth calculations for the portfolio as a whole?

By portfolio growth, do you mean a chart of portfolio balance since the start date? In addition to a portfolio balance data series, I can add another data series on the same chart for net contributions; i.e., cumulative (monthly flow = contributions - withdrawals).

Can't really do gain/loss because that requires knowing the cost basis. Can't compute the cost basis because we aren't capturing dividends in the tool (to keep it simple).

What do you mean by total growth calculations for the portfolio as a whole? Isn't that reflected in trailing returns for life of accounts section in the left-most column (column B, labeled Portfolio)?

For year-end value, I can add a table that shows annual year-end value for overall portfolio and each account.

BTW, I'm planning to restructure the account sheets to switch columns D and E to have a contiguous block of contribution/withdrawal/balance columns, and move the missing+extra warnings from column F to column A. I'll be making other changes on the hidden sheets as well. Not sure when I'll get to this. Maybe 1 or 2 weeks (?)

I was referring to this section on the old sheet:


https://ibb.co/ZKrfB3g

[scoothome]

Thanks a lot for this, it is great!

I do have a feature request if you do another release. Can you include the portfolio growth; in/out, gain/loss (market), year end value, and total growth calculations for the portfolio as a whole?

By portfolio growth, do you mean a chart of portfolio balance since the start date? In addition to a portfolio balance data series, I can add another data series on the same chart for net contributions; i.e., cumulative (monthly flow = contributions - withdrawals).

Can't really do gain/loss because that requires knowing the cost basis. Can't compute the cost basis because we aren't capturing dividends in the tool (to keep it simple).

What do you mean by total growth calculations for the portfolio as a whole? Isn't that reflected in trailing returns for life of accounts section in the left-most column (column B, labeled Portfolio)?

For year-end value, I can add a table that shows annual year-end value for overall portfolio and each account.

BTW, I'm planning to restructure the account sheets to switch columns D and E to have a contiguous block of contribution/withdrawal/balance columns, and move the missing+extra warnings from column F to column A. I'll be making other changes on the hidden sheets as well. Not sure when I'll get to this. Maybe 1 or 2 weeks (?)

I was referring to this section on the old sheet:


https://ibb.co/ZKrfB3g

I don't see that in version 2.1 of longinvest's spreadsheet, which according to the wiki is the latest. Perhaps the image you showed was from an earlier version or a custom modification? In any event, I'll add the items noted in my post above and release a new version in the upcoming weeks.

[BHawks87]

Thanks a lot for this, it is great!

I do have a feature request if you do another release. Can you include the portfolio growth; in/out, gain/loss (market), year end value, and total growth calculations for the portfolio as a whole?

By portfolio growth, do you mean a chart of portfolio balance since the start date? In addition to a portfolio balance data series, I can add another data series on the same chart for net contributions; i.e., cumulative (monthly flow = contributions - withdrawals).

Can't really do gain/loss because that requires knowing the cost basis. Can't compute the cost basis because we aren't capturing dividends in the tool (to keep it simple).

What do you mean by total growth calculations for the portfolio as a whole? Isn't that reflected in trailing returns for life of accounts section in the left-most column (column B, labeled Portfolio)?

For year-end value, I can add a table that shows annual year-end value for overall portfolio and each account.

BTW, I'm planning to restructure the account sheets to switch columns D and E to have a contiguous block of contribution/withdrawal/balance columns, and move the missing+extra warnings from column F to column A. I'll be making other changes on the hidden sheets as well. Not sure when I'll get to this. Maybe 1 or 2 weeks (?)

I was referring to this section on the old sheet:


https://ibb.co/ZKrfB3g

I don't see that in version 2.1 of longinvest's spreadsheet, which according to the wiki is the latest. Perhaps the image you showed was from an earlier version or a custom modification? In any event, I'll add the items noted in my post above and release a new version in the upcoming weeks.

Hmm. Perhaps someone in the thread added that modification at some point. I can do some digging and see where it came from.

[scoothome]

Version 2.0 has been released. Links in OP have also been updated.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

Added balances sheet:

• Initial and current portfolio and account balances and cumulative cashflows.
• Chart of portfolio balance and cumulative cashflow.
• Charts of account balances and cumulative cashflows.
• Annual portfolio and account balances and cashflows.

Here are some screenshots of the new balances sheet:

Initial and current portfolio and account balances:


Chart of portfolio balance and cashflow:


Chart of account balances:


Chart of account cashflows:


Annual portfolio and account balances:


Annual portfolio and account cashflows:

[paper200]

Thanks for upgrading the Boglehead Returns (the original spreadsheet) which I have found to be very useful and have been using for the past several years.
I downoaded the version 2.0 new upgrade "Main Excel workbook" to include the MWRR functionality and I get "#Name?" error in excel 2021 - the formula "_xlfn.VSTACK" is not recognized in Excel 2021, is there a work around? Thank you.

[scoothome]

Thanks for upgrading the Boglehead Returns (the original spreadsheet) which I have found to be very useful and have been using for the past several years.
I downoaded the version 2.0 new upgrade "Main Excel workbook" to include the MWRR functionality and I get "#Name?" error in excel 2021 - the formula "_xlfn.VSTACK" is not recognized in Excel 2021, is there a work around? Thank you.

From a quick internet search, it looks like VSTACK is only available to Excel 365 users. That function is critical to how the MWRR calculations are done in PIRS. Sorry, the only workaround for now is to use the Google Sheets version (just chunk your data into approximately 15 years at a time until we can figure out how to get Google Sheets IRR function to work with larger data series). [Update: it's no longer necessary to chunk your data into 15 years at a time, Version 2.1 fixed this issue.]

[AlwaysLearningMore]

Here is a tutorial on using Google sheets to source a desktop Excel portfolio tracker. (It's the technique I used.)

https://www.youtube.com/watch?v=KfWzslm1Ua4

[scoothome]

Here is a tutorial on using Google sheets to source a desktop Excel portfolio tracker. (It's the technique I used.)

https://www.youtube.com/watch?v=KfWzslm1Ua4

That's a nice way to pull in quote data, but my use of the FILTER and VSTACK functions in PIRS is so pervasive I don't think it would be straightforward to make it backward compatible by using a technique such as shown in the video. If someone wants to give it a shot, go for it! I use Excel and the other Microsoft products often enough that I opted to pay for Office 365, but I realize that doesn't appeal to many folks. I also don't have many qualms about using Google Sheets, but I know some folks are leery of having personal financial data on Google servers.

[scoothome]

FYI, this weekend I plan to release Version 2.1, which will increase the maximum number of accounts on the charts from the current 10 to 20. Can ignore the release if you don't have more than 10 accounts. Beyond that, in the next 2-4 weeks, depending on my availability, I plan another release to add some return statistics (min, max, average, median, etc.) for the trailing periods. [Update: recommend updating to Version 2.1 as there were bug fixes].

[scoothome]

Version 2.1 has been released. Links in the OP have also been updated.

I discovered some corrupted cells, which affected the Google Sheets IRR calculation. I'm happy to report that Google Sheets can now handle the same, longer data series as the Excel DEMO version. Google Sheets still appears to need an initial rate guess, whereas Excel does not. This version also updates the charts to display up to 20 accounts (was 10).

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

[scoothome]

Ack!

Version 2.2 has been released with bug fixes for 35, 40, 45, and 50 year return calculations. The benefit of having both Google Sheets and Excel versions working with the same input is that I'm better able to cross-check. Both versions have been tested with 75 years of simulated data; IRR calcs are the same. For Google Sheets, I've set the default IRR initial rate guess to 0.005, which has worked with all data sets I've tested so far.

Links in the OP have been updated as well.
Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

[scoothome]

Ack!

Version 2.2 has been released with bug fixes for 35, 40, 45, and 50 year return calculations. The benefit of having both Google Sheets and Excel versions working with the same input is that I'm better able to cross-check. Both versions have been tested with 75 years of simulated data; IRR calcs are the same. For Google Sheets, I've set the default IRR initial rate guess to 0.005, which has worked on all data sets I've tested so far.

Links in the OP have been updated as well.
Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

Well, darn! I still have a bug in the 40 year MWRR calculation, but it's minor and I'll fix it in the next release, which will add return statistics. Let me know if there are particular stats you'd like to see that can be calculated using the data available in the spreadsheet.

[barnhowl]

Firstly, I'd like to acknowledge and thank you for the work you've put into this spreadsheet.
It clearly took a lot of time and thought to build.
You included all of the useful metrics that I was looking for.

I attempted to produce a version for Libreoffice, but porting "filter" and "vstack" is not a trivial task.
For the time being, I've resigned to using Google Sheets; there's too much utility here to pass up.

I found a couple issues that I'd like to point out.

1. When an account reaches a $0 balance there's a division by zero error. ( Balances!C11:V11 )
I refactored with:

Code: Select all

=IF(C4="","",IF(C8=0,0,C10/C8))

2. When an account reaches a $0 balance, the IRR and TWR cells on the AccountX sheet become down-filled with the most recent non-zero value.
So, there can be periods where the TWR and IRR show non zero rates, despite the starting and ending balance being $0.
This causes some of the Returns in both the trailing and annualized sections to have erroneous values.

3. The annualized TWRR and MWRR on the Returns sheet (Annual portfolio and account returns) do not annualize for accounts that did not start before the calendar year. For example: create an account and add a positive cashflow and balance in February 2023. Fill in the remaining cashflow and balances with arbitrary data. The cell Return!C61 will not populate with an annualized rate, instead it retrieves a null value from an OFFSET cell.

Lastly, since you asked if there was any stats that could be added with the available data: Effective Withdrawal Rate.
I don't need this (or even want at this stage), but I couldn't help myself from thinking it.

Thank you scoothome. I really appreciate the work you've done.

[scoothome]

Firstly, I'd like to acknowledge and thank you for the work you've put into this spreadsheet.
It clearly took a lot of time and thought to build.
You included all of the useful metrics that I was looking for.

I attempted to produce a version for Libreoffice, but porting "filter" and "vstack" is not a trivial task.
For the time being, I've resigned to using Google Sheets; there's too much utility here to pass up.

I found a couple issues that I'd like to point out.

1. When an account reaches a $0 balance there's a division by zero error. ( Balances!C11:V11 )
I refactored with:

Code: Select all

=IF(C4="","",IF(C8=0,0,C10/C8))

2. When an account reaches a $0 balance, the IRR and TWR cells on the AccountX sheet become down-filled with the most recent non-zero value.
So, there can be periods where the TWR and IRR show non zero rates, despite the starting and ending balance being $0.
This causes some of the Returns in both the trailing and annualized sections to have erroneous values.

3. The annualized TWRR and MWRR on the Returns sheet (Annual portfolio and account returns) do not annualize for accounts that did not start before the calendar year. For example: create an account and add a positive cashflow and balance in February 2023. Fill in the remaining cashflow and balances with arbitrary data. The cell Return!C61 will not populate with an annualized rate, instead it retrieves a null value from an OFFSET cell.

Lastly, since you asked if there was any stats that could be added with the available data: Effective Withdrawal Rate.
I don't need this (or even want at this stage), but I couldn't help myself from thinking it.

Thank you scoothome. I really appreciate the work you've done.

Thank you for the feedback, much appreciated! I’ll take a closer look at the issues soon (may not get to it until the weekend). For issue #3, note that at least one account has to have data for the start year and month indicated on the “MyPortfolio” sheet. For example, if you only have one account and you have data starting in February, set the start month to 2. PIRS won’t (or shouldn’t) return annualized data until the next February (one year).

[loghound]

This is an impressive piece of work for tracking your assets (and accounting for contributions & withdrawals)

One question -- on the google sheets version when I try to add contributions (column D in accounts) it pops up and says

"You’re trying to edit part of this sheet that shouldn’t be changed accidentally. Edit anyway?" -- is that expected?

-John

[scoothome]

This is an impressive piece of work for tracking your assets (and accounting for contributions & withdrawals)

One question -- on the google sheets version when I try to add contributions (column D in accounts) it pops up and says

"You’re trying to edit part of this sheet that shouldn’t be changed accidentally. Edit anyway?" -- is that expected?

-John

No, that's not expected. Currently, if you go to Data->Protected sheets and ranges, it shows two cell ranges that are excepted: C14:D997 and E13:E997. In the next version I'm going to update that exception to one range, C13:E997 because it facilitates copying data from previous versions (in old version highlight cells C13:E14, then hit ctrl-shift-down arrow to select all data, ctrl-c to copy; go to new version, position cursor in cell C13 and paste-values). I'll take a closer look at the excepted ranges on protected sheets and make sure it's working as expected.

[scoothome]

This is an impressive piece of work for tracking your assets (and accounting for contributions & withdrawals)

One question -- on the google sheets version when I try to add contributions (column D in accounts) it pops up and says

"You’re trying to edit part of this sheet that shouldn’t be changed accidentally. Edit anyway?" -- is that expected?

-John

No, that's not expected. Currently, if you go to Data->Protected sheets and ranges, it shows two cell ranges that are excepted: C14:D997 and E13:E997. In the next version I'm going to update that exception to one range, C13:E997 because it facilitates copying data from previous versions (in old version highlight cells C13:E14, then hit ctrl-shift-down arrow to select all data, ctrl-c to copy; go to new version, position cursor in cell C13 and paste-values). I'll take a closer look at the excepted ranges on protected sheets and make sure it's working as expected.

Just confirming that the protected ranges were incorrect on the Account sheets. It will be fixed in Version 2.3, which I plan to release by the end of the weekend.

[scoothome]

Firstly, I'd like to acknowledge and thank you for the work you've put into this spreadsheet.
It clearly took a lot of time and thought to build.
You included all of the useful metrics that I was looking for.

I attempted to produce a version for Libreoffice, but porting "filter" and "vstack" is not a trivial task.
For the time being, I've resigned to using Google Sheets; there's too much utility here to pass up.

I found a couple issues that I'd like to point out.

1. When an account reaches a $0 balance there's a division by zero error. ( Balances!C11:V11 )
I refactored with:

Code: Select all

=IF(C4="","",IF(C8=0,0,C10/C8))

2. When an account reaches a $0 balance, the IRR and TWR cells on the AccountX sheet become down-filled with the most recent non-zero value.
So, there can be periods where the TWR and IRR show non zero rates, despite the starting and ending balance being $0.
This causes some of the Returns in both the trailing and annualized sections to have erroneous values.

3. The annualized TWRR and MWRR on the Returns sheet (Annual portfolio and account returns) do not annualize for accounts that did not start before the calendar year. For example: create an account and add a positive cashflow and balance in February 2023. Fill in the remaining cashflow and balances with arbitrary data. The cell Return!C61 will not populate with an annualized rate, instead it retrieves a null value from an OFFSET cell.

Lastly, since you asked if there was any stats that could be added with the available data: Effective Withdrawal Rate.
I don't need this (or even want at this stage), but I couldn't help myself from thinking it.

Thank you scoothome. I really appreciate the work you've done.

Thank you for the feedback, much appreciated! I’ll take a closer look at the issues soon (may not get to it until the weekend). For issue #3, note that at least one account has to have data for the start year and month indicated on the “MyPortfolio” sheet. For example, if you only have one account and you have data starting in February, set the start month to 2. PIRS won’t (or shouldn’t) return annualized data until data is available for January of the next year (12 months of data).

For #1, I think I'd prefer to display "N/A" instead of 0:

Code: Select all

=IF(C4="","",IF(C8=0,"N/A",C10/C8))

Row 12, %Bal from Init+Cashflow, is also affected. I plan to change it to this:

Code: Select all

=IF(C4="","",IF(C11="N/A","N/A", 1-C11))

For #2, I have eliminated the down-fill from the Accounts sheets and changed the trailing return functions on the Returns sheets to grab the last non-blank cell in the relevant column. I still need to think about how to pull the right numbers for accounts that go to zero and how to display on the Returns sheet though. I might provide a link to a test sheet to get feedback before I push out the next release.

For #3, let me know if adjusting the start month didn't fix the behavior you were seeing.

Thanks for the Effective Withdrawal Rate suggestion. The next release will be a minor release (Version 2.3) to fix bugs. Hopefully, the release after that will be a major release (Version 3.0) with new features.

BTW, I noticed that your first post was on this thread. I feel honored. I lurked for a long time before I created an account...

[barnhowl]

For #1, I think I'd prefer to display "N/A" instead of 0:

For #2, I have eliminated the down-fill from the Accounts sheets and changed the trailing return functions on the Returns sheets to grab the last non-blank cell in the relevant column. I still need to think about how to pull the right numbers for accounts that go to zero and how to display on the Returns sheet though. I might provide a link to a test sheet to get feedback before I push out the next release.

For #3, let me know if adjusting the start month didn't fix the behavior you were seeing.

Thanks for the Effective Withdrawal Rate suggestion. The next release will be a minor release (Version 2.3) to fix bugs. Hopefully, the release after that will be a major release (Version 3.0) with new features.

BTW, I noticed that your first post was on this thread. I feel honored. I lurked for a long time before I created an account...

1. You're right. "N/A" is a better solution.

2. If you provide a link, then I'd happily test with it.

I'd also have to think more about this. Maybe there could be a count of contiguous $0 balances to determine which of the trailing TWRs to calculate. I'll have to play around with the sheet.

Additionally, AccountX!G doesn't calculate a return if the balance is zero. But this isn't necessary correct because there may have been greater outflows than the previous balance. For example, the previous balance could have been $1000. Then the current withdrawal could be $2000 with a balance of $0. The return obviously isn't null or nil.

3. Adjusting the start month doesn't fix the problem in my case.
I have other accounts with earlier starting dates. My accounts that currently have zero balances were not the earliest accounts (they're prefilled with 0's). So, to expand on my previous example, leaving everything else the same, add 0's for January; it still won't produce annualized returns.

BTW, I noticed that your first post was on this thread. I feel honored. I lurked for a long time before I created an account...

Thank you. Up until now, there hasn't been anything meaningful that I could contribute.
I've always appreciated that the conversations in this forum have a high substance-to-chatter ratio, keeping threads informationally dense.

[scoothome]

2. If you provide a link, then I'd happily test with it.

I'd also have to think more about this. Maybe there could be a count of contiguous $0 balances to determine which of the trailing TWRs to calculate. I'll have to play around with the sheet.

Additionally, AccountX!G doesn't calculate a return if the balance is zero. But this isn't necessary correct because there may have been greater outflows than the previous balance. For example, the previous balance could have been $1000. Then the current withdrawal could be $2000 with a balance of $0. The return obviously isn't null or nil.

3. Adjusting the start month doesn't fix the problem in my case.
I have other accounts with earlier starting dates. My accounts that currently have zero balances were not the earliest accounts (they're prefilled with 0's). So, to expand on my previous example, leaving everything else the same, add 0's for January; it still won't produce annualized returns.

I believe I have simple fixes for #2 and #3. I'm not quite ready to send out the test link yet, but here's a preview. In column G, instead of checking for zero balance, just wrap the return calculation in IFERROR. In cell G14 enter this formula and copy down the column:

Code: Select all

=IF(B14="","",IFERROR((E14-(C14-D14)/2)/(E13+(C14-D14)/2) - 1,0))

Similarly, for the MWRR calcs, wrap it with IFERROR. In cell AE14 enter this formula and copy down the column:

Code: Select all

=IF(G14="","",IFERROR(POWER(1+IRR(VSTACK(-(E13+Z14/2),-Z14/2+E14),MyPortfolio!$B$17),1)-1,0))

Make similar edits for columns AF:AT.

In combination with my other changes to remove the down-fill on the accounts sheets and to how I'm picking off the returns on the Returns sheet, I think this solves the issue.

I'll provide a test link in a couple days or so...

[barnhowl]

Code: Select all

=IF(B14="","",IFERROR((E14-(C14-D14)/2)/(E13+(C14-D14)/2) - 1,0))]

Code: Select all

=IF(G14="","",IFERROR(POWER(1+IRR(VSTACK(-(E13+Z14/2),-Z14/2+E14),MyPortfolio!$B$17),1)-1,0))

Using the above:

#3 is solved
I can confirm that these cell changes do annualize the MWRR and TWRR in Returns.

#2 is partially solved.
Most of the inaccuracy was derived from the down-filling, removing that should fix the affected calculations.
However, now Y13 (Account) is incorrect because H is populated with "1.000" for periods before the actual start date of the account.
This causes at least some problems:
1. TWRR (Trailing Life)
2. TWRR (Trailing periods)

The following could be a solution for Y13, but the FILTER logic needs a second pair of eyes. It's conditional on balance=0 AND Mult.=1.
It seemed necessary to AND those conditions since we've already seen when a return can be non-zero with a zero balance.

Code: Select all

=IF(F10="","",PRODUCT(FILTER(H14:H997, (E14:E997 <> 0) * (H14:H997 <> 1)))^(12/COUNT(FILTER(H14:H997, (E14:E997 <> 0) * (H14:H997 <> 1))))-1)

Additionally, it looks like columns I:X will need the same down-filling removal logic. They're causing the trailing returns (Returns!) to be filled in for time periods that exceed the life of the account. (5 years and 3 years are populated despite the example account only being opened 12 months)

[scoothome]

Code: Select all

=IF(F10="","",PRODUCT(FILTER(H14:H997, (E14:E997 > 0) * (H14:H997 <> 1)))^(12/COUNT(FILTER(H14:H997, (E14:E997 > 0) * (H14:H997 <> 1))))-1)

Additionally, it looks like columns I:X will need the same down-filling removal logic. They're causing the trailing returns (Returns!) to be filled in for time periods that exceed the life of the account. (5 years and 3 years are populated despite the example account only being opened 12 months)

That should work, thanks for spotting that. Columns I:X are done in my pre-release test version. I plan to provide a link tonight after a bit more validation and clean up.

[barnhowl]

#Bug Alert.
I don't have a solution yet, but I wanted to notify you before you incorporate any of this.

I edited the FILTER and COUNT for precision:

Code: Select all

=IF(F10="","",PRODUCT(FILTER(H14:H997, (E14:E997 <> 0) * (H14:H997 <> 1)))^(12/COUNT(FILTER(H14:H997, (E14:E997 <> 0) * (H14:H997 <> 1))))-1)

Code: Select all

---E14:E997 > 0
+++E14:E997 <> 0

However, it's still not correct.

The bug:
1. it excludes periods where the balance = 0 and multiple != 1
- this can occur when a withdrawal brings the account to zero, however there were gains/losses for that period.

2. it excludes periods where the balance != 0 and the multiple = 1
- this can occur when there is a balance, however there is a 0% gain

Example of bug:

Code: Select all

ID	Cont.	With.	Bal.	Mult.
1	$1000	$0	$1000	1.000
2	$0	$0	$1000	1.000
3	$0	$0	$1200	1.2000
4	$0	$0	$0	0.000

The filter needs to include ids: [2,3,4]
It currently only includes ids: [3]