In: Finance
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)
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 |