Go Ad-Free on CAclubindia with Daily Mobile Updates. Subscribe Now!

Fiancial Modelling

Manu Kris (Market Risk Analyst) (185 Points)

06 December 2007

Hi,

Anyone who is into/interestedin financial modelling and analytics.Lets make a group and share the knowledge . Fin Modelling is a hot skill which would enable to to fetch an awesome job ....What are you waitin for....

Manu krishna

Reply
Follow
Share

10 Replies

Late CA Sampat Jain (Chartered Accountant) (4772 Points)
Replied 06 December 2007

YOU MAY FORM A GROUP IN NETPAL MODULE OF CAclub and promote it

Upgrade to CAclubindia Pro. Subscribe Now!

Dinesh (Article) (109 Points)
Replied 06 December 2007

If you are serious about any group like this than I want to join this group but you tell me what can I do for you. contact me at dmlpatel @ gmail.com

(Guest)

What exactly is financial modelling?

Manu Kris (Market Risk Analyst) (185 Points)
Replied 06 December 2007

Hi All, We all have learned finance at various stages and successfully solved problems in examinations but in the real context the CFOs or finance manager will not stit with a piece of paper and calculator to take financial decision, but they use different financial models to find solutions the models are generally made in spreadsheet (MS Excel) but We can create financial model in excel for the following purpose where in we need to key in the raw data and the model will calculate the answer within a fraction of seconds Different financial tools built in in excel are used for this. It’s a combination of formulas and functions with different complexity level. We can create financial model for the following purpose. Investment decision using capital budgeting Portfolio evaluation Option Pricing (Using Black and Scholes method and other methods) Bond Valuevation. Etc I am not an expert in this field but I am learning it continuously and wanted to master this art with discussions and sharing of knowledge. Most of the Chartered Accountants are unaware of this tool since all are concerned only about accounting standards, tax, etc etc and I strongly believe that the knowledge of Financial modeling will definitely be an added advantage.

Sahana (Service) (81 Points)
Replied 06 December 2007

Hello, as rightly pointed out, capital structuring and funding decisions are important areas other than taxation etc. Any discussion on these matters would definitely be very useful to all of us. Please let me know how to effectively use the built-in tools available in Excel for this - how to arrive at IRR for certain projected cash flows, etc. Thanks.

Upgrade to CAclubindia Pro. Subscribe Now!

Manu Kris (Market Risk Analyst) (185 Points)
Replied 06 December 2007

Hi raghu, I will send u some notes if u can send ur mail id

Manu Kris (Market Risk Analyst) (185 Points)
Replied 06 December 2007

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.

Sahana (Service) (81 Points)
Replied 12 December 2007

Hi Manu, My mail ID is raghu1958 @ gmail.com I am sure your information & notes will be very useful. Thanks Raghunathan

(Guest)

Originally posted by :Manu
"	Hi raghu, I will send u some notes if u can send ur mail id	"

Hi Raghu! Can u pls provide me with some notes on Financial Modelling?

piyush (Manager) (22 Points)
Replied 30 October 2009

Originally posted by :Manu Kris

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.