Question

please show me how to do this using excel and its functions

A proposed overseas expansion has the following cash flows:

            Year                                                    Cash Flow

                0                                                           -$200

        1 50

    2    60

          3 70

                   4                                                    200

Calculate the payback, the internal rate of return, the NPV, the profitability index, and the modified IRR at a cost of capital of 10%.

Answer 1

Payback period is the time it takes for a project to return the initial capital, Payback period is different than discounted payback in which cash flows are discounted first with required rate of return/discount rate, in payback period method cash flows are not discounted.

Year	Cash Inflow	Cumulative cash flow
1	50	50
2	60	110
3	70	180
4	200	380

Payback period will fall between year 3 and 4

Payback period will be,

= 3+ (200 - 180)/200

= 3+ (20)/ 200

= 3+ 0.1

= 3.1 years

-----------------------------------------------------------------------------------------------------------------

NPV is the difference between present value of all cash inflow and initial cost of asset or project.

All the future cash flows are discounted using discounting rate or required rate and the sum of all the discounted cash flows will be subtracted with initial cost.

NPV = PV of future cash inflow - initial cost

Pls refer below table for NPV calculation,

Year	Cash Inflow	PV factor	PV of cash flow
1	50	0.909091	45.45454545
2	60	0.826446	49.58677686
3	70	0.751315	52.59203606
4	200	0.683013	136.6026911
		Total	284.2360495
		Initial cost	200
		NPV	84.23604945

-----------------------------------------------------------------------------------------------------------------

PI = PV of cash inflow/Initial cash out flow

      = 284.24/200

     = 1.44

-----------------------------------------------------------------------------------------------------------------

MIRR is improved version of IRR, While calculating MIRR it is assumed that cash inflow resulting from the asset or project will not be kept idle however it will be invested further to generate more return

MIRR could be calculated using formula, however it is much easier to use excel.

Year	Cash Inflow
0	-200
1	50
2	60
3	70
4	200
MIRR	20.10%
Formula	=MIRR(AL16:AL26,H2,H2)

-----------------------------------------------------------------------------------------------------------------

Hope this answer your query.

Feel free to comment if you need further assistance. J