I'm having a hard time coming up with a proper spreadsheet formula to calculate rate of return (positive or negative) on investments within an IRA.

Hypothetical example:

Start with principal of $1,000,000 on January 1 of any given year.

Take a distribution of $50,000 on May 20 of that year.

On December 31, the account value is $1,200,000, meaning there were total earnings in the account of $250,000 for the one year period, some of which presumably happened prior to May 20 and some of which happened after May 20. I could backtrack and get an exact account value on the date of distribution so I could easily calculate my rate of return from January 1 to May 20 separately from my rate of return from May 20 to December 31, but I'm not particularly interested in those numbers. How do I calculate total overall performance for the entire year without breaking down the time period into smaller individual increments?

(METHOD A) Do I deduct the $50,000 distribution off the top and assume I started with $950,000 that earned $250,000? In this case, the earnings would be $250,000 / $950,000 = return of 26.32%

(METHOD B) If I assume the account value would have been worth $1,250,000 on December 31 had the distribution not occurred on May 20, the earnings would be $250,000 / $1,000,000 = return of 25.00%.

Alternatively, if there are losses during the year instead of gains, let's look at a different hypothetical example.

Start with principal of $1,000,000 on January 1 of any given year

Take a distribution of $50,000 on May 20 of that year

On December 31, the account value is $800,000, meaning there were losses in the account of $150,000 for the one year period.

(METHOD A) Do I deduct the $50,000 distribution off the top and assume that $950,000 was -15.79%, accounting for the $150,000 loss?

(METHOD B) Or do I assume the account value would have been worth $850,000 on December 31 had the distribution not occurred on May 20, the losses would be $150,000 / $1,000,000 = return of -15.00%?

Method A looks better than Method B when there are gains (up 26.32% as opposed to up 25%).

But Method B looks better when there are losses (down 15% as opposed to down 15.79%).

Is there a method C I'm failing to understand? How do I create a formula that's accurate, precise, and consistent? I could do the individual time period calculations as described above (1/1-5/20 and 5/20-12/31) and then create a weighted average of the two time periods based on number of days in each, but I'm looking for a quicker method with fewer steps. Specifically, I'm looking for something that does not require me to have account valuation information on the date of the distribution. I'm also looking to create a formula that will work across multiple years (meaning multiple distributions).

Thanks for visiting FatWallet.com. Join for free to remove this ad.

DTASFAB

Senior Member - 2K

posted: Oct. 20, 2016 @ 3:03p

Figured out the best way to measure it is by how long the distributed portion was invested during the time in question. If you take the distribution earlier in the year, use method A, because the $950,000 figure at the beginning of the year is closer to the truth. If you take the distribution later in the year, use method B, because the true account value near December 31 is more accurately close to $1,250,000.

If you take the distribution on June 30, split the difference and add back $25,000, so you'd roughly have $250,000 in earnings on $975,000.

It's just a quick way to roughly estimate the more complicated calculation that would require weighted averages by the exact number of days before and after the distribution.

This is also true if there are losses rather than gains. If you take the distribution early in the year and then the market goes down the tubes, your losses within the IRA will be less than if you left the distribution for later in the year. If the market goes down 15% for the year and you start with $1,000,000 invested on January 1, you're going to end up with $850,000 on December 31 when it comes time to take the $50,000 distribution at the end of the year, leaving you with $800,000 in the IRA. But if you take the distribution early in the year, before you've incurred the 15% loss, you'd start with $950,000 on January 1, which would dwindle to $807,500 after dropping in value 15% by the end of the year. This would result in a pre-tax savings of $7,500.

TL;DR - bottom line is the distribution date matters and there's no way around it.

mjohnson

Happy Member

posted: Oct. 20, 2016 @ 4:50p

DTASFAB said: Is there a method C I'm failing to understand? How do I create a formula that's accurate, precise, and consistent? I could do the individual time period calculations as described above (1/1-5/20 and 5/20-12/31) and then create a weighted average of the two time periods based on number of days in each, but I'm looking for a quicker method with fewer steps. Specifically, I'm looking for something that does not require me to have account valuation information on the date of the distribution. I'm also looking to create a formula that will work across multiple years (meaning multiple distributions).

There is no way around this. You need to value the account on the date of distributions to have an accurate, precise, and consistent performance calculation.

mjohnson

Happy Member

posted: Oct. 20, 2016 @ 4:50p

Duplicate

DrDubious

Senior Member

posted: Oct. 21, 2016 @ 1:53a

I'm a little vague on the details but I think the XIRR function in excel should accomplish what you are looking for. I believe you will need to know values on starting and ending dates, and the size and date of any inflows or outflows but not account values on the dates of ins and outs.

If you set up your inputs correctly it should spit out the rate of return that if consistently applied over the entire term would get you to where you actually ended up.

DTASFAB

Senior Member - 2K

posted: Oct. 22, 2016 @ 11:24a

DrDubious said: I'm a little vague on the details but I think the XIRR function in excel should accomplish what you are looking for. I believe you will need to know values on starting and ending dates, and the size and date of any inflows or outflows but not account values on the dates of ins and outs.

If you set up your inputs correctly it should spit out the rate of return that if consistently applied over the entire term would get you to where you actually ended up. Thanks I'll look into this.

psychtobe

Senior Member - 3K

posted: Oct. 22, 2016 @ 3:20p

XIRR does exactly this, and I do not think you need to know the value of the account on the day of distributions. All you need is:

beginning balance ending balance for each contribution or withdrawal, the amount and the date.

I've used XIRR for years to track the true rate of return of my investments, net of contributions.

Disclaimer: By providing links to other sites, FatWallet.com does not guarantee, approve or endorse the information or products available at these sites, nor does a link indicate any association with or endorsement by the linked site to FatWallet.com.

Members of our community may attach files to a post in accordance with the User Agreement. FatWallet is not responsible for the content, accuracy, completeness or validity of any information contained in any attached file. Files have *not* been scanned for viruses. Be especially wary of Excel files which may contain malicious content.

Shopping
Earn Cash Back while you shop - just 3 simple steps.

1. Sign Up so we know who to pay! (It's FREE.)

2. Shop through FatWallet for deals from your favorite stores. Your online purchases earn Cash Back that builds in your FatWallet account.

3. Get Paid by requesting a payment via check or PayPal.

FatWallet coupons help you save more when shopping online. Use our Coupons Search to browse coupons and offers from thousands of stores, gathered into one convenient location.

Forums
As part of our FatWallet Community, you can share deals with almost a million shoppers in our forums. Forum content is generated by consumers for consumers. Share deals, money-saving tips, and more. It's FREE, fun, and addicting.

Support
Our customer experience team is here around the clock - real people ready to assist.