HW12: Amortization Table, Net Present Value, Internal Rate of Return Getting started 1) Open Excel. Rename Sheet1: NPV-IRR. Rename Sheet2: Amortization. Delete Sheet3. 2) Save as username12.xls. In NPV-IRR 3) Wrap column A. Make all columns about 100 pixels wide. 4) Type the following: a) In A1: After 5 Years b) In A2: After 4 Years c) In A3: After 2 Years d) In A8: End of Year e) In A9: Beginning of Year f) In A10: Beginning of Year, with 1000 Loss g) In A11: End of Year, with 1000 Loss To help answer questions in this sheet, you may wish to insert data in columns D and/or E. 5) I estimate that it will cost me 200000 EURO to open a new business. I expect to net the following EURO during the first five years: 40000, 50000, 75000, 80000, and 100000. In B1: What is my internal rate of return after five years? In B2: After 4 years? In B3: After 2 years? Make answers percent, with two decimals. (ANSWERS: 18.08%, 7.65%, and -39.01%) 6) I plan to invest 50000 EURO at the end of a year. I estimate that I will make the following amounts of EURO at the end of each year following: 5000, 10000, 15000, 20000, and 25000. Assuming a discount rate of 11%, in B8, what is its net present value after five years? In B9, do the same problem, but assume the BEGINNING of the year. In B10, do the same problem, but assume the BEGINNING of the year, with a LOSS of 1000 EURO. In B11, do the same problem, but assume the END of the year, with a LOSS of 1000 EURO. Change to the correct currency, with 2 decimals. (ANSWERS: 1440.99, 1599.50, 1064.86, 959.33) 7) Using the information from B1, in B13 find the net present value of the internal rate of return. Change to the correct currency, with 2 decimals. (ANSWER: 0) 8) SAVE. In Amortization 9) Make all columns about 85 pixels wide. Then, copy the following table, starting in A1: What is the What is Cumulative your unpaid Interest paid principle so far (from this month? 1st month to (Formula) this month)? -20000 What is the Cumulative Principle paid so far (from 1st month to this month)?
What is How much How much your Total Interest is Principle is Payment Month paid this paid this this month? # month? month? (Function)
What is your Total Payment this month? (Formula)
What is your unpaid principle this month? (Function)
10)
1 Then, copy the following table, starting in K1:
Cumulative Interest YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 TOTAL
Cumulative Principle
Cumulative Paid
11) 12)
13) 14) 15) 16) 17) 18) 19) 20) 21) 22) 23)
Make the whole sheet Arial, 10. Then, complete the Amortization Table based on your borrowing €20000, at 8% compounded monthly, paid in five years: a) Starting in A3, use the fill-handle to copy from “1” to the last month. b) Starting in B3, find how much is paid toward interest in the first month, and copy for the other months. c) Starting in C3, find how much is paid toward principle in the first month, and copy for the other months. HINT: B6=-133.33; C6= -272.19 d) Starting in D3, use a function to find the Total Payment for the first month, and copy for the other months. e) Starting in E3, use a formula to find the Total Payment for first month, and copy for the other months. HINT: Answers in columns D and E should be the same. f) Starting in F3, use a function to find unpaid principle balance for first month, and copy for the other months. g) Starting in G3, use a formula and the value in G2 to find unpaid principle balance for first month, and copy for the other months. HINT: F14 and G14= -19,727.81. h) Starting in H3, find cumulative interest paid, from first month to the month in that row. Copy down. i) Starting in I3, find cumulative principle paid, from first month to the month in that row. Copy down. In L3:N8, find the cumulative interest paid, cumulative principle paid, and cumulative paid for each of the 5 years and total. In B63:E63, find totals. Wrap row 64. In B64:E64, write what the totals in row 63 are. In H64 and I64, write what the total are in H62 and I62. In K10, write: Total Interest Paid in Year3, Year4, Year5 In K11, use the answers in L3 to N8 to answer that question. In K12, write: Total Principle Paid in Year2, Year3, and Year4 In K13, use the answers in L3 to N8 to answer that question. In K14, write: Total Paid in Year1 and Year2 In K14, use the answers in L3 to N8 answer that question. Change all currency values to Euro, with two decimals.
Finishing 24) To both sheets: add gridlines; add a header with your name and surname, sheet name, and automatically updated date. 25) In Amortization, AutoFit columns A. Make column J about 20 pixels wide. Make your margins: Top, Bottom, Left, right: 0.5; Header, Footer: 0.25. Make Amortization fit on two sheets. If necessary, put a page break before column K. 26) Compress the Excel file as username12.zip or username12.rar. Send zip/rar file only to erin@bilkent.edu.tr before class on Thursday, May 10.