Calculating total return on Investments when you've withdrawn more than you've invested?

[Cowboy13]

I have previous posted a template that automatically calculates investments accounts returns Investment Returns (Monthly, Annual, Total, XIRR)!.

One of the things I calculate for each account is its total return; which seems pretty simple:
[end (or current) value] / [total invested] -1

However, I have a few accounts where I have withdrawn more than the total amount I invested into the account (possible because returns have been positive). In this scenario, the formula above does not work because the “total invested” is negative.

When this happens in my Investment Returns template, I say the return is ∞. But, this bugs me so I have been looking for a better answer. I have done a Google search on this but haven’t found anything helpful.

Would love to hear your feedback. If there is an article that explains, please point me to it.

I realize this is not really a Tiller topic, more like Seeking Alpha; but thought I would try Tiller first.

Thanks.

[ScottC]

This is a “primitive” solution that doesn’t consider the dates of the contributions or withdrawals.

Hope it helps.

ScottC

[ScottC]

The XIRR function in Excel or Sheets calculates the return based on an irregular series of cash flows. This is a simple example:

Annualized Return	27.63%	=XIRR(B4:B9, A4:A9)
Date	Amounts	Notes
1/1/2022	-$100,000.00	Starting amount as negative
6/1/2022	-$10,000.00	Adds (-), Withdrawals (+)
8/1/2022	$7,000.00
1/5/2023	-$15,000.00
3/15/2023	$8,000.00
7/1/2024	$200,000.00

[Cowboy13]

@ScottC

Thanks. I use XIRR to calculate average annual return since opening for each portfolio. I think this is the best metric to determine return of a portfolio.

I was trying to figure how to calculate TOTAL return for the portfolio. I did a bunch of playing around and have decided to keep the total return as ∞ if you have withdrawn more than your total deposits.

Thanks again.

[ScottC]

Wouldn’t the XIRR formula still be an accurate way to calculate the Annualized Return even if you’ve withdrawn more than your initial investment thanks to positive returns on your initial investment?

In the example below over a 2 1/2 yr period, investments have been $110,000. Withdrawals have been $128,000 and the final balance is $30,000. XIRR annualized return = 40.67%

I noticed that you “bolded” TOTAL return (as opposed to the annualized return).

Wouldn’t that be the Amount of (Withdrawals + Current Portfolio Value) / Total Investment?

Apologies if I’m still misunderstanding.

ScottC

[Cowboy13]

As you mention, XIRR is annualized and that one is easy.

For Total return, there are a few cases where what is the most logical formula (at least to me), [end (or current) value] / [total invested] -1, does not work. Here are 4 scenarios, and I included your suggested formula…

What is the actual TOTAL return of these 4 scenarios? I hope I’m missing something obvious.

[ScottC]

If I’m understanding your top left calc, you’re saying you lost money even though you took out more than you invested ($50,000) and still have $5,000 in the account.

Using your top left example … I’d say your total investment was $50,000 turned into $60,000 over a 5 month period. The total amount you got back from your investment is $55,000 (withdrawals) + $5,000 (final value) = $60,000 or $10,000 more than you invested.

So, your Total Return would be 20% ($10,000 gain on $50,000 investment). The annualized return (IRR) would be 172%

It seems like you’re counting your withdrawals as additional investments rather than returns. Is this because you’re counting them as you would dividends that you’re reinvesting? If so, I don’t think that approach gives you a realistic way of looking at the return on your investment of $50,000.

As I see it, if your withdrawals + ending balance are more than your initial investment + adds, you’re Total Return has to be positive.

[Cowboy13]

OK. I see what you’re saying.

Going back to your Total Return Formula (withdrawals + current value)/Total Investment, your “Total Investment” is the sum of all the deposits, correct? I have been hung up on Total Investment = Net Invested (Deposits - Withdrawals). Using this, here are the updated tables, where the total return makes a lot more sense!

Thanks!

[ScottC]

Rows 12 and 29 in your examples show various amounts for “Total Invested” because you’re considering withdrawals as “investments.”

That’s not how I’d look at it. In my view, the withdrawals are not investments; they are returns.

I’d consider the Total Invested for all of your example as $50,000 since you made only one initial investment and no additional contributions.

[Cowboy13]

Thanks for the help. I understand now.

[anderh1234]

All of the above is interesting. Wouldn’t it be easier and more informative to just do the following (on a spread sheet of course):
A = P1*(1+i)^(T-T1) + P2*(1+i)^(T-T2) + P3*(1+i)^(T-T3)+…etc. Where “A” is the value of investment per the broker at date “T”, and “P1” is the principle (+) or withdrawal (-) at date “T1”, wherein (T-T1) is the period of time between the two, and etc for P2, P3…Then recursively iterate on “i” until the the equation balances (or use “Goal Seek” in Excel). “i” would be the CAGR.

Henry Anderson

[anderh1234]

I find those methods to be not-understandable. I use the equation:
Ao = sum of Pj * (1 + j)^(delta T). Where Ao is the value of your account at some current date “To”, “Pj” is your investment at a previous date “j” where “Pj” can be a deposit or a withdrawal, “j” is the CAGR (compounded annual growth rate), and delta “T” is the time in years between “To” and “Tj”, i.e. delta “T”= “To” minus “Tj” in years. Then set-up your spreadsheet and iterate on “j” until the value you have calculated from the sum of your investments equals the value of your account presented by your brokerage.

Hank