Question

In: Finance

A project requires an initial cash outlay of $90,000 and is expected to generate $35 000 at the end of year 1, $43 000 at the end of year 2 and $40 000 at the end of year 3

Instruction: For the following questions, with the help of the formulas learnt in topic 4 and 5, you are to use excel and calculate:

1. A project requires an initial cash outlay of $90,000 and is expected to generate $35 000 at the end of year 1, $43 000 at the end of year 2 and $40 000 at the end of year 3, at which time the project will terminate. Calculate the IRR of the project in excel. (2marks)

2. For the cash flows in the previous problem, calculate the NPV of the project at a required return of 9% using excel? (2 marks)

3. Construct an amortization schedule of a loan of $10,000 to be repaid over 10 years with a 10- ordinary annuity payment at effective rate of interest of 10% per year in excel. (3 marks)

4. Construct a sinking fund schedule for the loan of $15,000 to be repaid over 5 years with a 10- ordinary annuity payment at effective rate of interest of 8% per year in excel. (3 marks)

Solutions

Expert Solution

1. IRR of the project

We enter the following values in excel with initial outlay as a negative sign.

Year Cash Flows
Yr 0 -90000
Yr 1 35000
Yr 2 43000
Yr 3 40000

The IRR can be calculated as

=IRR(B3:B6)

where B3:B6 denotes the range of cash flows throught the life of the project

Year Cash Flows
Yr 0 -90000
Yr 1 35000
Yr 2 43000
Yr 3 40000
IRR 14.51%

Answer in table

2. NPV OF THE PROJECT

To find the NPV of the project we enter similarly as the above example with return as percentage in a seperate cell

Year Cash Flows
Yr 0 -90000
Yr 1 35000
Yr 2 43000
Yr 3 40000
Rate 9%
NPV Rs. 8,430.89

where

=NPV(B7,B3:B6)

returns the NPV of the project .

B7 is the rate percentage , and B3:B6 is the range of cash flows

Answer in table

4.Loan Armotization:

In order to find the loan armotization , we need to calculate the yearly equal anuity payments to armotize the loan first :

so we use excel formula :

=($E$5*10%)/(1-(1+10%)^-10)

where $E$5 has the initail principal 10% is rate and 10at last is the number of years as per the formula

Formulas used :

Principal = Enter the principal amount

Interest = =E6*10%

payment = =($E$5*10%)/(1-(1+10%)^-10)

remaining = =E6+F6-G6

Use the fill handle to drag down and complete the armotization schedule

4. Sinking Fund:

The sinking fund can be calulated as follows

So we get the following :

Interest rate 8%
Number of payments per year 2
Time 5
Amount 15000
Periodic contribution 1249.364

Related Solutions

An investment project requires an initial outlay of $100,000, and is expected to generate annual cash...
An investment project requires an initial outlay of $100,000, and is expected to generate annual cash inflows of $28,000 for the next 5 years. The cost of capital is 12 percent. Determine the internal rate of return for the project (to the nearest tenth of one percent). a. 12.0% b. 12.6% c. 3.6% d. 12.4%
Project S requires an initial outlay at t = 0 of $16,000, and its expected cash...
Project S requires an initial outlay at t = 0 of $16,000, and its expected cash flows would be $6,000 per year for 5 years. Mutually exclusive Project L requires an initial outlay at t = 0 of $40,500, and its expected cash flows would be $12,100 per year for 5 years. If both projects have a WACC of 16%, which project would you recommend? Select the correct answer. a. Project L, since the NPVL > NPVS. b. Both Projects...
Project S requires an initial outlay at t = 0 of $18,000, and its expected cash...
Project S requires an initial outlay at t = 0 of $18,000, and its expected cash flows would be $4,000 per year for 5 years. Mutually exclusive Project L requires an initial outlay at t = 0 of $29,000, and its expected cash flows would be $14,900 per year for 5 years. If both projects have a WACC of 13%, which project would you recommend? Select the correct answer. a. Both Projects S and L, since both projects have IRR's...
Project S requires an initial outlay at t = 0 of $11,000, and its expected cash...
Project S requires an initial outlay at t = 0 of $11,000, and its expected cash flows would be $6,500 per year for 5 years. Mutually exclusive Project L requires an initial outlay at t = 0 of $46,500, and its expected cash flows would be $14,500 per year for 5 years. If both projects have a WACC of 13%, which project would you recommend? Select the correct answer. a. Project S, since the NPVS > NPVL. b. Both Projects...
Project S requires an initial outlay at t = 0 of $13,000, and its expected cash...
Project S requires an initial outlay at t = 0 of $13,000, and its expected cash flows would be $7,000 per year for 5 years. Mutually exclusive Project L requires an initial outlay at t = 0 of $34,500, and its expected cash flows would be $14,200 per year for 5 years. If both projects have a WACC of 14%, which project would you recommend?
Project S requires an initial outlay at t = 0 of $13,000, and its expected cash...
Project S requires an initial outlay at t = 0 of $13,000, and its expected cash flows would be $5,000 per year for 5 years. Mutually exclusive Project L requires an initial outlay at t = 0 of $47,500, and its expected cash flows would be $13,800 per year for 5 years. If both projects have a WACC of 14%, which project would you recommend? Select the correct answer. a. Project L, since the NPVL > NPVS. b. Project S,...
Project S requires an initial outlay at t = 0 of $11,000, and its expected cash...
Project S requires an initial outlay at t = 0 of $11,000, and its expected cash flows would be $7,000 per year for 5 years. Mutually exclusive Project L requires an initial outlay at t = 0 of $26,000, and its expected cash flows would be $10,800 per year for 5 years. If both projects have a WACC of 14%, which project would you recommend? Select the correct answer. a. Neither Project S nor L, since each project's NPV <...
a. Project L requires an initial outlay at t = 0 of $64,022, its expected cash...
a. Project L requires an initial outlay at t = 0 of $64,022, its expected cash inflows are $11,000 per year for 9 years, and its WACC is 9%. What is the project's IRR? Round your answer to two decimal places. b.Project L requires an initial outlay at t = 0 of $75,000, its expected cash inflows are $11,000 per year for 9 years, and its WACC is 10%. What is the project's MIRR? Do not round intermediate calculations. Round...
Project S requires an initial outlay at t = 0 of $20,000, and its expected cash...
Project S requires an initial outlay at t = 0 of $20,000, and its expected cash flows would be $4,500 per year for 5 years. Mutually exclusive Project L requires an initial outlay at t = 0 of $46,000, and its expected cash flows would be $13,450 per year for 5 years. If both projects have a WACC of 12%, which project would you recommend? Select the correct answer. a. Project S, since the NPVS > NPVL. b. Both Projects...
Project S requires an initial outlay at t = 0 of $11,000, and its expected cash...
Project S requires an initial outlay at t = 0 of $11,000, and its expected cash flows would be $5,000 per year for 5 years. Mutually exclusive Project L requires an initial outlay at t = 0 of $26,500, and its expected cash flows would be $11,950 per year for 5 years. If both projects have a WACC of 12%, which project would you recommend? Select the correct answer: a. Neither Project S nor L, since each project's NPV <...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT