Fiancial Modelling

Hi all,

I quite often get assignments where i have to prepare project reports. Quite comfortable with it. Would like to reaserch more and expertise it. would appreciate sharing of work. My personal Id is pmthakkar @ ymail.com.

Would like to be part of any forum or group created for the purpose.

one academic Q: Is IRR of the project affected by the divident payout ratio?

IRR

Internal rate of return
Next, you will add internal rate of return (IRR) functionality to your model. The IRR is the rate expected for investing in a project given the initial investment, and the operating cash flows. Your textbook will probably describe how to estimate the IRR using a trial-and-error method that provides an approximation of the result. However, given the initial investment outlay, the operating cash flows, you can calculate the IRR using a worksheet function. It will provide a more precise result and be a lot quicker to compute. Note that the IRR function requires at least one negative outflow and inflows that occur at regular periods such as monthly, quarterly or annually. Whilst IRR is popular and easy to understand, it pays to become familiar with its shortcomings. If you base a selection purely on rate of return, consider the following. Would you prefer to receive 12% on $50 or 10% on $100? Therein is the danger of rate based evaluation methods.
In developing the IRR model, you will continue to evaluate the EcoCo project from the preceding section. Assuming in this case that the cost of capital is unknown, you will compute the IRR. As most of the hard work is done, this is very easily accomplished as follows.
Preparing the Model
1. 1. Change the Initial Investment at period 0 back to -55,432.90. Enter zero as the rate and also enter 0 into the risk adjusted cell as well. IRR does not use Rate; indeed that is what is determined for you – the rate of return for the project.
2. 2. In order to serve you better when evaluating IRR, you may wish to edit the NPV formula. This is not essential, however, so you could skip this and move on to “Entering the IRR function”.
3. 3. In cell D16 delete the content then click the Paste Function button and select the IF function from the Logical selection.
4. 4. Enter the arguments and reference the cells as in the Figure 12.4 example below.

Figure 12.4: IF statement dialog box with data entered
5. 5. Click OK and the formula will be: =IF(B2>0,D15+D4,"***"). The result should not show at all now. In the cell a row of asterisk should appear “***”(see Figure 12.6). The IF statement is checking B2 and if it is greater than 0 will perform the calculation; otherwise it displays asterisks. The reason for the IF statement in the formula is to check to see if a rate is entered. In IRR estimations, the rate should be set to 0 to save confusion.
Entering the IRR Function
1. 1. In cell A16 enter “IRR =”. Format and right align it (see Figure 12.6).
2. 2. Go across to cell B16 and click on the Paste Function button. From the Financial functions select IRR.
3. 3. The IRR dialog box will pop up. Reference the Values box to the D4:D14 range as demonstrated in Figure 12.5. Note the initial outlay must be a negative figure, followed by a stream of cash flows. Naturally, most of these would normally be positive.

Figure 12.5: IRR dialog box with cell references
4. 4. Click OK and the formula will be entered as =IRR(D4:D14). Note that D4:D14 is technically treated as an array in the IRR function, which reduces the flexibility of variations you can make in the formula. This is the reason you have had to move to entering negative inflows.
5. 5. The result will be shown as 8.486% (see Figure 12.6).
Project Evaluation Risk Adj. RADR
Rate 0.00% 0.00% 0.00%
Period Outflows Inflows Cash Flows
Initial Investment 0 -55,432.9 - 55,432.9
1 19,600.0 19,600.0
2 15,400.0 15,400.0
3 -5,000.0 14,000.0 9,000.0
4 10,800.0 10,800.0
5 7,800.0 7,800.0
6 4,800.0 4,800.0
7 2,300.0 2,300.0
8 -
9 -
10 -
PV of CFs $69,700.00
IRR = 8.486% NPV = ***
Figure 12.6: IRR model showing the result
Thus, the internal rate of return is 8.486%. As the IRR is greater than EcoCo’s cost of capital of 8.03%, the project is worthy of further consideration within the framework of the investment strategy of the firm. That completes your IRR model.