ROR Calculator while Adding/Removing Principal

[nickenumbers]

Does anyone have an spreadsheet calculator or can you direct me to a calculator to perform the following:

 

 

We all know how to calculate the rate of return for a certain time period when the principal is a fixed amount.

But, what if we add to or subtract from the principal during the time period.

 

I can calculate it adhoc, but I wanted to see if someone has something elegant, or clean where you put in the starting and ending dates, and the date of the addition or withdraw and the calculator performs the rest of it.

 

I think I could build it in a spreadsheet, but I am trying to just find something open source and ready to use.

 

 

Thank you!

[John Hjorth]

nicke,

 

Do you use M$ Excell? -If so, perhaps I can help you out here.

[nickenumbers]

John,

 

Yes I do!  ;D

[coc]

The most commonly used function in Excel for this is XIRR. All you need is a column of dates and a column of principal added/subtracted. The final values in each should be today's date (or the end date) and the current value (or end value) as a negative number. XIRR will give you the annualized return for that period while taking into account the timing/amount of the principal adds and withdrawals.

 

There are certainly limitations to this approach, but it is one way. A more accurate way (in my opinion, as far as your performance) would be to compute your returns for each time period between adds/withdrawals and then compound them. But that is very tedious to do and I don't know of a function that does it automatically. You would also need the value of the position or the account at each add/withdrawal.

[wabuffo]

I prefer the Modified Dietz method (vs using IRR - which has some faulty assumptions if there are large inflows and outflows during the time period).

 

https://en.wikipedia.org/wiki/Modified_Dietz_method

 

wabuffo

 

[bookie71]

I have used a program called T-Value for years.  I don't know if it is around anymore, BUT it will do almost any time value that you want.

[John Hjorth]

The most commonly used function in Excel for this is XIRR. All you need is a column of dates and a column of principal added/subtracted. The final values in each should be today's date (or the end date) and the current value (or end value) as a negative number. XIRR will give you the annualized return for that period while taking into account the timing/amount of the principal adds and withdrawals.

 

There are certainly limitations to this approach, but it is one way. A more accurate way (in my opinion, as far as your performance) would be to compute your returns for each time period between adds/withdrawals and then compound them. But that is very tedious to do and I don't know of a function that does it automatically. You would also need the value of the position or the account at each add/withdrawal.

 

Nicke,

 

coc's way to approach this is [on a technical/calculation level] exactly the way to go. [This post took me tremendous force to post, [after coc not so long ago ridiculed me here on CoBF for being a Danish CPA] [<- [ ; - ) ]]].

 

- - - o 0 o - - -

 

Just the question implies that you're a thinker. A few years back here on CoBF I was by SharperDigaan introduced the concept of time weighted rate of return. [You'll have to lookup the posts by yourself.] Honestly, it was ringing for my ears. [Why wasn't I taught about this at the university?]

 

More food for thought : Joel Stevens [CoBF member: racemize], Austin Value Capital: "Measuring Returns"  [January 5^th 2017].

[nickenumbers]

Excellent resource CoBF.  COC, John, Bookie, Wabuffo, gold stars for the lot of you, take the rest of Friday off.

 

The XIRR, is flipping cool and easy in excel.  It is Slick enough to slide on barb wire!  It is slicker than cat shit.

 

I have been wondering how to do that, and it has been right under my big nose.  Thanks to all!

[TwoCitiesCapital]

Most people use a TWR or an IRR calculation depending on what is most appropriate.

 

If you have control over the inflows/outflows, an IRR (or the Dietz method mentioned) is more appropriate as it is a more accurate indicator of the $ made or lost over the time period as well as adjusting for the principal over that period of time. This is what I use to calculate my own personal returns since I do have full discretion over the inflows and outflows meaning that a 5% return on 100k invested gives me more credit than 5% return on 75k invested.

 

If you do not have control over the inflows/outflows, a TWR methodology (as outlined by COC) is more appropriate as it removes the impact of the fluctuating principal and basically shows what your return would have been had the principal remained flat the entire time. Most portfolio managers use this because they have no control over clients adding/removing money and it's a more true representation of the portfolio managers' ability.

 

Both of these can be accomplished in excel with a bit of familiarity with the functions and how the formulas should work.