Remind Me How to Calculate Return? (Google Sheets)

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
Topic Author
Balanthalus
Posts: 82
Joined: Tue Apr 17, 2007 10:04 am

Remind Me How to Calculate Return? (Google Sheets)

Post by Balanthalus »

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.
backpacker61
Posts: 1613
Joined: Wed May 20, 2020 6:36 am

Re: Remind Me How to Calculate Return? (Google Sheets)

Post by backpacker61 »

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.
“Now shall I walk or shall I ride? | 'Ride,' Pleasure said; | 'Walk,' Joy replied.” | | ― W.H. Davies
Topic Author
Balanthalus
Posts: 82
Joined: Tue Apr 17, 2007 10:04 am

Re: Remind Me How to Calculate Return? (Google Sheets)

Post by Balanthalus »

Thanks - I'll give that a shot.
retire2022
Posts: 3286
Joined: Tue Oct 02, 2018 6:10 pm
Location: NYC

Re: Remind Me How to Calculate Return? (Google Sheets)

Post by retire2022 »

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 am math and spreadsheet challenged I will never learn.

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
Post Reply