How to determine my annual return rate with contributions

[Tiger85]

Hello,

I'm trying to compare my actual rate of return to my assumptions for planning. When looking at retirement, I planned for a 4% rate of return over a 30 year period with annual contributions of $31,500/year (maxing TSP and 2x IRAs).

When looking online I see lots of stuff about CAGR, IRR, and all sorts of formulas. I'm not exactly sure how to use these formulas since most seem to be based on an initial amount and an end amount and I can't find how annual contributions affect these equations.

My understanding for determining how much my investments actually returned when factoring in contributions is: (end of year balance - contributions)/starting year balance and then subtract 1 from that number to get the annual growth rate %.

For example, at the end of 2019 the combined balance for all 3 of my retirement accounts was $201,998. At the end of 2020 it was $268,390, and I had contributed $31,500 during 2020.

Do I take $268,390-$31,500 = $236,890, then divide $236,890/$201,998 = 1.17, subtract 1, which gives me a 17% return for my rate of return in 2020?

If I don't take out the contributions, the growth is 32.87%. While my total balance grew that much, I don't think that was my rate of return since $31,500 was based on contributions.

Any help on setting me straight on this would be appreciated.

I'm also tracking that I have a 30 year horizon and I'm not planning on adjusting anything based on a one year return, just curious how to compare my actual results vs. my planned assumptions.

Thank you.

[Nate7out]

Enter starting and ending balance, all inflows with dates, use XIRR formula.

Or go to the theory forum and check out longinvest's returns spreadsheet.

[livesoft]

I use the XIRR() algorithm in Excel as implemented by the freely downloaded MS Money software. Quicken does something similar. All the brokerages that I use including Vanguard that show performance numbers as well as Morningstar use the same thing. They all give the same number.

I do not believe that one can really "understand" the rate of return easily if there are contributions, withdrawals, etc. Even the calculator provided by Bogleheads that is NOT the XIRR() algorithm is probably incorrect, but is close enough for most folks.
viewtopic.php?f=10&t=150025 and
https://www.bogleheads.org/wiki/Calcula ... al_returns

You will have to look up what XIRR() does.

[Sahara]

I have always wondered how this works.

If I’m investing in the balanced index, for example, and make one contribution during the year. Will the Fund annual return vary slightly from my personal annual return?

Would I use XIRR to calculate my personal return? Or would I take the beginning balance plus the contribution and divide by the overall increase? In other words - how does/does the timing of the contribution affect the personal return?

Could someone post an example of how this works - maybe an image of the excel formula with figures?

Say the balance 12/31/2019 is $50,000. $7,000 is invested on 5/1/2020. Value on 12/31/2020 is $57,940. Is this the situation that XIRR addresses?

[Exchme]

I've not used XIRR, but sounds like it is doing what I've been DIYing.
You are trying to get a weighted average, so need to calculate the "$ x days" and divide by days to get the average $ balance.

Set up four columns: Amount of transaction, Date of transaction, Days Since Transaction, and multiply the Amount by the Days Since Transaction to get "$ x days".

Sum the dollars and sum the "$ x days".
Calculate the total elapsed time since the start.
Divide the "$ x days" by the total elapsed days to get the average balance.
Calculate your total gain as the current value minus the sum of your investments.
Divide the gain by the average balance to get your rate of return.

You can see that the "$ x days" is the key to getting the weighted average and with a little more work you also have everything you need to calculate the return for any subset of time by simply looking at the starting and ending balance, dates and "$ x days" for any period of time you want to look at.

[tonyclifton]

If you are using low cost index funds your return will be whatever the index returned (or very close). If so, then paying attention to growth in net worth might be more helpful or your year to year growth of the total portfolio as that is what you will actually have to spend. A second answer (which I know is not what you wanted) is to use the reports provided by the broker / account which will be accurate for the rate of return for the funds in that account. I gave up trying to do this across accounts (401k, Iras, 457b, taxable) and the calculation your provided in your example is also what I do and seems good enough.

[Tiger85]

Thank you everyone for the replies and the links to the spread sheets provided on the site. I'll take a look at those and do some reading on the XIRR formula as well.

Appreciate the help!

[senex]

Tiger, your method is an approximation of IRR, and may be good enough for your purposes.

IRR is conceptually simple. It pretends there is a bank account with a fixed interest rate, and computes the interest rate that would result in the same ending balance as your investment, given all your dated inflows & outflows. The math is a bit complicated, but the concept is simple.

[tominsc]

I have a suggestion. I'm not sure if it constitutes advice to use or buy something, which I may not be allowed to make. If it is, I'm sure the board admin will deep six this, but if it passes them, here's what I do.

For decades, I've used a marvelous program called Fund Manager to track all my investments all the time. All purchases, sales, exchanges, ins, outs, dividends of all types, fees, it's all covered in this great program. I track every fund I own, and I group them inside the program into as many different bundles, which the program calls portfolios, as I can think of, and it keeps track of it all.

Now then, you ask, so what, in light of my question: Well, it also calculates your internal rate of return (IRR) for any single investment or any portfolio of investments you can conjure up. That is, it's already built to do precisely what you want it to. True, to get started, you'd need to spend time inputting a lot of back year data, if you wanted it to be able to compute IRR for back year performance, but if you do the homework I assure you it'll do the math you want.

[livesoft]

^This is exactly what the free downloadable MS Money does. One can generate more reports many different ways than a spreadsheet will ever let you create.

[pshonore]

The Excel XIRR function is easy way to calculate returns. Note for periods of less than 1 year, it will annualize the return.

[livesoft]

The Excel XIRR function is easy way to calculate returns. Note for periods of less than 1 year, it will annualize the return.

Yes. This is why I use an end date for my 2021 YTD performance of 12/31/2021.

[Tiger85]

Thank you for the additional replies.

I'll try and take some time this weekend to try the suggestions out.

[Spiderv6]

My understanding for determining how much my investments actually returned when factoring in contributions is: (end of year balance - contributions)/starting year balance and then subtract 1 from that number to get the annual growth rate %.

I do exactly this, and it’s close enough. I’m adding to my 401K every two weeks and I’m not about to start adding that into an excel sheet. Far too much effort.

My method pretty much matches what Fidelity says, and I assume they are actually doing the “real” calculation.

Unless you want absolute precision, you are good to go with your original plan.

[ruralavalon]

My understanding for determining how much my investments actually returned when factoring in contributions is: (end of year balance - contributions)/starting year balance and then subtract 1 from that number to get the annual growth rate %.

I do exactly this, and it’s close enough. I’m adding to my 401K every two weeks and I’m not about to start adding that into an excel sheet. Far too much effort.

My method pretty much matches what Fidelity says, and I assume they are actually doing the “real” calculation.

Unless you want absolute precision, you are good to go with your original plan.

This is an good way to calculate for anyone who makes a contribution in a standard amount at regular intervals(e.g. $xxx every pay period), or who makes withdrawals of a fixed amount on a regular basis (e.g. RMDs every month).