Investment Exit plan - Decision Making Via XIRR and XNPV

Miss K bought company G share about four years ago. Over years, she received dividend from company G semi annually. However, share price has no significant increase. Its range from RM6 to RM8. She has no idea on the calculation of the return of investment. Indecisively, she does not know how to make the move from it.

a) what is the CAGR of the share G investment? loss and gain?
b) does the invest worth to be kept if we take finance cost as the risk free rate of 4%p.a.?
c) does the invest worth to be kept if our expected rate is targeted as 15%p.a.?
d) if she wants to realize the loss/gain by taking finance cost as the risk free rate of 4%p.a. CAGR, what is the selling price for share G?

Solution:

Step 1: Tabulate the net cashflow in time series.
Step 2: Take the market value base on today price - make it liquidate by today (+ve sign)

a) what is the CAGR of the share G investment? loss and gain? 

Step 3:  Function XIRR is used to calculate CAGR. 1.31% gain p.a. is obtained, but it is lower than the risk free rate 4% p.a. The return 1.31% is bench-marked with 4% e.g. MGS or simply make it as 4% p.a. for fix deposit return.

b) does the invest worth to be kept if we take finance cost as the risk free rate of 4%p.a.?

Step 4: Function XNPV is used to make the investment feasibility. Positive NPV leads to make the decision to accept the project while negative NPV leads to reject the project. For the 4% p.a. benchmarking, the project has the net present value of -RM1041. Thus, we reject the project. So we should not keep the investment. We should realize the loss.

c) does the invest worth to be kept if our expected rate is targeted as 15%p.a.?

Step 5: For the 15% p.a. benchmarking, the project has the net present value of -RM3997. Thus, we reject the project. So we should not keep the investment. We should realize the loss. 15% p.a. is my favorite CAGR investment return. It is used to gauge my investment performance.

For those who had gone through the retirement and children education planning, desired return from the planning is used to benchmark the investment share G performance. For instance, Miss K desired an investment of CAGR 6% p.a., then she should use 6% to replace 15%.

d) if she wants to realize the loss/gain by taking finance cost as the risk free rate of 4%p.a. CAGR, what is the selling price for share G?
Step 6: Excel Solver is used to calculate liquidation price by fixing CAGR at 4% p.a. Thus, she should sell the company G at least higher than RM7.86 without suffer any loss.

See, as simple as ABC.