Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: calculating interest rate on fluctuating account

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Thanks,
    Jack Kleinert

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Private e-mail sent with workbook attached.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •