I am looking over some data regarding contributions to my retirement portfolio, and the total balance, over time. I want to turn this data into rates of return that I can use to make sure my investing strategy isn't deviating significantly from the relevant benchmarks in any unexpected way. The data is in the following format, although the actual numbers are made up. (As you can see, the dates are not regular; just whenever I felt like checking my balances.)
9/30/14 - Total portfolio balance: $50,000
10/15/14 - New contributions: $10,000; balance: $61,000
1/12/15 - Balance: $63,000
3/1/15 - New contributions: $7,000; balance: $69,000
And so on . . .
I am trying to look up or recall how to manipulate this data into a format that XIRR or some other function in Google Sheets will turn into the number(s) I'm looking for. It's a bit maddening, because I've done these kinds of calculations and used the IRR/XIRR and similar functions before (albeit in Excel). But it's been a long time, and the knowledge isn't coming back to me like I thought it would, even after going over a few how-to's I found online.
Remind Me How to Calculate Return? (Google Sheets)
-
- Posts: 82
- Joined: Tue Apr 17, 2007 10:04 am
-
- Posts: 1613
- Joined: Wed May 20, 2020 6:36 am
Re: Remind Me How to Calculate Return? (Google Sheets)
You need two columns in your data.
The first column, call it 'A', is transaction dates.
The second column, call it 'B' is cash flow; negative values are "in to" the security (purchases from external cash), positive values are "out from" the security (dividends taken in cash, capital gains distributions received in cash, share sales, etc).
To get the IRR, for the last row, enter the current date and the liquidation value as if you were to take it all out today (a positive value, since it's a hypothetical "sale").
Then the function is =XIRR(B1:B10,A1:A10), if you have 10 rows in your table, for example.
The first column, call it 'A', is transaction dates.
The second column, call it 'B' is cash flow; negative values are "in to" the security (purchases from external cash), positive values are "out from" the security (dividends taken in cash, capital gains distributions received in cash, share sales, etc).
To get the IRR, for the last row, enter the current date and the liquidation value as if you were to take it all out today (a positive value, since it's a hypothetical "sale").
Then the function is =XIRR(B1:B10,A1:A10), if you have 10 rows in your table, for example.
“Now shall I walk or shall I ride? |
'Ride,' Pleasure said; |
'Walk,' Joy replied.” |
|
― W.H. Davies
-
- Posts: 82
- Joined: Tue Apr 17, 2007 10:04 am
Re: Remind Me How to Calculate Return? (Google Sheets)
Thanks - I'll give that a shot.
-
- Posts: 3286
- Joined: Tue Oct 02, 2018 6:10 pm
- Location: NYC
Re: Remind Me How to Calculate Return? (Google Sheets)
I am math and spreadsheet challenged I will never learn.Balanthalus wrote: ↑Fri Jun 18, 2021 4:34 pm I am looking over some data regarding contributions to my retirement portfolio, and the total balance, over time. I want to turn this data into rates of return that I can use to make sure my investing strategy isn't deviating significantly from the relevant benchmarks in any unexpected way. The data is in the following format, although the actual numbers are made up. (As you can see, the dates are not regular; just whenever I felt like checking my balances.)
9/30/14 - Total portfolio balance: $50,000
10/15/14 - New contributions: $10,000; balance: $61,000
1/12/15 - Balance: $63,000
3/1/15 - New contributions: $7,000; balance: $69,000
And so on . . .
I am trying to look up or recall how to manipulate this data into a format that XIRR or some other function in Google Sheets will turn into the number(s) I'm looking for. It's a bit maddening, because I've done these kinds of calculations and used the IRR/XIRR and similar functions before (albeit in Excel). But it's been a long time, and the knowledge isn't coming back to me like I thought it would, even after going over a few how-to's I found online.
I use Morningstar portfolio manager
just copy your transactions for that particular security and it self calculates. All you need is a free membership.
https://www.morningstar.com/portfolio-manager/my-view
step by step video on how to use
https://www.youtube.com/watch?v=wqo3sbSbFjg&t=44s