Question

Problem a) Use Excel to find the rate of return for the following cash flow. (5 points) 12,000 1 2 3 4 5 6 7 8 Year 100 100 100 300 300 300 300 4000

Answer 1

First we find net cash flow for each of the year

Then we can use IRR function in MS Excel to get rate of return.

Rate of return= 11.6749%

Syntax and net cash flows are shpwn in following sceen shot.

Bookl - Microsoft Excel View Acrobat File Home Insert Page Layout Formulas Data Review - FX Calibri r * AA = = = E General 9 % Paste BI A - , ... - 11 U P Font fx Conditional Format Cell Formatting as Table Styles Styles Insert ** Delete Format Cells Sort & Find & 2° Filter Select Editing Alignment E Number Clipboard F18 A B C D E F G H I J K L M N O 2 Year, n Cash Net Cash Outflow, Cash Flow, Co Inflow, ci Co+Ci 0 -4000 0 -4000 11 0 0 0 -300 0 -300 -300 -300 -300 -300 -300 0 -300 -100 0 -100 -100 -100 8 -100 12000 11900 13 Rate of return can be found by using IRR function in Excel 14 Rate of Return= 11.6749% =IRR(D3:011) 15 16 17 18 19 Sheet1 Sheet2 Sheet3 » Ready 201 100% 09:24 1x ill 25-03-2020