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