asked on

Excel or maths formula

How to find the returns of multiple, irregular inflows & outflows over a short period, lets say in 4 months?

I don't want to use the XIRR as it gives the compounded annualized return, which will be misleading. I want to calculate the returns for the duration of the investment only.

For eg., please refer to the attached excel sheet which lists out 5 transactions of Buy & Sell over 4 months period.

Thanks and regards

R
Question.xlsx

Shums Faruk

Hi Rahul,

First delete blank rows in between, then you can use either one of below formula in G4 and drag down:

=IF($C4="Buy",$G3-($D4*$E4),IF($C4="Sell",($D4*$E4)+$G3,""))

Open in new window

=SUMIF($C$4:$C4,"Sell",$F$4:$F4)-SUMIF($C$4:$C4,"Buy",$F$4:$F4)

Open in new window

Rahul_Chauhan_Stock_Calculation.xlsx

Mahima Gupta

Hi,

Agreed with the Shums given code, and to delete the blanks rows in Excel, give a 2 min read to this Excel Tips & Trick Article.

Thanks

Rahul Chauhan

ASKER

Thanks Shums & Mahima for your help.

The formula gets me the total profit. Whereas, I want to find out the rate of return for the profit earned over the duration of the investment. Let me explain my question:

1) The Profit of 15000 was made on the total investment of 39500 over the entire duration of 111 days (from 1st July to 20th Oct. i.e. first investment date to last sale date)

2) But entire 39500 was not invested for 111 days. Rather, out of 39500, 20000 on 1st July was invested for 111 days. Similarly, 9000 on 6th July was invested for 106 days & 10500 on 4th Aug was invested for 77 days.

2) Also, the entire profit of 15000 was not over 111 days. Rather, out of 15000, 5000 was made in first 15 days of the investment duration of 111 days and the remaining 10000 was made on the 111 day.

So, now if I want to calculate my rate of return of the profit made for the duration of my investment, how should I calculate that?

Pl. be advised that I don't want to calculate the rate of return using XIRR because it gives the profit return as 581.389%, which is misleading since XIRR is annualizing the rate of return, whereas, I want to calculate the rate of return for the duration of investment only.

Even if there is no ready to use excel formula to calculate it, but if I can know how to mathematically calculate it using what approach, that is also be fine with me.

Thanks and regards

R

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shums Faruk

It would be better if you can attach a workbook with expected result sheet as well.

Rahul Chauhan

ASKER

Thanks Shums for the reply. My apologies for the unintended miscommunication.

I am looking for the overall rate of return over 111 days instead of individual rate of return for each transaction.

Thanks and regards,

R
Rahul_Chauhan_Stock_Calculation_v2..xlsx

Shums Faruk

Solution provided for initial requirement. For additional request, I would request to raise a new question.