Hello guys this is Wajahat Ahmad and I’m here to give you a tutorial on Excel. So in this video I will tell you how you can calculate Return on Investment (ROI) in Excel. So what is Return on investment (ROI) is a calculation that shows how an investment or asset has performed over a certain period. It expresses gain or loss in percentage terms. so what’s the formula for calculating ROI is simple, as you can see on my screen it’s (Current Value – Beginning Value) / Beginning Value = ROI you can get ROI The current value can be one of two things whatever amount the investment was sold for (its realized value) or whatever the investment is worth at the present time (like the market price of a stock). So let’s move on to the calculation Unfortunately, when you pile all of the calculations into a formula, you can’t easily see what numbers go where, or what numbers are user inputs or hard coded.
The way to set this up in Excel is to have all the data in one table like just I have did here have date here and then break out the calculations line by line so I have So I have figures for 4 years like 2017, 2018, 2019, 2020 so I have current value of 100 and ending value which you also called like current value you also called it like outflows and inflows are here inflows which are 115 for 2018, 125 for 2019 and 82 for 2020 so I have made a column for return on investment so first of all I’ll go to this column it’s E8 in the 2018th year so I’ll put the calculation here first of all you have to put is equal to sign for the formula then add bracket starting bracket and then I’ll add this value it’s sorry it’s a E6 okay and E6 and then what we do is we subtract it from the current value so we subtract it from first of all we put dollar sign then it’s a D ROW and then again dollar sign and then we put line number it’s 5 and then close the bracket and divide it by same thing dollar D dollar five and then press enter and here we go I have it has calculated by value for it’s 15% what you will do for others just don’t put that we already did the formula just click here and drag it to here and it will automatically calculate the formulas for all of the years so as you can see that, first of all I will convert it into the percentage so you can easily here we go so it’s 15% for 2018 25% for 2019 and -18% for 2020 so that was simple easy but still if you if you know the formula it will not be so hard for you to calculate so that’s it for today if you have any questions please feel free to ask and don’t forget to SUBSCRIBE
Read More: How to Calculate ROI