Hi need help for assignment and i attached copy of my work till now.

[Pawanbabbu]

spread-sheet model of your business costs over a 6 year period. For the purpose of modelling, assume a discount rate of 10 per cent. Your business model should include the following analysis to ascertain the financial viability of the project:
1. Payback period
2. Net present value (NPV)
3. Internal rate of return (IRR), and
4. Profitability index
To obtain the payback period you would first need to calculate the cumulative [COLOR=#009900 !important]net cash[/COLOR] flow. Use the NPV and IRR functions provided in[COLOR=#009900 !important]Microsoft[/COLOR] Excel. You may need to search the Internet for information if you are not familiar with how to use these functions. Interpret your results.
Investment should only be in millions. The result of profit percentage should be remain between 1 to 2%.
Really need help.

year0	Year1	Year2	Year3	Year4	Year5	Year6
-$(10.00)	-$(5.00)	-$(2.00)	$1	$6	$11	$13.00
		Net	Cashflow	Cum cashflow
		Year0	-$(10.00)	-$(10.00)
		Year1`	-$5.00	-$5.00
		Year2	-$3.00	-$3.00
		Year3	$1.00	$1.00
		Year4	$6.00	$6.00
		Year5	$11.00	$11.00
		Year6	$13.00	$13.00

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

Discount rate(R)=10.0%
year0	Year1	Year2	Year3	Year4	Year5	Year6
-$(10.00)	-$(5.00)	-$(8.00)	$1	$6	$11	$13.00
1	0.909091	0.826446	0.751315	0.683013	0.620921	0.564474
-$10.00	-$4.55	-$6.61	$0.75	$4.10	$6.83	$7.34

<colgroup><col span="8"></colgroup><tbody>
</tbody>

 

[StephenCrump]

Your assignment is asking you to use the NPV and IRR functions, and says you may need to do some research if you are not familiar with these. Have you done this yet, and if so, what results do you have?

People will be happy to help you if you are having trouble understanding these functions, probably less inclined to do your homework for you.

 

[Pawanbabbu]

Your assignment is asking you to use the NPV and IRR functions, and says you may need to do some research if you are not familiar with these. Have you done this yet, and if so, what results do you have?

People will be happy to help you if you are having trouble understanding these functions, probably less inclined to do your homework for you.

I completed this one.

	NPV
Discount rate	10%
Initial Investment	-$1,000,000.00
0 Year Return	$0.00
1nd Year		$5,000.00
2nd		$10,000.00
3rd		$20,000.00
4th		$25,000.00
5th		$25,000.00
6th		$15,000.00
NPV		-$852,147.36
IRR		-$ 0.35
Profablilty Index	$56,943.55

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[StephenCrump]

OK, you've applied NPV and IRR correctly (nearly). Your IRR of -$0.35 should be formatted as a %, i.e. -35% p.a. (which might give you a clue about how financially viable this project is looking)?

With the NPV and IRR functions, the periods between cashflow amounts need to be equal, so your table should look like this, i.e. without the extra row for time = 0:

Excel 2010

	A	B
1	Time	Amount
2	0	-$1,000,000
3	1	$5,000.00
4	2	$10,000.00
5	3	$20,000.00
6	4	$25,000.00
7	5	$25,000.00
8	6	$15,000.00

<tbody>
</tbody>

​

The NPV of your cashflows at times 1, 2 .. 6 should be calculated on B3:B8, giving $68,902 at 10% pa discount rate. Given the initial investment of $1m, the NPV for the project is $68,902 - $1m = -$931,098, i.e. your investor is looking to lose $931,098 (discounted present value) on the deal. And IRR should be calculated on B2:B8 giving -40% p.a.

Do these numbers make sense to you? Are they consistent with an investment of $1m which has returned only $100,000, before allowance for discounting?

How have you calculated your profitability index? It should be the discounted value of your cash flows in years 1 to 6, divided by the initial investment of $1m. Given you have recouped only 10% of your initial investment, before allowing for discounting, wouldn't you expect this measure to be less than 10%?