Page 1 sur 2






EDHEC M1



























FINAL ASSIGNMENT
START DATE: 12/10/2022 12:00 (Paris time)
DUE DATE: 20/10/2022 13:00 (Paris time)

Professor/Lecturer: Pierre Teller

INSTRUCTIONS:
• APPENDIX(CES) PROVIDED : N/A
• SafeAssign : THIS ASSIGNMENT IS PROTECTED AGAINST PLAGIARISM


• Assignment Start date and hour:
• Assignment Type : Individual assignment
• Assignment File(s) Type and Number : XLSX
• Submission Attempts : 2 attempts max
• Instructions for students :





633 - ADVANCED EXCEL ONLINE
Page 2 sur 2


General instructions:
In all exercises, respecting the best practices discussed during the live sessions will be important.
It will always be considered during the grading. An important part of the job is to find a suitable
way to present your results, not only to compute the right value somewhere.
Don’t forget the presentation rules, the use of references, the ‘auto-check’ formulas, etc.
You must upload a single Excel file. In this file, you are free to organize the work as you prefer
(number of worksheets, color scheme, etc.)

Part 1: Amortization table
Draw the complete amortization table of a loan. The initial amount is 20 000€, and the annual
interest rate is 3,5%. Interests are calculated at the end of each period, and the payments are
monthly. The loan must be repaid after 3 years.
Make sure you do not forget some relevant information. (Check some examples on internet first?)
This table is a good opportunity to show that adding ‘auto-check’ formulas makes the data more
reliable.
Find a way to show that the loan is really repaid at the end of the period on the last row of the
table.

Part 2: what-if analysis for a loan
Consider another loan. The principle is 100 000€.
If the annual interest rate is 3% and the number of periods is 4 years, what is the monthly
repayment?
Then, draw a table to show what would be the monthly repayment for all interest rates between
3% and 8% (entire values only), and for a number of years between 4 and 10 (again, entire values
only).
You can use Excel data analysis tools to build this table, but it can also be built manually.
In this table, find a way to “highlight” the payments lower than 1500€.
Assuming the number of periods is 5 years, what is the maximum interest rate to have monthly
payments lower than 1900€?

Part 3: future value of an investment
Consider the following investment. You make an initial deposit on a bank account of 5000€, and
then another deposit of 1000€ at the end of each year. The interest rate on this account is 4%.
What is the value of this investment after 8 years?
At the end of which period the value of this investment is greater than 10000€?

Part 4: NPV and payback period
We need to compare 3 different investments (A, B and C). All have a lifetime of 6 years.
For each investment, we have the initial amount invested (outflows) and all subsequent cash flows
(inflows, received at the end of each period), see the table below. The constant discount rate is
8% over the period.
Draw a table with the net present value of each investment, at the end of each period (from
year N+1 to year N+6). Which investment is better?
Find a way to show in this table what is the payback period of each investment.

Initial amounts:

Project A: 120000€
Project B: 155000€
Project C: 69000€
Cash flows:
A B C
N+1 (€) 19500 25200 11100
N+2 (€) 22800 27400 16100
N+3 (€) 23000 33800 20200
N+4 (€) 27700 31400 25600
N+5 (€) 31500 42800 21000
N+6 (€) 38600 49400 22400



学霸联盟