Week 4 Final Assignment >> Excel Skills for Business: Advanced
1. This week’s assignment will have you reproducing some of the calculations and schedules you studied in the videos. Follow the instructions carefully, write your formulas in the green cells and answer the questions as you go. Do not insert or delete any rows or columns because then the references in the instructions may not align with your worksheet.
Download and open the assignment workbook.
Look at the Calcs worksheet. Work through the sheet from top to bottom, answering the questions as you go.
Section 1 – DatesIn this section, we will focus on using formulas to take a given input date and return an end of quarter date. This is a useful skill when building models that are based on quarterly time periods.
Cells F5:T5 contain dates from the years 2010 to 2020. Your task is to write a formula in cells F6:T6 that will return the end-of-quarter date for the calendar quarter in which the date from row 5 falls. For example, if cell F5 contained 11-Jan-2018, the correct end-of-quarter date would be 31-Mar-2018. Space for helper cells is provided if you need it, but it can certainly be done neatly without helper cells. Use the EOMONTH function, so that F6 will have the formula =EOMONTH(F5,[??]). You will need to write an expression in place of [??] that returns either 0, 1 or 2 depending on what is an appropriate value to get to the end of the quarter.
When you are done, submit the value of the Check Sum from cell D6.
161053
2. In cells F18:T18, write a formula that returns the previous end of quarter date for each of the dates in cells F5:T5. For example, if cell F5 contained 11-Jan-2018, F18 should give the value 31-Dec-2017. When you are done, submit the value of the Check Sum from cell D18.
160876
3. Section 2 – Loan SchedulesIn this section, we are going to build a loan schedule that has some flexibility for changing input values. To begin, we are going to model a 3-year loan with constant monthly scheduled payments. The outline of the schedule has been provided at cells A25:H62. The loan amount is for $50,000 and the interest rate is 9.75% per annum. Interest is calculated each month, with the monthly rate of 1/12th of the annual rate applied to the monthly opening balance. Scheduled Payments are made on the last day of each month. The loan must be paid down to a balance of $0 at the end of 3 years.
To begin, write a formula in cell F24 using the PMT function to calculate the monthly scheduled payment amount necessary for this loan. Assume for now that Additional Payments will be zero. What is the monthly payment amount?
- -5,052.39
- -1,607.50
- -1,668.11
- -1,594.54
4. Next, complete the modelling of the green cells C27:H62, using the scheduled payment you calculated at cell F24 in each of cells F27:F62. Assume all Additional Payment cells in column G are zero and assume all Drawdown cells in column D after the provided 50,000 are zero. The Closing Balance formula has been filled in for you. If you have done this correctly, the Closing Balance at cell H62 should be zero. What is the closing balance as of 20 Feb 2019? Submit your answer without a dollar sign or thousands separator, rounded to two decimal places.
HINT: The Interest formula in column E should be the Opening Balance multiplied by the interest rate divided by 12, and the Opening Balance should be the previous period’s Closing Balance.
36236.18
5. What is the total interest paid on this loan? (This should be the value in cell E64). Submit your answer without a dollar sign or thousands separator, rounded to two decimal places.
7869.89
6. Now, let’s assume that we had some extra funds available at the end of 2018, and decided to make an additional payment of $10,000 on 20 Jan 2019. Enter -10000 at cell G36, including the negative sign. Because of this additional payment, our loan is now paid off before the end of period 36. To compensate, we are going to adjust the scheduled payment amount that is used for all months AFTER January 2019, so that the closing balance still becomes zero on 20 March 2021.
In cell F37, write a new formula using the PMT function to calculate a new constant payment amount for periods 11 to 36, and apply this amount to cells F37:F62. What is this new monthly payment amount? Submit your answer including the negative sign, rounded to two decimal places. As a hint, the answer is between -$1100 and -$1200.
-1179.27
7. What is the new total interest amount shown at cell E64, after we make the additional payment in January 2019 and after we adjust the scheduled payments for February 2019 to March 2021? Submit your answer without a dollar sign, rounded to two decimal places.
6736.06
8. Section 3 – Net Present Values and Internal Rates of ReturnFor this section, look at rows 67:107. The schedule at rows 72:99 shows a 24-month loan from the point of view of the bank making the loan. The interest column has not been completed. For this loan, the bank lends (invests) $10,000 at the end of period 0 (20 March 2018), and receives payments of $450.00 on the 20th of each month for the next 24 months.
In cell E101, use the IRR function to find the internal rate of return to the bank for the cashflows in cells E73:E97. In cell E102 express this answer as an annual rate (calculate the annual rate as 12 times the monthly rate). What is the annual internal rate of return?
- 7.50%
- 7.75%
- 8.00%
- 8.25%
9. If the bank was to charge interest on this loan using an interest rate equal to the calculated internal rate of return, the closing balance should come out to be $0.00. Try it and see, by filling in the Interest cells at D74:D97 using the monthly internal rate of return as the interest rate, and calculating the interest amount as the Opening Balance for the month multiplied by this monthly rate.
Now let’s assume that the bank is a little more precise in their interest calculations. Use the Annualised IRR result in cell E102 as the annual interest rate, but now calculate the interest each month as:
(Opening Balance) * (Annual Interest Rate) * (Days in Period) / 365
The Days in Period can be calculated as the Period Payment Date minus the previous Period Payment Date. Update your formula in cells D74:D97 to use this new approach. The Closing Balance after period 24 should now be an amount between -2.00 and -3.00 dollars. What is the final Closing Balance? Submit your answer without a dollar sign, rounded to two decimal places.
-2.54
10. The last thing we will do in this section is to calculate the Net Present Value of this loan to the bank. Ignore the interest calculations and just focus on the cashflows to the bank presented at cells E73:E97.
The bank is able to borrow funds on the wholesale lending market at a cost of 4.00% per annum. If the bank is able to make loans to customers that have an IRR of above 4.00% per annum (such as this one), then that should be a positive NPV situation for the bank.
In cell E105, calculate the NPV as at the end of Period 0 of the cashflows in cells E73:E97 at a 4.00% per annum discount rate. Submit your answer without a dollar sign, rounded to two decimal places.
HINT: Remember to convert the discount rate to a monthly amount, since the periods between cashflows are monthly periods.
362.71
11. Section 4 – DepreciationIn this section, we are going to calculate a depreciation schedule using the Double Declining Balance method, but we are going to calculate it two different ways.
The first way will use the DDB function we learned about, and the second way will recreate the same schedule using a first-principles approach. The reason why a first-principles approach can be useful in modelling is because it more easily allows for additions to the Asset Base after depreciation of the Asset Base has already begun.
In cells F122:T122, write a formula using the DDB function and the provided assumptions to calculate the depreciation amount each period. Make sure there is a minus sign at the front of the formula so that the result each period is a negative value.
What is the sum of all the depreciation from years 10 to 15 inclusive? Submit your answer without a dollar sign, rounded to two decimal places. Your answer should be a negative value between -800 and -1000.
-907.65
12. Now we will look at how to reproduce these results without the DDB function. Ignore the Additions in Year at row 136 for the moment, and focus on writing a formula for cells F137:T137. Each year, if the Salvage Value is 0 as per this example the depreciation amount should be
-1 * (Opening Balance) * (DDB Factor) / (Life of Assets)
Write this formula in cells F137:T137 and verify that it produces the same results as method 1 in row 122.
Now, assume that at the end of year 5, new assets worth $1500 are acquired and to be depreciated using the same assumptions as the original $5000 of assets. Enter this addition to the asset base in the relevant column of row 136. This addition will mean more depreciation in all of the years after year 5.
What is the new value for the sum of depreciation in years 10 to 15? Submit your answer without a dollar sign, rounded to two decimal places.
Save your work. Well done.
-1368.78