What will be Rate of Interest (ROI) ?

[RushatiINDIA]

Could anyone please help me to calculate the Rate of Interest of the
Following?

Problem 1:

Intestment Rs. 4384 for Next 20 years. ie Total Investment Rs. 87680

Maturity at the end of 25th year.

Maturity Value = Rs. 265500

I have calculated the Rate of Interest (ROI) as follows:

=ROUND(((265500-87680)/87680*(365/(25*365)))*100,2) ie 8.11%

Problem 2:

Intestment Rs. 6450 for Next 20 years. ie Total Investment Rs. 129000

Maturity at the end of 20th year.

Maturity Received Such as follows:

at the end of 5th year = Rs. 20000

at the end of 10th year = Rs. 20000

at the end of 15th year = Rs. 20000

at the end of 20th year = Rs. 40000

plus interest = Rs. 84000

Total : 184000

I have calculated the Rate of Interest (ROI) as follows:

=ROUND(((184000-129000)/129000*(365/(20*365)))*100,2) ie 2.13%

Is the both procedure of calculation right?

May I know the correct function for solution?

Thanks in advance.

Regards

 

[Martin Fishlock]

Hi Rushati:

The easiest way to do it is you do the classic cashflow worksheet where you
put all the information done and then do the calculations.

Then you can see if the figures look resonable and you have something to
discuss.

So you start off with your balances and then the cash injections and then
calculate the interest and carry it forward.

Good luck.

 

[joeu2004]

Could anyone please help me to calculate the Rate of Interest of the
Following?

There are many ways to express and compute Return on Investment
(ROI). It is difficult to say which is correct for your purpose
without knowing what your textbook asks for or what you are currently
studying in the chapter.

Problem 1:
Intestment Rs. 4384 for Next 20 years. ie Total Investment Rs. 87680
Maturity at the end of 25th year.
Maturity Value = Rs. 265500

I have calculated the Rate of Interest (ROI) as follows:
=ROUND(((265500-87680)/87680*(365/(25*365)))*100,2) ie 8.11%

That calculates the nominal (uncompounded) simple ROI per year,
although you certainly chose the most difficult way to express it.
Why write 365/(25*365) when 1/25 is not only equivalent, but also
probably expresses your intent more clear ("per year")? I would write
simply:

=(265500/(20*4384) - 1) / 25

and format the cell as Percentage with 2 decimal places.

The problem with the nominal simple ROI is that is usually not very
useful. If you told me that your ROI was 8.11% per year, I would
expect that that is the compound rate. In other words, the future
value after 25 years would be:

=fv(8.11%, 25, 0, -20*4384)

Of course, that is wrong. So another way to compute the (compound)
ROI is:

=(265500/(20*4384))^(1/25) - 1

Alternatively and equivalently, you could write RATE(25, 0, -20*4384,
265500).

Those formulas yield 4.53%. FV(4.53%, 25, 0, -20*4384) does yield
265500.

However, both methods suffer from the same over-simplification,
namely: they assume that all negative cash flows (20*4384) occur at
Time 0 and all positive cash flows (265500) occur at Time N.

If you want to take "time value" (i.e. the timing of cash flows) into
account, you want to compute the internal rate of return (IRR). Excel
does not make that easy because there is no way to group consecutive
equal cash flows. The most flexible formulation is to put the cash
flows into individual cells, then use the IRR() function. Thus,
A1:A20 might contain -4384 each, A21:A25 might contain 0 each, and A26
might contain 265500. Then IRR(A1:A26) yields 6.90%.

Problem 2:
Intestment Rs. 6450 for Next 20 years. ie Total Investment Rs. 129000
Maturity at the end of 20th year.
Maturity Received Such as follows:
at the end of 5th year = Rs. 20000
at the end of 10th year = Rs. 20000
at the end of 15th year = Rs. 20000
at the end of 20th year = Rs. 40000
plus interest = Rs. 84000
Total : 184000

I have calculated the Rate of Interest (ROI) as follows:
=ROUND(((184000-129000)/129000*(365/(20*365)))*100,2) ie 2.13%

Again, that is the nominal annual ROI. It is non-compounding, and it
fails to take "time value" into account. A problem expressed with the
above complexity is probably looking for the IRR as its solution. (My
answer is 5.33%.)

 

[Fred Smith]

Your formulas do not calculate the compounded rate of return.

The function which will solve your first problem is Rate. The second is XIRR.

Post back if you need help with either.