calculating interest rate on fluctuating account

[kleinert]

have an investment account from which I make varying annual withdrawals, and to which I
made varying annual contributions over the years. I would like to calculate my rate of
return over the life of the account, and during selected periods. What excel function should I use, or is there another formula I should use?

Thanks,
Jack Kleinert

[Jay Petrulis]

On 2002-04-29 05:08, kleinert wrote:
have an investment account from which I make varying annual withdrawals, and to which I
made varying annual contributions over the years. I would like to calculate my rate of
return over the life of the account, and during selected periods. What excel function should I use, or is there another formula I should use?

Thanks,
Jack Kleinert

Hi Jack,

Using Excel functions, go with XIRR, MIRR, or IRR. Be sure that you understand the differences between them, and pay careful consideration to non-normal cashflows (multiple sign changes) which give multiple IRRs.

For a nice extension of the above, the following thread gives the link to David Hager's XMIRR function.

http://groups.google.com/groups?hl=e...40TKMSFTNGXA11

If you want interperiod returns, the Modified Dietz method can be used, and if you require a more sophisticated technique, use the BAI Iteration method (the one AIMR recommends for performance reporting).

Then, geometrically link the returns using GEOMEAN on the resulting array.

Example: Period returns in A1:A12, array enter
{=GEOMEAN(100+A1:A12)-100} or
{=GEOMEAN(1+A1:A12)-1}
depending on the way you structure the return results.

For info on the Modified Dietz and BAI Iteration, see my post at
http://groups.google.com/groups?hl=e...40tkmsftngxa12

Check the entire thread for background info.

The IRR, XIRR, MIRR functions can be used in non-"since inception" periods by using the market value (switch the sign) as the beginning "cashflow" at the beginning of the period.

Finally, and most simply, you can use a total period return [(end-begin)/begin] - 1 and adjust by a CPI deflator, for instance, to get a "real" rate of return very simply. (I have an inflation indexed IRR file, but that is very, very involved just like the BAI technique is.)

This is a lot, so please let us know if this is helpful or if we can help further.

Bye,
Jay

[kleinert]

Jay,
Thanks for your reply. Unfortunately it was over my head. I don't know which of the three functions to use, even after reading their definitions.
To simplify my problem, hypothetically:
1-I invest 100 every month for 10 years, then 350 every month for 5 years, then 200 for 3 years. The last 7 years I invest nothing.
2-During year 18 I withdraw 4000, the next year 8000, then 3000, then 12000, etc.
3-During all these years the money is invested and growing.
4-Assuming I have a monthly date column for all these transactions, separate columns for the contributions and the withdrawals, what excel formula do I use to compute my investment return at various times during these 25 years? (I tried using IRR, but the resulting percentages don't seem accurate at face examination.)

Thanks,
Jack Kleinert

[Jay Petrulis]

On 2002-04-29 11:11, kleinert wrote:
Jay,
Thanks for your reply. Unfortunately it was over my head. I don't know which of the three functions to use, even after reading their definitions.
To simplify my problem, hypothetically:
1-I invest 100 every month for 10 years, then 350 every month for 5 years, then 200 for 3 years. The last 7 years I invest nothing.
2-During year 18 I withdraw 4000, the next year 8000, then 3000, then 12000, etc.
3-During all these years the money is invested and growing.
4-Assuming I have a monthly date column for all these transactions, separate columns for the contributions and the withdrawals, what excel formula do I use to compute my investment return at various times during these 25 years? (I tried using IRR, but the resulting percentages don't seem accurate at face examination.)

Thanks,
Jack Kleinert

Hi Jack,

IRR and MIRR will not work because they assume equal intervals between cashflows. But, since you have a monthly date column, they may work if you don't skip months (they will be filled with 0's).

XIRR will work for you, I believe, so should XMIRR, although you don't have different project/re-investment rates, it seems.

If you need periodic returns, you will need to have a beginning portfolio value and an ending portfolio value (pseudo cashflows).

That is, you may need to know the value of the "grown" portfolio after the 4th year, for instance.

If you'd like, please send me your file and I will have a look.

john.petrulis@notes.ntrs.com

Please do *NOT* send if there is any sensitive information which you do not revealed, unless you can disguise or remove it.

Bye,
Jay

[RET79]

Time weighted rate of returns would perhaps be best as it ignores the effect of cashflows.

How often did you know the value of your fund, every month, every year?

I can come up with something for you if you like.

See you soon,

RET79

[kleinert]

I know the value of my account at the end of each month and at the end of each year.

Thanks,
Jack Kleinert

[RET79]

One way, probably not the most effective or elegant, assuming you know the value of yoru account every month is to use the time weighted rate of return which is:

(1+i)^T = Ft1/(Ft0+Ct0)*Ft2/(Ft1+Ct1)*...FtT/(Ftn+Ctn)

where Ft1, Ft2.... is your fund value at time t1, t2...

and

Cto, Ct1, ... is yoru cash flow at time to,t1.

This can be set up using 3/4 coumns of a spreadsheet for every time interval which in your case is probably months.

RET79

[Jay Petrulis]

Hi,

Please be aware of the signs if you are using the TWRR methods.

Cashflows *out* of your pocket are negative for the IRR variants, but for the time-weighted, consider them as cashflows *in* your portfolio.

Also, with few exceptions, contributions, withdrawals, fees and expenses are the only cashflows that should count.

For example, a sale of a stock position reduces the value of your assets but increases the value of the cash in the portfolio by the same amount. It is only when you withdraw the cash does it leave your portfolio.

Bye,
Jay

[Jay Petrulis]

Private e-mail sent with workbook attached.