How to Get Started

To get started, download the starter file (.xlsx).

What to Turn in

You must submit your solution to Canvas by the due date. You will be creating a spreadsheet using either Microsoft Excel or Google Sheets. When you finish the assignment, save the file and upload it to Canvas. You must name the file

LastName-FirstName .advanced-fns

where LastName is your last name and FirstName is your full first name.

Saving the file if you use Excel. . .

If you use Microsoft Excel for this assignment, you save the file using the Save  as . . .  command of the File tab.

You should also turn on the Auto-save option to the top left corner of the window to make sure you do not lose any data.

Knowledge Needed

This assignment involves the following Excel functions and techniques:

•  EDATE() (or DATE()), SEQUENCE(), DATEDIF(), QUOTIENT(), SUM(), MOD(), TRUNC(), COUNTA(), PMT(), LAMBDA(), LET.

• What-if analysis using dynamic array formulas.

• Formatting

Keep in mind that how you express the formulas matters and is part of the grading. It’s not just getting the right answer that counts; how you write the formulas matters too. Use good naming for intermediate results and develop your LAMBDA formulas incrementally using the LET function.

Problem 1    Collecting Interest (43 points)

There’s an urban legend that Albert Einstein once said compound interest is the most powerful force in the universe. While this attribution is not substantiated, he did apparently say

“Compound interest is the eighth wonder of the world. He who understands it, earns it. . . he who doesn’t. . . pays it.”

The worksheet Savings  Interest represents a savings account calculator. Use name ranges in formulas where appropriate.  Deposits are made monthly.  The monthly interest rate is equal to the annual in- terest rate divided by 12.

One additional note: the starting date uses TODAY() by default. TODAY() returns the current date.      You can use the FORMULATEXT() function to display the formulas near the columns where they have been used.

There is a cell labelled "months to try", which you can use to experiment the problem with a set number of months.

The first 5 columns (DepNo.  -> Total Deposits) are expected to be array formulas which are spillable. The rest of the columns should have draggable formulas.

1.  (4pts) Create named ranges for each input (D6:D12) and for H12.

2.  (1pt) Our planning goal will be to double our savings. D12 will be twice the starting balance.

3.  (31 pts) There are 9 columns, each worth 1 point, except Deposit Date is worth 9 points and Extra Deposit is worth 15 points.  Use formulas to fill in the columns, including the column for DepNo. We’re going to assume the deposits go in at the first of the month, which means the interest accrued will be for the entire balance, the starting balance plus the new deposits.   Let’s group the two deposits (scheduled or extra) together as:

Total deposits  = scheduled deposit + extra deposit

The formula to calculate the balance at the end of the month is:

ending Balance =    beginning balance  + Total deposits +

(month’s beginning balance + deposits) × interest rate

(a) Use EDATE() or DATE() to fill in the Deposit Date column.

Write a  LAMBDA formula which takes 3 arguments dat e, n, k, where dat e  is a date  (e.g., TODAY() or DATE(2022,1,1)), n is the number of dates produced and k indicates how many months apart the dates must be. Your LAMBDA formula starts like this LAMBDA(date,n,k,...) and it will use somewhere the SEQUENCE function. Use your LAMBDA formula to compute the deposit dates in your savings account calculator.

EDATE(given date, months) adds months to a date. It calculates a date k months after a given date. For example, EDATE(“1/1/2020”, 5) returns “6/1/2020.”

You can also use the DATE() function.  To go this route, you need to pass three parameters, each of which is using a function: YEAR(), MONTH(), and DAY(). You would enter the year, month, and day into each function respectively. Suppose we have a date in B3, to find the date a month from B3, you would write =DATE(YEAR(B3), MONTH(B3) + 1, DAY(B3)) .

(b) If your plan is to put something extra in every month, D9 would be 1.  For something extra every other month, D9 would be 2.

To compute the extra deposits write a LAMBDA formula LAMBDA(n,k,v,...), where n, k, v are numbers.  Produce a column of n cells so that every k-th cell contains value v and the other cells contain 0.  The first cell is number 1.  Use your LAMBDA formula to compute the extra deposits in your savings calculator. Remark: If you know the IF function, use it. If you don’t use the property that TRUE*v=v and FALSE*v=0.

Figure 1: An example of what your sheet for Problem 1 should look like

4.  (5 pts) In H6:H10 calculate a summary view.

5.  (2 pts) Format the sheet so it looks the same as Figure 1.

6.  (Bonus 2 pts) In H11, translate the number of months needed to reach the goal into a text string of y  years  and  m  months.

Key Considerations:

1. For DepNo., Deposit Date, Deposit, Extra Deposit, Total Deposit use only one formula which has months_to_try as parameter.  Make sure your formulas for Beginning Balance, Interest etc., are “copyable”down, i.e., that they use named ranges or absolute references ($) wherever needed or appropriate.

2. Write formulas that are easy to read and understand by someone other than yourself.

Problem 2    Paying Interest (42 points)

Having gained interest in Problem 1, now let’s look at its opposite, paying back a loan.  In worksheet Paying  Interest, you will determine how long it will take to pay off a loan.  Loans have principal (the amount borrowed), an interest rate, a duration (the term), and a payment schedule (monthly, bi-weekly, etc.). The interest rate can greatly affect how much you pay back.

This problem uses a specialized function, PMT(), which calculates the monthly payment needed to pay back a loan on-time. It’s one of many built-in functions tuned to specific tasks. You should always look to see if there is a built-in function before you set about to do it by hand. Finding a function can be done in the ribbon.

 .

Or, you can click on the fx button on the formula bar to raise the Insert  Function dialogue box. If you look closely at the next example, you can see we didn’t know the function’s name, but instead looked for functions related to loans.

 .

1.  (5pts) Create input fields for the amount loaned, the lowest possible interest rate, and the highest possible interest rate.

2.  (5 pts) Create columns for looking at five interest rates evenly spaced between the lowest and highest input interest rates. This has to be done using a Formula to compute the table headers.

3.  (8 pts) Use PMT() to calculate the monthly payment due if the loan is paid in full at the end of each period. For instance, the monthly payment of a loan with a 4.75% interest rate given a five (5) year term is $937.85.

4.  (12 pts) Show two different ways of writing the important formula using the PMT() function.  At least one of the formulas must be spilling the entire table.  Indicate which of the formulas require copying to extend it to the entire table and which are spilling. Use appropriate names for the named ranges but you do not have to define them. Assume that the horizontal list of interest rates and the vertical list of terms are defined using the SEQUENCE function.

5.  (2 pts) Format the model exactly as shown in Figure 2.

You may introduce intermediate calculations—and if you do, please do not hide them.  Breaking complex calculations into smaller steps is a good idea. You can see our example calculated the step size for the interest rates in F1.

6.  (10 pts) In the same sheet, create a table to summarize the total costs as shown in Figure 3. You should calculate the actual cost of the loan. From the Monthly Payment Due (MPD) table compute the Actual Cost (AC) table which gives for each interest rate and term the total amount of interest you paid over the duration of the loan. To compute AC, multiply each entry in MPD by the total number of periods and subtract the principal (Initial Balance).

Figure 2: An example of what your payment sheet for Problem 2 should look like

7.  (Bonus 2pts) Show two different ways of writing the formula in the upper left corner of the Actual Cost table. One is spilling and the other one needs to be copied. Indicate which of the two formulas requires copying to extend it to the entire table. Use appropriate names for the named ranges but you do not have to define them.  Assume that the list of terms is defined using the SEQUENCE function.  Assume that MPD is a two-dimensional named range covering the Monthly Payments Due table. You have a reference to the upper left corner of MPD to use in one of the two formulas. Discuss the advantages and disadvantages of the two formulas.

Problem 3    Writing your own MyMax function (15 points)

Write a function called MyMax(T) where T is a table of numbers. MyMax(T) returns the maximum of the numbers in T. You are not allowed to use the Excel functions MAX or MAXA. But you have the built-in Excel function MIN available to define your own implementation of MAX. Test your MyMax function with at least three inputs: a row with 4 numbers, a column with 4 numbers and a table of numbers with 4 rows and 2 columns. For example, one of your test cases might be MyMax(B11:E11).