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

#### Pawanbabbu

##### New Member
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>

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### StephenCrump

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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
AB
1TimeAmount
20-\$1,000,000
31\$5,000.00
42\$10,000.00
53\$20,000.00
64\$25,000.00
75\$25,000.00
86\$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%?

Replies
12
Views
994
Replies
2
Views
507
Replies
1
Views
191
Replies
0
Views
222
Replies
8
Views
390

Threads
1,191,227
Messages
5,985,381
Members
439,961
Latest member
drose1105

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back